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
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
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.
|
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.
Parts of this blog |
---|
Some other pages relevant to the above blogs include:
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 2023. All Rights Reserved.