Where's the Debug button in Excel VBA error

Question

Simple question I haven't been able to find the answer to with Google:

How do I get the "Debug" button to appear in the pop-up that appears when there's a run-time error in Excel 2010 VBA? I've seen it in the past, but now its missing.

Is this a configuration issue?

Answer 1

It happens sometimes if you run your method directly from Immediate window.

VBA editor, in order to display Debug option needs to break the code on some line and it is impossible when error occurs on the line in Immediate window.

---

Example:

If you run the code below directly in Immediate window:

MsgBox 1 / 0

editor will display the following window:

However if you wrap this line of code into method:

Sub test
    MsgBox 1 / 0
End Sub

and call it from Immediate window like that:

Call test

editor will raise an error because of the same operation but run-time error window will look like that:

Answer 2

If you are using On Error Goto xxx error handling in your VBA then you won't see the Debug/Halt dialog - this is by design, because you are expected to provide your own handling routines.

This also applies if you are using On Error Resume Next, except that the error is completely ignored.

If you use the setting On Error Goto 0, (the default setting, equivalent to not specifying either of the above settings) then you will see the Dialog box when an error occurs.

Answer 3

You can try changing the debugging mode in the settings, that often helps. In addition, I've read reports that say that the Debug button can disappear if the VBA code is in a hosted object, so moving it to a standard module may help.

Answer 4

In VB Editor, Go Tools \ Options. General Tab, Error Trapping section, Then switch to Break on all Errors.

Answer 5

If you are looking to debug your code, you can do that from "Microsoft Visual Basic for Applications", you can use Alt + F11 shortcut to open it from Excel.