Debugging VBA Code: Adding Breakpoints
Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday’s code.
Christopher Thompson
The process of debugging IS the most stressful and frustrating stage in writing any type of computer-based code, VBA included! This process intensifies tenfold if you don’t know the techniques or how to use the built-in tools to assist you in troubleshooting your code. In the following sections we will cover a very basic debugging technique that will save you a ton of time as you work through fixing errors in your code. The debugging technique is Inserting A Breakpoint.
What Are Breakpoints?
A Breakpoint pauses the execution of your could on a designated line. This is helpful when debugging your VBA code because it allows you to run parts of your code that are bug-free while pausing the execution of your macro in areas that need further testing.
As an example let’s say you have 50 lines of code and the first 20 lines of the macro run perfectly. However, around line 21 you are receiving an unexpected result. Instead of stepping through each line of you code (by keying F8 twenty times!), you can simply add a breakpoint on line 21 of you code. You then can play your code (key F5) and your code will execute instantly through til it reaches line 21. Once the Visual Basic Editor is paused, you can then investigate what is causing your code to output the unexpected results. Proper use of breakpoints will save you HUGE amounts of time while you are debugging!
How To Insert A Breakpoint
Another great thing about breakpoints is they are extremely easy to add and remove from your code. Here are a couple ways you can add a breakpoint to a line of VBA code:
- Placing your cursor on a code line and keying F9
- Clicking in the left margin next to the desired code line
Note you CANNOT add breakpoints to lines containing
- Nothing in them (blank lines)
- Only code comments (lines with an apostrophe as the first character)
- Dimensioning statements (ie Dim x as Long)
Removing Breakpoints
To remove a single breakpoint you can either
- Rekey F9 when the cursor is within the breakpoint code line
- Re-click the breakpoint icon in the left margin
To remove all breakpoints use the shortcut key Ctrl + Shift + F9
Example Situations For Using Breakpoints
Only pause code when a certain criteria is true in an IF statement. In the example shown below, the macro will only break when a cells value is equal to 25.
The below image shows how you can pause your code before you run another VBA macro.
Below shows how you can pause the code right after it has completed going through a large loop. It would waste so much time if you had to individually step through each line in the loop just to get to the next section of your code. This setup allows you to run through the loop in a split second!
How Do You Debug?
Using Breakpoints will save you valuable time while attempting to fix errors in your code. I want to hear from you about other techniques everyone can use to help debug VBA code faster. Leave a comment below explaining your methods. I look forward to hearing your thoughts!
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.