Tips for Debugging Add-ins

Before compiling a workbook to an add-in, you can hide the workbook and then run its procedures from another workbook. This will help you find reference errors and errors that occur because the workbook is hidden. If your code uses error handling, you can turn on the Break On All Errors option to disable the error handler and display error messages. The Break On All Errors option is located on the Module General tab in the Options dialog box (Tools menu).

Note

After you compile the add-in, Microsoft Excel ignores the state of the Break On All Errors option. Therefore, your add-in's error handler will run, regardless of whether this option was on or off when you compiled the add-in.

After an add-in is compiled, it can be difficult to debug. The add-in's procedures don't appear in the Run dialog box, so you cannot use the debugging features such as Step Into and Step Over, nor can you see the add-in code in the Debug window. This behavior is intentional — you usually don't want users to be able to single-step through your code or to see your code in the Debug window.

You can use an error handler, line numbers, and the Debug.Print statement to continue debugging after you compile the add-in. Although line numbering may sound archaic, it does give you a way to indicate where an error occurred. You can number all the lines in your code, or just the lines in certain critical sections, as shown in the following example.

Sub AddinSub()
    Dim x, y As Integer
    On Error GoTo ErrHandler
    ' Notice the line number in the following statement
10  For x = 1 To 10
        For y = 1 To 10
            If x = 7 And y = 7 Then
                ThisWorkbook.Worksheets("Sheet" & x * y).Cells(1, 1) = 5
            End If
        Next y
    Next x
    ' Here's another line number . . .
50  ' Do some more stuff
    ' Exit sub before running error handler
Exit Sub
ErrHandler:
    ' This statement prints the error information
    Debug.Print "Error " & Err & " " & Error(Err) & _
        " in line no. " & Erl
    ' This line prints the values of two variables of interest
    Debug.Print "x ="; x; Chr(13); "y ="; y
End Sub

To use code in the following example, compile it to an add-in; create a new workbook, add a module to it; and then create a reference to the add-in using the References dialog box (Tools menu). In the new workbook, create a small sub that calls the AddinSub procedure:

Sub dbg()
    AddinSub
End Sub

The AddinSub procedure should fail on the line that contains the Worksheets method call (unless the add-in contains more than 48 worksheets), although you'll see no indication of this error. Press CTRL+G to display the Debug window; notice that the Debug.Print statements in AddinSub wrote error information to the Immediate pane. Notice also that the error occurred on a line that didn't have a line number. In this case, the Erl function returns line number 10, which is the previous line number closest to the line in which the error occurred.

Before you compile a copy of a workbook in the add-in format (assuming that you've already thoroughly tested and debugged your code), remember to remove all debugging code, such as Stop statements and Debug.Print calls.