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 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.
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:
'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
'break code here
'check each letter appears the same number of times
For i = 1 To Len(Letters)
If LetterDifferent(Mid(Letters, i, 1)) Then
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|
25 Aytoun Street