When you write VBA, or any programming language, you are going to encounter errors in it, or should we call them unintended features? Basically you can't write any substantial amount of code without needing to fix errors and make sure that it works as it's supposed to.This post is going to look at the tools I use the most in the VBA editor to help debugging VBA code.
These debugging tools not only let you fix problems, they allow you to gain a better understanding of code, so can be used to familiarise yourself with code written by someone else.
The first thing you need to do is open the VBA editor, press ALT + F11 in Excel.
Of course you need a bit of code to debug, so here's a trivial example. This code is available to download.
Enter your email address below to download the sample workbook.
Sub ListNumbers() Dim counter As Integer Dim myRange As Range Set myRange = Range("B11") For counter = 0 To 10 myRange.Offset(counter).Value = counter - 1 Next counter ChangeFont End Sub Sub ChangeFont() ActiveCell.Font.Bold = True End Sub
The Debugging Tools
When I say 'tools' what I am referring to are ways the VBA editor allows us to interact and run the code.
If you look at the Debug menu in the VBA editor this is what you will see
Almost all the tools (or commands) have a shortcut key and you should learn these as it's a much quicker way to debug, rather than going to the menu all the time.
Running Code : F5
Whilst this isn't listed in the Debug menu, you will use it when debugging.
When you place the cursor into a sub, either with a mouse or using the keyboard, press F5 to run that sub.
Also, if you are stepping through code (F8 - see below) and get to a point where you are happy with what you have checked, you can press F5 to execute the rest of the code.
You can't use F5 or F8 to run a sub that requires parameters, or to execute a function.
Stepping Through Code : F8
With the cursor in the sub, press F8 to execute one line of code at a time.
The next line to be executed will be highlighted in yellow, with a yellow arrow pointing to it.
Each time you press F8, the line highlighted in yellow is executed, and the next line to be run is highlighted.
Notice that the DIM statements declaring variables are not included in this and are stepped over.
You can press F8 to execute every line of code, one at a time, until the sub ends.
Stepping Over Code : SHIFT + F8
If your code calls another sub, you may not want to step through each line of code in that 2nd sub. In this case you can 'step over' the 2nd sub and immediately continue executing the code in your 1st sub.
My sub ListNumbers calls another sub ChangeFont. When code execution gets to the call for ChangeFont, I can press SHIFT + F8, the ChangeFont sub is executed, but I don't have to wait for this, and the code pauses again at the next line which is the End Sub statement.
Stepping Out of Code : CTRL + SHIFT + F8
If I find myself in a called sub, either intentionally or accidentally because I pressed F8 too many times, I can Step Out of that sub by pressing CTRL + SHIFT + F8.
What this means is that the code in the sub will be executed, but code execution will pause at the next statement after the call to the sub.
So in my code, if I am in ChangeFont, stepping out of this sub will pause the code at the End Sub statement of ListNumbers.
Breakpoints : F9
A breakpoint is a line in your code where you tell VBA to pause and wait.
To create a breakpoint you can either position the cursor on the desired line and press F9, or just click in the margin beside that line. A dark red/brown dot will appear beside the line in question to indicate a breakpoint has been set, and that line of code is highlighted in the same color.
Clicking on the dot again will remove the breakpoint, as will pressing F9
You can set as many breakpoints as you want so removing them all can take some time if you have a lot of code. To make this easier, pressing CTRL + SHIFT + F9 removes all breakpoints.
You can use breakpoints to interrupt the code in a function and check that it is working.
Run to Cursor : CTRL+ F8
This works in a similar way to breakpoints, but are not not set. With a breakpoint, that breakpoint exists until you remove it. With run to cursor, it only works once.
To do this, position your cursor on the line where you want code execution to pause and press CTRL + F8
Changing the Next Line to Execute
As you are stepping through code, you'll notice that the next line to execute is highlighted in yellow, and has a yellow arrow pointing to it in the margin.
You can use your mouse to drag this arrow to whatever line you want in the same sub (provided it's an executable line), and execution will then continue from there.
This is extremely useful if you want to alter the value of something or fix a bug and then re-execute the code to see if your changes have the desired effect.
So if I have started my For loop where the variable counter takes the values from 0 to 10, and then decide I want counter to actually go up to 20, I just drag the yellow arrow back up to the start of the For loop, change 0 to 20, then start stepping through the loop again.
That's great I hear you say, but I'm not sure what value counter has. So how do we check that?
Checking the Value of Variables
There are a number of ways to do this, the easiest is to just hover your mouse over the variable while your are debugging:
Or you can use the Debug.Print statement, I use this a lot. This will print the vale of a variable to the Immediate Window. CTRL + G to show the Immediate Window.
Insert the Debug.Print statement directly into your code like so:
This will only work with 'simple' data types (integer, string, long etc) but won't work with things like ranges.
You can also type the statement directly into the Immediate window (and hit enter) and get the variable's value
Debug.Print will print any string so you can make it more descriptive if you like e.g.
Debug.Print "At this precise moment in time, counter has the value " & counter & ", thanks for asking."
You can Watch a variable and its value is shown in the Watch window. If your Watch window isn't showing, turn it on from the View menu in the VBA editor.
To watch a variable, place the cursor in the variable, right click and then click on Add Watch.
You now have 3 choices. Don't worry about the Context as you shouldn't need to change this. What you need to decide is do you want to simply watch the variable. In which case you'll see its value change as the code executes. Or do you want to break (like breakpoints) when the variables value is True (for Boolean variables) or break when the value changes.
If we are just interested in seeing the value as it changes then choose Watch Expression, otherwise choose the appropriate Break option.
You'll now be able to see the value of your variable change in real time.
The Locals window is like the Watch window, you can see variable values in real time. Use the View menu to turn it on.
Altering Code While Debugging
As I've demonstrated, you can alter your code on the fly while you are debugging. This is a great way to iron out issues. Just change the offending code, then use your mouse to drag the yellow arrow to change the next line to be executed.
Interacting With the Workbook While Debugging
Similar to changing code whilst debugging, you can interact with your workbook(s) while debugging.
With the code paused, you can change the data in the worksheet, select different sheets or workbooks etc.
Be aware of doing this as it may cause unintended results for any code that refers to the ActiveCell or ActiveWorkbook as my ChangeFont sub does.
Sudip Chandra Pal
I am very new to vba and can not get the following code to work. What I am trying to achieve is to get the value from Sh1 to Sh2 for the items which have matching section in Sh1 with those in Sh2.
The code I have written is as follows:
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sec As String
Dim sec1 As String
Dim i As Integer
Set sh1 = Sheet2
Set sh2 = Sheet5
i = 47
For i = 47 To 55
sec = ActiveCell
”Application.VLookup(sec, sh2.Range(“i37:i44”), 1, 0)
If sh1.Cells(i, 3) = sh2.Range(“i37:i45”) Then
sh1.Cells(i, 4).Value = sh2.Range(“i37:i45”).Offset(0, 1).Value
sec = ActiveCell represents only a single cell, it does not move to another cell even if this code is placed within the loop. If you want this value to change at each loop iteration, use sec=sh1.cells(i,3).Value
can you please upload a sample file on our forum?
Will be easier to help you.
Hugh VBA program. Creator deceased. One sub calls two subs and exits, EXCEPT it exits to who knows where! End sub goes non-yellow and excel/vba is very dead, including the X….completely dead!
I suspect that the stack has been corrupted so the return goes to some useless address.
Anyway I can view the assembly language? If I can find the address of the CALL or RETURN I can use the ms debugger, of course….
P.S. I know VB.NET extremely well. I know the x86 family pretty well. Using Win7 as the host…..doing this for an 88 year old friend who owns the program but is not a programmer!
Please start a topic on the forum and supply the workbook in question.
exactly what I was looking for – great explanation and tutorial – thanks
You’re welcome Rory.
I have a workbook with 21 columns. I used to allow users for multi-user with their own password. I have a VBA code for the auto sorting the workbook. When I used the protected and shared workbook with define a password the code doesn’t work. Can you help me with this?
my code is below:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column 11 Or Target.Columns.Count > 1 Then _
If Target.Column 13 Or Target.Columns.Count > 1 Then _
If Target.Column 14 Or Target.Columns.Count > 1 Then _
If Target.Column 21 Or Target.Columns.Count > 1 Then _
Dim tmp As Variant
tmp = Cells(Target.Row, 21).Formula ‘save contents
On Error GoTo Enable_Events
Application.EnableEvents = False
Cells(Target.Row, 21) = “#$”
Range(“K1”).Sort Key1:=Range(“K1”), Order1:=xlAscending, Header:=xlYes
Cells(Application.Match(“#$”, Columns(21), 0), 11).Select
Range(“M1”).Sort Key1:=Range(“M1”), Order1:=xlAscending, Header:=xlYes
Cells(Application.Match(“#$”, Columns(21), 0), 13).Select
Range(“N1”).Sort Key1:=Range(“N1”), Order1:=xlAscending, Header:=xlYes
Cells(Application.Match(“#$”, Columns(21), 0), 14).Select
Range(“U1”).Sort Key1:=Range(“U1”), Order1:=xlDescending, Header:=xlYes
Cells(Application.Match(“#$”, Columns(21), 0), 21).Select
Cells(Selection.Row, 21) = tmp ‘restore contents
Application.EnableEvents = True
Can you upload a sample file on our forum? Here is a link. (create a new topic after sign-in)
It will be much easier to understand your situation.
Nice article. Other that the above methods, I also use MSGBOX regularly to display variables.
A couple of things on debug.print in the “immediate window”.
– the question mark equates to the statement debug.print in the immediate window so “? counter” (without the quotes)does the same thing as debug.print counter.
– on it only working on simple data types, you can examine ranges etc by using their properties. ie I have rng defined as a range and in the immediate window “? rng.address” (without the quotes) will return something like $A$1:$B$5 or to work out where you are ? activecell.address or ? activesheet.name
Thanks for that Alex, good tips.
Thanks I loved the article. I will squirel away some of the short-keys mentioned in here.
I often need to break based on a condition ie at some point in a loop.
You might want to add a reference to the Stop command and debug.assert command which allows you to do that.
Thanks Alex. I’ll look to add something about Stop and Debug.Assert.
Many thanks for this article on debugging, however I haven’t seen any reference to one vital bit of VBA – Option Explicit.
Failure to use this statement can give beginners a severe headache and much head scratching!
Thanks Tudor. Option Explicit isn’t part of the debugging tools but I do know what you mean.
I always use it too (look in the sample workbook) and it makes sure that your variables are declared.