VBA Error Handling not working in Excel

Question

I have not had much experience with VBA, but I sometimes use it in work. Recently, I have encountered a problem that shouldn't happen, and that neither my boss nor myself can figure out.

Basically, the issue is that the Application property DisplayAlerts is set to True by default, and can't be changed for some reason. Possibly related, is that when I hit an error, it always display the End|Debug|Help alert and never hits the applied error handling.

I am running 64-bit Office 2010 on a 64-bit Windows 7 machine. However, I do not believe it to be a platform issue, as I have tested on multiple different platform, operating system and software permutations and no other machine has this error; just mine.

I have created some sample code in case anyone has encountered this before or has any ideas. The only thing I can think of, is that I have something installed on my machine that is causing this. But after a program purge and many restarts, I am no closer to decipher what it might be.

Public Sub TestErrorHandler()

' Suppress alerts
Application.DisplayAlerts = False

Dim strArray(1) As String
strArray(0) = "Hello"
strArray(1) = "World"

' Set up error handler
On Error GoTo ErrHandler

For i = 0 To 3
    MsgBox strArray(i)
Next

' Strip the error handler
On Error GoTo 0

' Unsuppress alerts
Application.DisplayAlerts = True

Exit Sub

ErrHandler:

MsgBox "Error: " & Err.Description

Resume Next

End Sub

The error is thrown on the third enumeration of the for loop (as it should). The type of the error is irrelevant, what is relevant is that I get the error and never hit the error handler.

Any suggestions or help on this would be greatly appreciated.

Many thanks!

Answer 1

Press ALT + F11 from within Excel to reach VBA editor.

Goto Tools menu -> Options item -> General tab.

Set the error trapping to "Break on unhandled errors"