Excel Macros 简明教程

Excel Macros - Debugging a Code

您已经了解到宏存储为 Excel 中的 VBA 代码。您还了解到可以在 VBA 编辑器中直接编写代码来创建宏。但是，与任何代码一样，即使是宏代码也可能存在缺陷，而且宏可能无法按预期运行。

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 编辑器允许您暂停代码的执行并执行任何必需的调试任务。以下是您可以执行的一些调试任务。

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.

  1. Stepping Through Code

  2. Using Breakpoints

  3. Backing Up or Moving Forward in Code

  4. Not Stepping Through Each Line of Code

  5. Querying Anything While Stepping Through Code

  6. Halting the Execution

这些只是您可能在 VBA 调试环境中执行的某些任务。

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.

您可以从工作簿中的宏对话框或 VBA 编辑器本身进入代码。

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

Stepping into the code from the workbook

若要从工作簿进入代码，请执行以下操作 −

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

  1. Click the VIEW tab on the Ribbon.

  2. Click Macros.

  3. Select View Macros from the dropdown list.

出现宏对话框。

The Macro dialog box appears.

  1. Click the macro name.

  2. Click the Step into button.

step into

VBA 编辑器打开，宏代码出现在代码窗口中。宏代码中的第一行将以黄色突出显示。

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

Stepping into the code from the VBA editor

若要从 VBA 编辑器进入代码，请执行以下操作 −

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

  1. Click the DEVELOPER tab on the Ribbon.

  2. Click Visual Basic. The VBA editor opens.

  3. Click the module that contains the macro code.

宏代码出现在代码窗口中。

The macro code appears in the code window.

stepping

  1. Click the Debug tab on the Ribbon.

  2. 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

在调试过程中，您可以添加一个监视器来计算一个表达式，并在变量达到特定值时停止执行。这意味着您配置一个监视器表达式，它将被监视，直到为真，然后宏将停止并让您进入断点模式。VBA 为您提供了多种监视器类型以供选择，以便实现您的目标。

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.

如果您是一位经验丰富的开发人员，则调试术语对您来说并不陌生，并且 VBA 编辑器调试选项使您的生活变得轻松。即使没有经验，如果您学习过 VBA 并理解代码，掌握这项技能也不会花费太多时间。

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.