564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls 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
Debugging macros in Excel Visual Basic / VBA
Part one 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.
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!):
Const Letters As String = "abcdefghijklmnopqrstuvwxyz"
'the two words to check
Private FirstWord As String
Private SecondWord As String
'each letter number in the alphabet
Dim i As Integer
'first get the words from spreadsheet
'if they're not the same length, the answer is no
If Len(FirstWord) <> Len(SecondWord) Then
'check each letter appears the same number of times
For i = 1 To Len(Letters)
If LetterDifferent(Mid(Letters, i, 1)) Then
'if we get here, it is an anagram!
'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, " ", ""))
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
LetterDifferent = False
Sub GiveVerdict(IfSame As Boolean)
'put the result in the answer cell
If IfSame Then
Range("C6").Value = "Perfect anagrams"
Range("C6").Value = "Not anagrams"
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.
|Parts of this blog|
25 Aytoun Street