Debugging VBA Code in Excel

When we use VBA to write code in excel or when we any programming language to write code we may encounter errors that need to be analyzed, checked, and fixed. To overcome this problem most IDEs and programming languages support debugging. Debugging is the process of executing your programming codes step by step and checking which the steps currently compiler is executing, what are their values, and what is their output, we will use debugging. Alternatively, we can also execute each programming statement one by one and check the output, it is almost similar to debugging but it is not convenient and took a lot of time. To learn more in detail about debugging please refer to Software Engineering | Debugging.

Debugging VBA Code

In this example, we will use the following sub-procedure to understand the VBA debugging process.

'define a sub procedure
Sub fun()

'declare 3 variables
Dim a, b, i As Integer
a = 0
b = 0

'iterating
For i = 1 To 5

a = a + 1
b = b + 1

Next

End Sub

Now, we will run our VBA macro. For this Click On Run Button In Toolbar.

Once, we run our VBA code, the entire program gets executed at once.

Getting Started with Debugging

To start with debugging process, we need to observe the values of each and every statement before and after execution. We can observe it in Locale Window. To insert the Locals Window in our VBA editor we will go to View > Locals Window.

Once, we click on the Locals Window option, it will add locals window at bottom of our VBA Editor.

This Locals Window will show the currently debugging Expression, its current Value, and the Type of the expression currently getting executed.

Step into Function (F8)

The Step Into function is used to execute the code step by step, it highlights the currently executing step in yellow color. In order to debug the sub-procedure, we need to Place The Cursor Inside VBA Editor > Toolbar > Debug > Step Into (F8).

Note: We can also use a shortcut F8 for calling Step Into function.

Once, we click on Step Into, it will start debugging and we can get the currently executing expression, its value, and type from the local's window.

Furthermore, we can use the shortcut for Step Into - F8 and the values of the variables in the local's window get updated. In case we need to execute the entire program in one go, we need to click on the Run button from the toolbar window.

Step over Function (Shift + F8)

The Step Over function is used for escaping the sub-procedure. For example, if we have a sub-procedure inside another sub-procedure and we want to escape it while debugging as soon as the cursor reaches that procedure we will call Step Over function, and it will escape that particular procedure.

Note: We can also use a shortcut as Shift + F8 for calling the Step Over function.

Here, we will add one more sub-procedure to our existing procedure. For this, we need to first add a module and define a sub-procedure. To add a module go to VBA Project > Right-Click > Insert > Module.

This will insert a module.

Now, we will insert a sub-procedure in our inserted module.

'define a sub-procedure
Sub addition()

'declare 3 variables
Dim a, b, res As Integer
a = 1
b = 2
res = a + b

End Sub

We will call our addition() sub-procedure in our main procedure.

Now, if we debug our sub-procedure, as soon as the debugger reaches to Call addition step, it will move to our addition procedure.

If we want to avoid our debugger to move to the addition() procedure, we need to call the Step Over(Shift + F8) function when the debugger reaches to Call addition step.

Step out Function (Ctrl + Shift + F8)

The Step Out function is used to move out of the sub-procedure. Let's understand it with the above example. If we want to escape the call of addition sub-procedure but, by mistake, we have called it by pressing too many Step Into(F8) functions. Then to move out of the sub-procedure we can call Step Out(Ctrl + Shift + F8), it will move out our debugger from the currently executing procedure. 

Breakpoints (F9)

When we execute our code, the breakpoint is a line where the debugger will pause and wait. For example, if there are thousands of lines of code and we want to keep track of a particular line of code, we can use breakpoint there. To place a breakpoint we need to place our cursor where we want the breakpoint and then press F9 or we can simply click inside the margin area beside the editor line.

Watch Window

As above, we have seen using the Locals Window we can keep track of all the variables. But, If we want to keep track of only one variable we can use Watch Window to keep watch on a single variable. In order to insert the watch window, we need to go to View > Watch Window.

Once, we click on the Watch Window option, it will get inserted at the bottom of the VBA editor.

We will be using the following sub-procedure to use the watch window.

'define a sub procedure
Sub fun()

'declare 3 variables
Dim a, b, i As Integer
a = 0
b = 0

'iterating
For i = 1 To 5

a = a + 1
b = b + 1

Next

End Sub

In order to add a variable to the Watch Window, we need to Select Variable > Right-Click > Add Watch.

Once, we click Add Watch option a popup will come we need to click on the OK button.

This will inset the variable to the Watch Window.

Now, if we will debug our VBA code, we can watch over the variable in the Watch Window.

ankur035

Follow

Improve

Article Tags :

• Excel
• Technical Scripter 2022
• Excel-VBA
• ExcelGuide