How to Debug Code in the Excel Visual Basic Editor
In an earlier post, we recorded two Excel macros in Visual Basic, and ran them. Then, in the next post, we viewed the code in the Excel Visual Basic Editor.
Code you have recorded will usually run. However, you may want to change it for all sorts of reasons. To tidy it up, make it interactive, put in repeats or conditions, etc. Doing so may also introduce bugs.
How do you find these errors?
The first way is to look for any lines in red. If you followed our recommendations for VB Editor Options, Excel will not complain about every spelling or syntax error. It will mark them in red.
Fix them first, referring to help (next post) where needed. Then:
1. Compile VBAProject
In the Visual Basic Editor menu, click Debug > Compile VBAProject.
This will pick up extra errors. They will include variables you failed to define (these are often spelling errors). That’s if you have Option Explicit at the top of the module as we recommended in VB Editor Options.
In theory, your code is now error-free. But Excel would never be able to pick up errors like making an entry in the wrong cell! Those you need to find for yourself. So:
2. Save Your Macro and the Excel File
Click somewhere in the macro you want to test.
Save your code before testing it. The Save button on the Standard Toolbar will do this, as will the menu item File > Save.
Is the macro you are running is going to work on a different file to the one that contains the macro? Then save that file too, from Excel itself. Most macros, unless specifically written to do something different, will act in the current file in Excel.
3. Step Through the Code
A brave –or foolhardy– programmer might at this point run the macro. You could either press F5 or click the Run button on the Standard Toolbar. Menu item Run > Run Macro (F5) is another way to do it.
Unless it is a simple macro, this might be a bad idea. You could have no idea what causes any errors. Rather, step through the macro a line at a time. This will let you watch the Excel window and see exactly what the macro does at each step. If it goes wrong, you will know where the problem occurred.
To start stepping through the macro, press F8 It’s also found on the Visual Basic Editor menu, Debug > Step Into (F8), but tedious to use from there each time).
The current line, that is, the next one to run, will highlight in yellow, with a yellow arrow in the (left) margin.
Here we arranged the Visual Basic Editor and the Excel worksheet on screen so that we can see both. Then we can watch the macro execute one line at a time and see the effect in Excel, as we repeatedly press F8.
What is amazing about the Visual Basic Editor is that it lets you change your code while you are stepping through it, and continue! Most programming languages would force you to start again at the beginning! You can also drag the yellow arrow (current line indicator) to a different line, to either repeat or skip code. Again, very few other programs let you do this.
To stop the code, click the Stop button on the Standard Toolbar (the square). Or use Menu item Run > Reset.
To run the code from the current line (without stepping further), press F5. Or click the Run button on the Standard Toolbar. Or use Menu item Run > Run Macro (F5).
4. Break Mode and Breakpoints
When running from Design mode, as you may have seen already, an unhandled error results in an error message like this:
Click Help to see help on the error message.
End stops the code running, leaving you back where you were: Either in the spreadsheet or in Visual Basic’s Design Mode, where you can modify your code.
Debug puts you into Break Mode. This is like Design mode except that you can restart the program by pressing F5 or the Continue Button. Or you can step through the code with F8. If you want to exit Break mode back to Design mode, click the Stop button (the block).
You can enter Break mode manually at any time the program is running by pressing Ctrl+Break.
In Break Mode, unlike Design mode, some changes are not a good idea. Specifically, you should not change or add Dim or Sub statements. Also, you may have trouble editing the current line if it is a multi-line statement.
The Current Line (typically the line that caused the error) is highlighted (yellow by default). This is as set in Tools > Options > Editor > Next Statement Text.
To change the Current Line (Next Statement) to a different line, put the cursor on that line (click it). Then press Ctrl+F9 or use Run > Set Next Statement or drag the yellow arrow to the new line.
5. The Debug Windows
This is only useful in Break mode. Enter Break Mode by clicking the “Pause Button” on the toolbar (“||”), or setting a breakpoint (red dot) ahead of time on a line on which you want the program to pause. You set a Breakpoint by clicking on the line and pressing F9. Or click in the grey Margin Indicator bar on the left of the code. Or use Debug > Toggle Breakpoint. You remove them in the same way.
In the Excel Visual Basic Editor’s View menu, we find the Immediate Window (Ctrl+G), the Locals Window and the Watch Window:
In the Immediate Window (Ctrl+G), you can enter “?” and an expression to find out the value of that expression. You can set a new value by typing Variable = Expression Enter as you would in code.
Here are the two Debug Windows (Locals and Watch):
In the Locals Window (bottom left), you can peruse the object model of the Form you are on (“Me”) by clicking “+” to expand or “-” to contract a branch. This will show you all its properties and collections.
In the Watches Window (bottom right), you can set Watch Points by clicking on an expression and then using Debug > Add Watch:
You can set a Quick Watch by pressing Shift+F9. The Watch expression defaults to the highlighted expression in the code. The Procedure is the current procedure only. The Watch Type defaults to “Watch Expression”.
You can edit Watch Points by pressing Ctrl+W. Or use Tools > Edit Watch. Or right-click on the watch point and select Edit Watch from the pop-up menu.
If you need more help, see our next post.