What is Excel VBA Breakpoint?
A break in VBA is the point at which you tell the VBA code to pause, especially during debugging. There are two kinds of breakpoints in VBA. One is for debugging, and the other is for exiting loops. As part of code testing in VBA, we must test the code line by line to check for bugs and fix them at specific lines. We can stop the code execution at a specific line by applying a breakpoint.
Another breakpoint is used for exiting loops whenever a specified condition is met, even though the mentioned loop is still running. Generally, this kind of loop breakpoint is used in FOR NEXT and DO WHILE loops.
For example, look at the following image.
The red highlighted line is the breakpoint applied in the macro procedure. When we try to execute the code, the macro will execute until it finds the breakpoint(red dot) and will stop there, and even the right highlighted line will not be executed.
Table of contents
- By using the VBA break, we can apply breakpoints and debug code if any issues persist.
- A red line indicates the breakpoint applied to the code.
- VBA breakpoint allows us to stop the code at a specific line.
- Exit…For is used to come out of a loop whenever the specific logical condition is met, even though there are many times a loop is supposed to run.
Why do we need a breakpoint in VBA?
In any programming language, there are bound to be bugs initially. When we try to resolve them, we must test them line by line. If you want to run through many lines of code, it is necessary to apply breakpoints to fix them quickly.
For example, assume we have 50 lines of code, and we are sure that the first 30 lines of code have no bugs. We must check the code only from the 31st line. In this case, we must execute the first 30 lines of code line by line to reach the 31st line. However, using the breakpoint, we can quickly run the first 30 lines of code in one click and stop the execution at the 31st line. Now, we can use the step into method (F8 is the shortcut key to execute codes line by line) to find the bug from the 31st line.
When it comes to a loop, we may have to run the loop 100 times but in case certain conditions are satisfied when the loop is at the 50th time, we may break the loop before we reach the 100th time.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How to Apply Breakpoints in VBA?
We can apply breakpoints for debugging the code in two ways: the manual way and the shortcut key.
Let us first see the debugging breakpoint before the loop breakpoint.
Example #1 – Breakpoint for Debugging
#1 – Manual Method
We can apply the breakpoint for debugging through the manual and shortcut methods. Let us show you an example of applying the breakpoint using the manual method.
For example, look at the following code.
Dim x As Long
Dim y As String
ActiveSheet.Range(“A1”).Value = “Excel VBA Break Point”
ActiveSheet.Name = “VBA Break Point”
x = “New York”
y = “London”
Range(“B1”).Value = x
Range(“C1”).Value = y
Let’s execute the code in one shot by pressing the F5 shortcut key and see what happens.
When we executed the code, we encountered an error “Run-time error ‘13’:” Type Mismatch. Click on “Debug,” and it will take us to the line where it has encountered an error.
The yellow highlighted line is where the error is shown.
Therefore, when we try to debug the code, we must execute the code till the line where we got an error. To do this, we can apply a break point.
For the error line of the code, click on the left margin side of the error line code.
As soon as we click on the left margin of the code, we a breakpoint is applied, and the whole line of code is highlighted in red.
Now, if we execute the code, it will run till it sees a break point and stops at the line of the break point. We can see a mixture of red and yellow on the breakpoint line.
Thus, we can debug the code by applying the break point at a specific line of the code.
Solution: Now, let’s look at the reason for getting the error. We get the error message at the line x = “New York.”
Here, we assign a value to the variable x as “New York,” and the error we get is a type mismatch error.
Let’s look at the first line of the code, where we have defined a variable.
In this line, we have defined a variable “x,” and assigned it a VBA data type “Long” which can hold only numerical values.
However, now look at the error code line.
In this line, we have assigned a value “New York” to the variable “x” which can hold only a numerical value. Hence, we get the error “Type mismatch.”
To resolve this error, we must change the data type from “Long” to “String.”
Now the code will run without any errors.
#2 – Shortcut Method
We can also apply the breakpoint using the shortcut method. The excel shortcut key to apply the breakpoint is F9.
Place your cursor at the line of the error code.
After placing the cursor in front of the error code, press the shortcut key F9. It will apply the breakpoint to the line.
This red line indicates that VBA break mode is on.
Example #2 – Break Point in Loops
Coming out of loops is one of the essential parts of coding if the specified condition is met when the code is running within the VBA loop.
For example, look at the following FOR NEXT LOOP code.
Dim k As Long
For k = 2 To 11
If Cells(k, 1).Value = “” Then
Cells(k, 1).Value = k – 1
MsgBox “Found a non blank cell and exiting the loop”
It will insert serial numbers from 1 to 10 in cells A2 to A11. Before we run this code, let’s look at the worksheet area from A2 to A11.
We have some values in cell A7, but the current FOR LOOP code does not bother about the values in the range A2 to A11. When we run the code, it will insert serial numbers from 1 to 10, and the loop runs ten times in this scenario.
The code overwrote the value which was there in cell A7. Assume we need to exit or VBA break while the loop is running, then we need to apply the logical condition, and EXIT FOR will be implemented. Look at the following code.
We have applied the IF condition inside the loop to check if the current looping cell is blank or not.
If the current looping cell is blank, then it will insert a serial number, and if it is not blank, it will display a message saying, “Found a non blank cell and exiting the loop.” It will break the loop with the keyword “Exit For.”
Before we execute the code, let’s look at the worksheet.
Execute the code and see the result.
As seen, the loop ran five times, and on the 6th attempt, it found the value in cell A7. Hence, it displayed the VBA break code message and triggered the VBA breakpoint through “Exit For.”
Click on “Ok,” and the “FOR loop” will be broken.
Important Things to Note
- The shortcut key to apply the VBA breakpoint is F9.
- Once the VBA break links are applied, the execution just stops on that line and will not go forward until the user executes manually.
- When the Exit loop is applied with the VBA breakpoint, the loop will no longer run for the remaining number of times.
Frequently Asked Questions (FAQs)
For example, look at the following code.
A VBA break point has been applied on the 3rd line of the code. If we want to stop the breakpoint, we can click on the left margin where we have a red point circle.
To come out of VBA break mode, place your cursor at the break point code line and press the shortcut key F9.It will remove the break point from that line.
When we apply a logical condition to break a loop, the condition should be satisfied to break the loop. If not, the VBA Break will not stop the loop from running it.
In case of error handling, we should use the “On Error” statement to break the error in the code.
This article must be helpful to understand VBA Break, with its formula and examples. You can download the template here to use it instantly.
This has been a guide to VBA Break. Here we explain how to apply breakpoint in VBA for debugging the code with examples & downloadable excel template. You can learn more from the following articles –