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 three 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.
|
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.
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.