VBA Error Handling not working in Excel

Question

I have not had much experience with VBA, but I sometimes use it at 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 platforms, operating systems 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 deciphering 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"

_{(source: microsoft.com)}