BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
This series of blogs is intended to provide online training in how to debug in Visual Basic for Applications, including using breakpoints and the immediate window.
- Debugging Macros
- Ways to Step Through Code in VBA
- Setting Breakpoints and the VBA Stop Statement
- Debug.Print and the Immediate Window
- Other Useful Debugging Tools (this blog)
Posted by Andy Brown on 25 November 2011
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Other Useful Debugging Tools
This blog doesn't (quite) claim to be a comprehensive guide to debugging, but I thought I'd end with some of the slightly more exotic debugging tools. Of the following, the call stack is the only one I personally use regularly.
The Call Stack
When you are in a procedure called from another, it can often be useful to see whereabouts you are in the call stack. This is easier to understand with a diagram:
The green arrow shows where you are in the calling routine called CheckAnagrams.
To display the call stack, while debugging a macro and in break mode either press CTRL + L or choose the menu option shown below:
Choose this option to display the call stack.
You can then double-click on any procedure listed to go to the current cursor position within it:
Double-click on any routine to go to the currently executing line within it.
The call stack is especially useful when you have several layers of calls, as you can see where you are in each and every currently executing procedure.
The Locals Window
The Locals window isn't particularly well named, as it allows you to see the value of local and global variables. It might be better called the Variables window. To display it, while in break mode (ie while debugging):
Choose the menu option shown to display the Locals window.
Here's an example of the Locals window:
In this window you can see the value of local and (if you expand Module1) global variables.
The Locals window indiscriminately displays the value of all currently used variables, but sometimes you may want to watch the value of a variable to see - for example - when it changes. You can do this as follows:
Double-click on any variable to select it, then right-click and choose to watch it as shown here.
The next stage is to choose how to watch it - perhaps the most useful way is shown here:
Here we are choosing to go into break mode whenever the value of the variable called Word1Reduced changes.
When watching variables like this, you will find it useful to have the Watch window open!
Choose the menu item shown to display the Watch window.
In the example below, we're watching two variables, but one of them is shown as Out of context:
Here we're watching two variables.
Here the variable FirstWord is in a different subroutine to the current one, and so at the moment its value is not set.
It's been a long blog, but that is pretty much everything that you can do when debugging Excel VBA macros. If you only remember to split the screen into two parts to see what's going on while you press F8 to step through your macros, that is 90% of what matters.
Now go zap those bugs!