This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

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.

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)

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

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.

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:

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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC