vbaexcel

How to Programmatically Break on All Errors?

What I want to do is set a flag to go from the Error Trapping option "Break on Unhandled Errors" to the option "Break on all Errors" (VBA menu: Tools > Options > General tab):

enter image description here

The reason for this is that I have error handlers throughout my code and whilst I'm debugging I want to be able to see what the errors are.

Setting the error flag off:

On Error GoTo 0

Will just take me back to the last place error handling was used, like two or three procedures before the one I'm working on.

The reasoning behind this is that I have a large codebase and I know there are several sections that I do not need to see what the errors are but I do once I get to a certain part of the processing (many hundreds of calls deep), it is at this point I want to turn on Break on Unhandled Errors.

Solution

  • I know this question is old, but adding an answer for anyone that needs to do this in Excel VBA. I have an add-in that I developed for my company that uses the following technique:

    1. Check for unwanted error trapping option using registry key (Break on All Errors in this example)
    2. Change Registry Key if needed
    3. Restart Excel (I let this part be manual but you could automate it depending on the circumstance)
    Dim objShell As Object
Dim strErrOption As String
Set objShell = CreateObject("WScript.Shell")
On Error Resume Next
strErrOption = objShell.RegRead("HKEY_CURRENT_USER\Software\Microsoft\VBA\7.1\Common\BreakOnAllErrors")
If strErrOption = 1 Or Err.Number <> 0 Then 'Break on All Errors Set or Reg Key DNE
    On Error GoTo 0
    objShell.RegWrite "HKEY_CURRENT_USER\Software\Microsoft\VBA\7.1\Common\BreakOnAllErrors", 0
    MsgBox ("Please Restart Excel")
End If
On Error GoTo 0

    Note: Excel only reads the registry on open and only writes on close. This is why restarting the app is required.