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
581 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 7 is as follows (a similar method works for Windows Vista too):
Right-click on any blank part of the task bar (usually at the bottom of the screen) and choose to split windows stacked (split horizontally) or side by side (vertically).
If you have other applications open when you do this, you may get your screen split more times than you had bargained for! In this case, the trick is to minimise the applications which you're not interested in (you don't have to close them) then repeat the split as above.
You can also do the same thing in Windows 7 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:
Now that we've seen what our example is, let's look at how to debug it, starting with the basic debugging keys.
You can use various combinations of the F8 key to step through code, and also use the yellow arrow and right mouse button to change the next executable statement.
To learn much more about programming and debugging in VBA, book onto one of our classroom or online VBA courses.
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:
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).
Once you've got the hang of stepping through macros, it's time to set some breakpoints.
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.
WE cover breakpoints and debugging - and much else besides - on our two-day Introduction to VBA course - if you book the online version you can attend this from anywhere in the world!
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.
Now that we've started talking about the Debug.Assert statement, it's time to look at the much more powerful Debug.Print statement, and with it the immediate window.
Having created a text file (as per the previous part of this blog), here's some code to read it in. The trick is to keep reading lines until we reach the end of the text stream:
Sub ReadFile()
'again, we need this strange thing to exist so that ...
Dim fso As New FileSystemObject
'the file we're going to read from
Dim ts As TextStream
'... we can open a text file with reference to it
Set ts = fso.OpenTextFile("C:\Wise Owl\info.txt", ForReading)
'keep reading in lines till no more
Dim ThisLine As String
Dim i As Integer
i = 0
Do Until ts.AtEndOfStream
ThisLine = ts.ReadLine
i = i + 1
Debug.Print "Line " & i, ThisLine
Loop
'close down the file
ts.Close
End Sub
Here's what this would show in the immediate window:
The routine prints out the lines to the immediate window
When you choose to open a text file, you can do it in one of 3 ways:
The 3 possible ways to open a text stream
In our example we wanted to read from the file, but you can also use the OpenTextFile method to open a file to write more lines to it (using ForAppending).
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!
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.