Skip to content

Excel VBA Debugging Techniques

    Syntax Errors

    Syntax  errors occur as you are writing your code. If you have a syntax error the offending code will be displayed in red and a Compile error warning will be displayed.

    Syntax errors occur when you make a grammatical error in your code like leaving out speech marks when referring to a range…

    … or leaving out brackets where they are needed.

    For both of the above syntax errors you get the same Compile error message. For the second error it’s pretty useful for the first, not so useful.

    You may want to turn Syntax error warnings off in Tools | Options | [Editor Tab].  Untick the option Auto Syntax Check. Syntax errors will still be displayed in red when you untick this option – so you will still be able to spot them!

    Compile Errors

    Compile errors only become apparent when you compile your code.  Code will always compile before it runs but you can compile without running it by clicking  Debug |Compile VBA Project.

    Compile errors are not shown in red, but you do get an error message when you compile the procedure. In the example below, the Worksheets object name is spelt incorrectly.

    When you attempt to run the procedure you get this error message…

    … and the code enters break mode.  You can see below that the first line of code is highlighted in yellow with a yellow arrow in the margin – this is the breakpoint (meaning the code couldn’t execute beyond this point) and the offending keyword is highlighted in blue.

    Now you can fix the spelling error, Reset the procedure with VBE’s Reset button as shown below and attempt to run the code again.

    If you just compile the procedure rather than compile and run, the code will not enter break mode.

    Using Option Explicit

    Always use Option Explicit so any errors you make referring to variable names or other misspellings are picked up when you compile your code. For example the misspelling or vbRed in the code below is only picked up as a compile error if Option Explicit is used.

    Run-time Errors

    Run-time errors do not appear in red like a syntax error, nor do they occur when you compile a procedure (compile without running), but they do occur when you attempt to run a procedure.

    The code below attempts to select a sheet that is not available and therefore produces a run-time error.  Run-time errors put your procedure into break mode: break mode temporarily stops the code running until you fix the problem and resume execution.

    The yellow arrow and yellow highlighting indicate the position at which the code stopped – the break point.  Lines of code above the break point have been successfully executed.

    This is the run-time error displayed for this code.

    To fix the error, you need to click on the Debug button on the run-time error dialog box. This will allow you to fix your code and then resume code execution from the break point.  Use the VBE’s Continue button to resume running the code or use the F5 keyboard shortcut.

    Error Handling

    Some run-time errors occur not because of errors in your code necessarily but because the procedure encounters unexpected things in your data like missing workbooks, worksheets or values, or unexpected data types.  For this reason you will need to build error handling code into your procedures in expectation of any of these problems.

    Run-time error are covered in more depth in the tutorial on Error Trapping and Handling.

    Stepping Through Code

    When you step through code you are executing one line of code at a time.  This might be useful if your procedure produces unexpected results or an error.  By stepping through code you are much more likely to identify and resolve any problems.

    To step through code you need to put your procedure into break mode: Click Debug | Step Into or use the F8 shortcut key.

    The first line of code will be highlighted in yellow and when you press F8 again, that line of code will be executed. Press F8 again and the next line of code will be executed and so on and so on.

    To see what your code is actually doing, split your screen so that the VBE and Excel are both visible.

    Breakpoints

    You can set a breakpoint in your code so that all code above it is executed normally but from your breakpoint onwards you can step through the code. This is useful in long procedures where it would be tedious to step through every line of code before you get to the part that you are trying to debug.

    You can click into the grey margin to set a breakpoint or click into a line of code and then select Debug | Toggle Breakpoint or use the F9 shortcut key.

    To remove the breakpoint click the breakpoint circle.

    Immediate Window

    The Immediate Window can be used to display the current value assigned to a variable or property: useful information when debugging code! To show the Immediate Window click View | Immediate Window or use the keyboard shortcut CTRL G.

    Take this simple example.  Our code is going to loop through the numbers on our worksheet and multiply each number by 10. When it gets to Goat the code will produce a run-time error.

    The code for the procedure is as follows…

    Sub UsingImmediateWindow()
Dim rg As Range, MyData As Range
Set MyData = Range("A1:A12")
For Each rg In MyData
    Debug.Print rg
    rg = rg * 10
Next rg
End Sub

    Using Debug.Print

    Notice the line of code that reads

    Debug.Print rg

    The Debug.Print code will display the value of the variable rg in the Immediate Window as shown below. The run- time error means that only 5 values are shown in the Immediate Window.

    From a debugging point of view this is a great bit of information as we can see exactly why the data has caused an error.

    If I wanted to find out the cell address for the word Goat, I can type ? rg.Address into the Immediate Window, press ENTER and the answer is displayed on the line below.

    I could even change the value of rg in the Immediate Window by typing rg = 5 which fixes the problem and I am now able to execute the remainder of the code.

    Using ? In the Immediate Window

    You do not have to use Debug.Print in your code to show variable or property values in the Immediate Window.

    As you are stepping through code or at the point you have a debug error, just type a question mark to the left of the variable or property you want to examine. Press ENTER and the answer will appear on the line below.

    Watch Window

    The Watch Window allows you to place a watch on the value of a variable or property.  As with the Immediate Window this is a useful tool when you are trying to debug code.

    To show the Immediate Window click View | Watch Window.

    The easiest way to add a watch is to right-click on the variable or property and select Add Watch…

    The Watch Window will only show values when you are in break mode.

    In this example we want to format cell background colours based on the value in the cell.  The cell’s value will be used to set the colour index number of the cell’ s interior property.  There are 56 colours available in the colour index palette.  You will notice two values that could be problematic: 200 in cell A20 and TRUE is A28.  200 will cause a run-time error but TRUE won’t.

    Here is the code for our procedure

    Sub UsingWatchWindow()
Dim rg As Range, MyData As Range
Dim FontColour As Byte
Set MyData = Worksheets("Using Watch Window").Range("A1:A56")
MyData.ClearFormats
For Each rg In MyData
    FontColour = WorksheetFunction.RandBetween(1, 56)
    rg.Interior.ColorIndex = rg
    rg.Font.ColorIndex = FontColour
Next rg
End Sub

    We are going to add a watch for rg and rg.address before we run the code.

    Right-click on the rg variable any place it appears in the code and click Add Watch…

    Click on OK to confirm and the watch is added to the Watch Window.

    To add a watch for rg.address do the same thing again but edit the expression to include .address

    Click on OK to confirm and the watch is added to the Watch Window.

    Lastly we are going to add a watch that puts the code into break mode if rg is a logical value.  Repeat as before to add a watch and edit the expression so it reads as…

    WorksheetFunction.IsLogical(rg)

    Change the Watch Type to Break When Value is True

    Click on OK to confirm and the watch is added to the Watch Window.

    Our three watches…

    When we run the code a run-time error occurs as we expected.  Hit  Debug  and the Watch Window shows us the offending value and cell address.

    I can now quickly find and change the value on my worksheet.  Once done I click Continue in the VBE to take my procedure out of break mode.  The procedure goes into break mode again, not because of run-time error but because of my third watch which stops the code if rg is a logical value.  Without this watch the code would have continued and I would have a missed a potential problem in my data.

    Locals Window

    The Locals Window shows a complete list of all your procedure’s variable when in break mode.  To show the Locals Window, click View | Locals Window.

    In this example we are going to encounter a run-time overflow error when x * y exceeds the Byte storage limit of 255.

    Sub UsingLocalsWindow()
    Dim x As Byte
    Dim y As Byte
        For x = 1 To 100
            For y = 1 To 100
                Cells(x, y) = x * y
            Next y
        Next x
End Sub

    If we weren’t quite sure how to debug our run-time error we could look at the Locals Windows to see the values assigned to x and y.

    So we could work out that 3 x 86 = 258 and together with the run-time error message work out that we need to declare x and y with a data type of Integer.