Debugging Macros
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).
Splitting the Screen
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!
Basic Debugging
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.
Stopping a Macro Prematurely
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.
Showing the Value of Variables
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.
Our Example
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"
Private FirstWord As String
Private SecondWord As String
Sub CheckAnagrams()
Dim i As Integer
GetWords
If Len(FirstWord) <> Len(SecondWord) Then
GiveVerdict False
Exit Sub
End If
For i = 1 To Len(Letters)
If LetterDifferent(Mid(Letters, i, 1)) Then
GiveVerdict False
Exit Sub
End If
Next i
GiveVerdict True
End Sub
Sub GetWords()
FirstWord = Range("C2").Value
SecondWord = Range("C4").Value
FirstWord = LCase(Replace(FirstWord, " ", ""))
SecondWord = LCase(Replace(SecondWord, " ", ""))
End Sub
Function LetterDifferent(ThisLetter As String) As Boolean
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)
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.
