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 line of the code. Otherwise, you can execute the code line by line, 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 −

  • Click the VIEW tab on the Ribbon.
  • Click Macros.
  • Select View Macros from the dropdown list.

The Macro dialog box appears.

  • Click the macro name.
  • Click the Step into button.
Step into

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

Macro Code

Stepping into the code from the VBA editor

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

  • Click the DEVELOPER tab on the Ribbon.
  • Click Visual Basic. The VBA editor opens.
  • Click the module that contains the macro code.

The macro code appears in the code window.

Stepping

  • Click the Debug tab on the Ribbon.

  • Select Step into from the dropdown list.

Dropdown

The first line in the macro code will be highlighted. The code is in the debugging mode and the options in the Debug dropdown list will become active.

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 line 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 lines 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 accomplish 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 familiar to you and VBA editor debugging options make your life simple. Even otherwise, it will not take much time to master this skill if you have learnt VBA and understand the code.

Advertisements