BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Debugging Macros (this blog)
- Ways to Step Through Code in VBA
- Setting Breakpoints and the VBA Stop Statement
- Debug.Print and the Immediate Window
- Other Useful Debugging Tools
This blog is part of our Excel VBA tutorial. You can also learn to program in Visual Basic for Applications on one of our scheduled or tailored VBA training courses.
Posted by Andy Brown on 25 November 2011
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
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"
'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.