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.

  1. Debugging Macros (this blog)
  2. Ways to Step Through Code in VBA
  3. Setting Breakpoints and the VBA Stop Statement
  4. Debug.Print and the Immediate Window
  5. 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):

Splitting windows in Win 7

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:

Debug toolbar

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:

Debugging - first line highlighted

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:

Next line after variable declaration

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:

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:

Value of variable shown in tooltip

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:

Two words in different cells

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:

  1. 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.
  2. The CheckAnagrams routine then compares the lengths of the two words entered, and reports an error if they're not the same.
  3. 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.

 

This blog has 0 threads Add post