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.

  1. Debugging Macros
  2. Ways to Step Through Code in VBA
  3. Setting Breakpoints and the VBA Stop Statement
  4. Debug.Print and the Immediate Window
  5. Other Useful Debugging Tools (this blog)

This blog is part of our Excel VBA tutorial.  You can also learn to program in Visual Basic for Applications on one of our scheduled or tailored VBA training courses.

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:

Call stack demonstration

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:

The call stack menu

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:

Call stack dialog box

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):

The locals window menu

Choose the menu option shown to display the Locals window.

 

Here's an example of the Locals window:

Example locals window

In this window you can see the value of local and (if you expand Module1) global variables.

 

Watching 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:

Adding a watch

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:

The Add Watch dialog box

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!

The Watch window menu

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:

Example of watch window

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!

 

This blog has 0 threads Add post