CODEWITHSUNDEEP
vbaexcel
spences10
spences10

Reputation: 590

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.

Upvotes: 3

Views: 3266

Answers (3)

Jacob Andrews
Jacob Andrews

Reputation: 101

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.

  • 0 is Break on Unhandled Errors
  • 1 is Break on All Errors
  • 2 is Break in Class Module

Upvotes: 5

Tod Flak
Tod Flak

Reputation: 141

You didn't say which VBA environment you are using. In Access (and only in Access, as far as I know), there is a very useful method to programmatically modify the Error Trapping setting:

Application.SetOption("Error Trapping", 2) 
Debug.Print Application.GetOption("Error Trapping")

I've used this many times where I have error handling set up on an outer method, but I really want to see where the code is hitting an error somewhere far down in a call stack. And just globally setting the Error Trapping to "Break on all errors" is a pain if you have expected, well-handled errors somewhere in your code, because it always stops on those! So, in Access, you can switch to "Break on All Errors" just in one section when you want to.

Upvotes: 9

spences10
spences10

Reputation: 590

As per the comments, there isn't an easy way to do this other than manually change the setting in the options when you get to the section of the code you want to have a runtime error for rather than being taken to the last active error handler.

Upvotes: 1

Related Questions