560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Debugging macros in Excel Visual Basic / VBA
Part five of a five-part series of blogs
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.
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.
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 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!
|Parts of this blog|
25 Aytoun Street