Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
It is a sad fact that nearly every macro that you ever write will contain bugs initially. This tutorial explains how to debug VBA macros (that is, how to find any mistakes in them).
The best way to debug a macro is to split your screen. The easiest way to do this in Windows 11 is as follows:
Hover over the icon to restore, minimise or maximise your Excel or VBA window and you should be able to choose to split your screen vertically like this.
You can also do the same thing in more simply by holding down the Windows key and pressing the right or left arrows. Try it!
If you want to debug, you can now press the F8 key to step through a macro line by line:
You can also use the tools on this Debug toolbar, but I find the short-cut keys easier. To display this toolbar, just right-click on any existing VBA toolbar and choose Debug.
Excel will show the line that you are about to execute in yellow:
Here we are about to run the first line of this macro, so it appears in yellow.
When you press the F8 key to execute the line above, VBA will omit the variable declaration and go straight to the line after it:
Variable declarations are what is called non-executable (they just set aside space in memory).
You can now continue pressing the F8 key until your macro finishes.
If you've decided that you want to stop a macro running, you can do so by clicking on the Reset tool:
Click on the blue square to stop your macro running.
I have never found a quick short-cut to do this: the best I can do is to press Alt + R followed by R (try it - you'll see what it does).
If your macro crashes (perhaps because it's looping infinitely), you can press Esc (this is easier to find on your keyboard, and usually works), or failing that try to find the Break key.
At any time you can let your mouse linger over a variable or property to show its value:
Here the value of the variable i is shown as 0 (hardly surprising - we haven't set it to anything yet).
There are lots of other ways to display the current values of variables, but this is the easiest.
Throughout the rest of this debugging tutorial we use an example to work out whether two words or phrases are perfect anagrams of each other:
The macro checks whether the two words entered are (give or take spaces) perfect anagrams of each other.
Here is our code (I don't claim that this is a particularly efficient algorithm, by the way!):
Option Explicit
Const Letters As String = "abcdefghijklmnopqrstuvwxyz"
'the two words to check
Private FirstWord As String
Private SecondWord As String
Sub CheckAnagrams()
'each letter number in the alphabet
Dim i As Integer
'first get the words from spreadsheet
GetWords
'if they're not the same length, the answer is no
If Len(FirstWord) <> Len(SecondWord) Then
GiveVerdict False
Exit Sub
End If
'check each letter appears the same number of times
For i = 1 To Len(Letters)
If LetterDifferent(Mid(Letters, i, 1)) Then
GiveVerdict False
Exit Sub
End If
Next i
'if we get here, it is an anagram!
GiveVerdict True
End Sub
Sub GetWords()
'first find out what the words are
FirstWord = Range("C2").Value
SecondWord = Range("C4").Value
'remove any spaces, and turn into lower case
FirstWord = LCase(Replace(FirstWord, " ", ""))
SecondWord = LCase(Replace(SecondWord, " ", ""))
End Sub
Function LetterDifferent(ThisLetter As String) As Boolean
'determines whether this letter appears the same
'number of times in the two words
Dim Word1Reduced As String
Dim Word2Reduced As String
Word1Reduced = Replace(FirstWord, ThisLetter, "")
Word2Reduced = Replace(SecondWord, ThisLetter, "")
If Len(Word1Reduced) <> Len(Word2Reduced) Then
LetterDifferent = True
Else
LetterDifferent = False
End If
End Function
Sub GiveVerdict(IfSame As Boolean)
'put the result in the answer cell
If IfSame Then
Range("C6").Value = "Perfect anagrams"
Else
Range("C6").Value = "Not anagrams"
End If
End Sub
In a nutshell:
The CheckAnagrams routine first calls the GetWords procedure, which reads the words entered into the two variables FirstWord and SecondWord, strips out any spaces and converts all letters to lower case.
The CheckAnagrams routine then compares the lengths of the two words entered, and reports an error if they're not the same.
Finally, the CheckAnagrams routine calls the LetterDifferent routine 26 times (once for each letter of the alphabet). For each time, the LetterDifferent routine removes the given letter from each of the two words, and determines if the new words that this would yield are still of the same length.
Now that we've seen what our example is, let's look at how to debug it, starting with the basic debugging keys.
As mentioned, you can use the F8 key to step through code, but what happens when you reach a call to another routine?
In the example shown, the CheckAnagrams macro is about to call the GetWords macro. What will happen next depends on which function key you press.
Here are the two main keys that you can press, and what each would do:
Key | What it will let you do |
---|---|
F8 | Step into GetWords,taking you to the first line of this subroutine. |
Shift + F8 | Step over GetWords (Excel will run the subroutine in its entirety, and take you to the first executable line of code immediately after this. |
If you step into the GetWords routine as above by pressing F8 and then realise that you didn't mean to do this, you can press - wait for it - SHIFT + CTRL + F8 to execute the rest of the GetWords routine and return the current line to the main CheckAnagrams program.
The above keys are great for stepping forward through a macro line by line, but whoever said you had to do this? What happens if you want to go backwards, or jump forwards? The easiest way to do this is to click and drag on the yellow arrow:
Want to run the GetWords routine again? Just drag the yellow arrow to the line which you want to execute next, and Excel will make this the next executable statement.
It hardly needs saying, but you should do this with care only!
Another way to achieve the same effect (though not nearly as much fun) is to right-click on the line which you want to execute next:
Right-click on any executable line to make this the next statement to be executed (it will go yellow).
Often in a complicated or long macro you will want to avoid stepping through it line by line. To do this you can set a breakpoint:
Here we've set a breakpoint halfway down the CheckAnagrams macro. If you run this macro, it will execute until it reaches the breakpoint, and then allow you to press the F8 key to step through the code line by line.
You can set or remove a breakpoint by clicking in the margin to the left of it:
Click in the grey margin to the left of a line of code to set (or remove) a breakpoint for it.
Alternatively, press F9 to toggle a breakpoint on or off.
A surprisingly useful short-cut key is SHIFT + CTRL + F9, which removes all of the breakpoints that you've set.
One feature of breakpoints is that they are not saved wihen you close a workbook - so if you come back to your program on the next day, you'll have lost any breakpoints that you set. A way round this is to use the STOP statement instead, as in this example:
Sub CheckAnagrams()
'each letter number in the alphabet
Dim i As Integer
'first get the words from spreadsheet
GetWords
'if they're not the same length, the answer is no
If Len(FirstWord) <> Len(SecondWord) Then
GiveVerdict False
Exit Sub
End If
'break code here
Stop
'check each letter appears the same number of times
For i = 1 To Len(Letters)
If LetterDifferent(Mid(Letters, i, 1)) Then
GiveVerdict False
Exit Sub
End If
Next i
When you run the code above, this is what will happen:
VBA will run the code until it reaches the STOP statement, then allow you to step through your code line by line.
When you save this workbook, the Stop statement will be saved with it.
Although I don't use it much, for the sake of completeness I should mention the Debug.Assert statement, which will take you into debug mode if a condition is true:
Here when we run the main macro it will stop when the condition following the Debug.Assert statement is False. To say the least, this is counter-intuitive. The first break in this code will thus come when i is first not less than 20, as shown here.
This is an odd command: not only does it break when the condition is False, rather than True, but it has a very strange name too.
The immediate window is an invaluable tool for debugging. You can display it like this:
Choose this option or press Ctrl + G.
You can ask any question in the immediate window by preceding it with a question mark:
Here we've asked for today's date, the current time, the name of the current workbook and the name of the active sheet.
However, you can also use the Debug.Print statement to write things to the immediate window - for example:
Sub ShowFirstTenNumbers()
Dim i As Integer
'show first ten numbers
For i = 1 To 10
Debug.Print i
Next i
End Sub
This subroutine would show the following:
Notice that we have the output from the previous commands showing at the top.
The only way Wise Owl have ever found to clear the immediate window is to select its entire contents (press Ctrl + A) then press Del to delete the text selected.
Wondering why it's called the immediate window, but the command is called Debug.Print? The answer is that the window used to be called the debug window!
You can print multiple things to each line of the immediate window by separating them with commas. For example, this modified subroutine:
Sub ShowFirstTenNumbers()
Dim i As Integer
'show first ten numbers, with their
'squares and cubes
For i = 1 To 10
Debug.Print i, i * i, i ^ 3
Next i
End Sub
would produce this output:
You'll find columns don't always line up neatly like this.
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!
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.