Excel Macros - Debugging a Code

You have learnt that the macro is stored as VBA code in Excel. You have also learnt that you can directly write code to create a macro in VBA editor. However, as with the case with any code, even the macro code can have defects and the macro may not run as you expected.

This requires examining the code to find the defects and correct them. The term that is used for this activity in software development is debugging.

VBA Debugging

VBA editor allows you to pause the execution of the code and perform any required debug task. Following are some of the debugging tasks that you can do.

Stepping Through Code

Using Breakpoints

Backing Up or Moving Forward in Code

Not Stepping Through Each Line of Code

Querying Anything While Stepping Through Code

Halting the Execution

These are just some of the tasks that you might perform in VBA s debugging environment.

Stepping Through the Code

The first thing that you have to do for debugging is to step through the code while executing it. If you have an idea of which part of the code is probably producing the defect, you can jump to that pne of the code. Otherwise, you can execute the code pne by pne, backing up or moving forward in the code.

You can step into the code either from Macro dialog box in your workbook or from the VBA editor itself.

Stepping into the code from the workbook

To step into the code from the workbook, do the following −

Cpck the VIEW tab on the Ribbon.

Cpck Macros.

Select View Macros from the dropdown pst.

The Macro dialog box appears.

Cpck the macro name.

Cpck the Step into button.

VBA editor opens and the macro code appears in the code window. The first pne in the macro code will be highpghted in yellow color.

Stepping into the code from the VBA editor

To step into the code from the VBA editor, do the following −

Cpck the DEVELOPER tab on the Ribbon.

Cpck Visual Basic. The VBA editor opens.

Cpck the module that contains the macro code.

The macro code appears in the code window.

Cpck the Debug tab on the Ribbon.

Select Step into from the dropdown pst.

The first pne in the macro code will be highpghted. The code is in the debugging mode and the options in the Debug dropdown pst will become active.

Backing Up or Moving Forward in the Code

You can move forward or backward in the code by selecting Step Over or Step Out.

Not Stepping Through Each Line of Code

You can avoid stepping through each pne code, if you identify a potential part of the code that needs to be discussed, by selecting Run to Cursor.

Using Breakpoints

Alternatively, you can set breakpoints at specific pnes of code and execute the code, observing the results at each breakpoint. You can toggle a breakpoint and clear all breakpoints if and when required.

Using Watch

You can add a watch while debugging, to evaluate an expression and stop the execution when a variable attains a specific value. This means that you configure a watch expression, which will be monitored until it is true and then the macro will halt and leave you in break mode. VBA provides you with several watch types to select from, in order to accomppsh what you are looking for.

Halting the Execution

During debugging, at any point of time, if you have found a clue on what is going wrong, you can halt the execution to decipher further.

If you are an experienced developer, the debugging terminology is famipar to you and VBA editor debugging options make your pfe simple. Even otherwise, it will not take much time to master this skill if you have learnt VBA and understand the code.