Writing Visual Basic for Applications code is hard, but what about writing VBA code that works and to write it fast? Often I found many colleges struggling to get a few simple procedures to work. I was amazed that most of them preferred to keep at it, trying to pin down the one line of code the causes their issues, rather then spend a few minutes learning how to properly debug Excel code and get the job done much faster! Introducing today’s post on how to debug VBA code!

Debugging is essentially a methodical process of locating and fixing bugs (or defects as some prefer). Basically find that bug and kill it! Excel VBA compared to other programming languages / environments has one significant advantage – you can debug code on the fly without having to recompile the code. This makes life much easier and debugging some much more pleasant! So let’s jump right to it.

Debug VBA: Basic terms

First we need to introduce some basic terms to facilitate the remaining part of this post:

• Executing/Running code – the process of running a macro
• Debugging code– the process of finding and fixing bugs/defects
• Breakpoint – a line of code at which the execution of the macro will pause

Running / Breaking / Reseting

Let’s start with the tool bar at the top of the VBA Project Viewer window. You should find 3 buttons as shown below:

The buttons allow you to do the following:

• Run – run your macro (Sub) or UserForm. This is equivalent to the key shortcut F5
• Break – pause a running macro CTRL+Break. You can also stop a running macro by hitting the Esc button
• Reset – reset a running/paused macro

These are the basic commands for running macros.

Break points

Breakpoints specify lines of code at which the execution of your macro should pause when you debug VBA. They are convenient when you want to be sure your code does run through a certain loop of If statement.
To add/remove a breakpoint simply left-click on the left gray bar in your VBA Project View next to your code. A red dot should appear indicating that you have specified a new breakpoint. Click on the dot again to remove the breakpoint.

Assertions – the right way to breakpoint errors

Often breakpoints are specified in places where error might occur. This may be cumbersome when you have loop running and are not sure when the error will occur or if you are aware of a condition that causes the error but are unable to catch it at the right moment. This is where you will want to use Debug.Assert.

How does Debug.Assert work? Say you are dividing to numbers and want to make sure the denominator is non-zero. Otherwise you want the code to pause. Consider the example below. In the first example the code will continue to execute normally, in the second example however the macro will immediately pause at the assertion as if a breakpoint was defined!

x = 100
y = 10
Debug.Assert y <> 0 'Condition met: Continue!

x = 120
y = 0
Debug.Assert y <> 0 'Condition false!: Pause!

Stepping through code

The key to debugging is to skillfully step through your code either by line or an entire function/procedure. Here are the basic commands found in the Debug menu toolbar:

• Step Into F8 – step into each procedure/function
• Step Over SHIFT+F8 – step over every procedure/function (run just the current procedure)
• Step Out CTRL+SHIFT+F8 – step out of the current
  running procedure
• Run to Cursor CTRL+F8 – execute and break at the line pointed by the cursor

Usually this is enough although you might want to get familiar with the other commands in the Debug menu toolbar.

The Immediate window and Debug.Print

In the bottom left corner of VBA editor you should find the Immediate window. This panel can be used to execute immediately pieces of code (even your code is paused). Simply start typing and hit ENTER! Additionally the Immediate window is the default output of the Debug.Print VBA command which prints a certain provided string (similarly like the MsgBox but does not display any pop-up). The Debug.Print command is very convenient for outputting VBA execution messages / statuses or execution progress (e.g. number of processed items).

Debug.Print "Hello there!"

The output:

Summary

Debugging is an easy skill to learn. Knowing how to skillfully debug VBA code with benefit your coding experience and efficiency! Let me know what you think!