调试VBA，查找问题和解决方法故障排除方法 [英] Debugging VBA, Locating Problems & Troubleshooting Methods

问题描述

调试VBA代码有哪些方法?

具体是:

• 单步执行代码
• 断点和Stop命令
• Debug 命令
• 本地人&看窗户
• 调用堆栈

推荐答案

调试VBA代码

此页面介绍了调试VBA代码的方法.

Debugging VBA Code

This page describes methods for debugging your VBA code.

调试程序是软件开发中最重要的步骤之一.了解VBA的调试工具可以使调试更容易，更高效.本页介绍了一些VBA内置的调试工具，您可以在测试和调试应用程序时使用它们.

Debugging a program is one of the most important steps in software development. Knowledge of VBA's debugging tools can make debugging easier and more productive. This page describes several of VBA's built-in debugging tools you can use when testing and debugging your application.

调试代码的第一种方法是一次单行执行代码.要单步执行代码，请将光标放在要分析的代码的第一行上，然后按 F8 或在 Debug 菜单上选择 Step Into .将要执行的下一行代码将在黄色背景中以黑色字体显示.请注意，突出显示的代码行尚未执行-这是要执行的下一行.

One of the first methods to debug code is to step through the code one line at a time. To step through code, put the cursor on the first line of code to be analyzed and press F8 or choose Step Into on the Debug menu. The next line of code to be executed will be displayed in yellow background with a black font. Note that the highlighted line of code has not yet been executed -- it is the next line to execute.

如果您的代码调用另一个过程，则使用 F8 单步执行该代码将使执行过程逐行输入所调用的过程.如果要不逐步执行被调用的过程，请按 Shift + F8 .这将执行被调用的过程，然后在调用该过程后在代码行上暂停.如果您已经完成了某个过程，则可以按 Ctrl + F8 逐行恢复代码执行.在分步模式或断点处暂停的任何时候(请参见下文)，都可以在 Run 菜单，以使VBA运行完成或直到遇到暂停语句.

If your code calls another procedure, stepping through the code with F8 will cause execution to enter the called procedure in a line-by-line sequence. If you want to execute the called procedure without stepping through it, press Shift+F8. This will execute the called procedure and then pause on the line of code after calling the procedure. If you are already stepping through a procedure, you can press Ctrl+ F8 to resume code execution line-by-line. At any time you are paused either in step-by-step mode or at a breakpoint (see below), you can press F5 or Continue from the Run menu to cause VBA to run to completion or until a pause statement is encountered.

每当您在逐步模式下暂停时，都可以从立即窗口查询或更改变量的值.

Whenever you are paused in step-by-step mode, you can query or change a variable's value from the Immediate window.

断点是放置在代码行上的标记，它使执行立即在执行该行之前暂停.您可以在代码行中添加断点，方法是将光标放在有问题的代码行上，然后按 F9 ，然后在 Debug (调试)上选择 Toggle Breakpoint (切换断点)strong>菜单，或单击代码行旁边的左边距.设置断点后，该行以砖红色背景显示，并带有白色字体.运行代码时，执行将立即在带有断点的代码行之前暂停，并将在黄色背景上以黑色字体显示.请注意，黄色行尚未执行-这是要运行的下一行代码.

A breakpoint is a marker placed on a line of code that causes execution to pause immediately before executing that line. You can add a breakpoint to a line of code by putting the cursor on the line of code in question and pressing F9, choosing Toggle Breakpoint on the Debug menu, or clicking in the left margin next to the line of code. When a breakpoint is set, the line is displayed in brick-red background with a white font. When you run the code, execution will pause immediately before the line of code with the breakpoint and will display it in yellow background with a black font. Note than the line in yellow has not yet been executed -- it is the next line of code to run.

当代码在断点处暂停时，您可以在即时"窗口中发出命令以更改或查询变量的值.要查看变量的内容，请输入?字符，后跟变量名称，然后按 Enter .您可以通过在立即窗口中输入 VariableName = NewValue 并按 Enter 来更改变量的值.

While the code is paused at the breakpoint, you can issue commands in the Immediate window to change or query a variable's value. To view the content of a variable, enter a ? character followed by the name of the variable and then press Enter. You can change a variable's value by entering VariableName = NewValue in the Immediate window and pressing Enter.

如果立即"窗口不可见(通常在VBA编辑器屏幕的底部)，请按 Ctrl + G 或选择立即窗口从查看菜单中使该窗口可见.

If the Immediate window is not visible (typically at the bottom of the VBA Editor screen), press Ctrl+G or choose Immediate Window from the View menu to make the window visible.

要删除断点，请将光标放在代码行上，然后按 F9 .您可以通过从 Debug 菜单中选择 Clear All Breakpoints 清除所有断点，或按 Ctrl + Shift + F9 .VBA还提供了 Stop 命令.这样只会停止该行代码的执行，并进入中断模式.

To remove a breakpoint, put the cursor on the line of code and press F9. You can clear all breakpoints by choosing Clear All Breakpoints from the Debug menu or pressing Ctrl+Shift+F9. VBA also provides the Stop command. This simply stops code execution on that line of code and enters break mode.

完成代码调试之后，请务必返回并清除所有断点(从 Debug 菜单中选择 Clear All Breakpoints )或按 Ctrl + Shift + F9 )，并确保删除或注释掉所有Stop语句.

Once you are finished debugging the code, be sure to go back and clear all breakpoints (choose Clear All Breakpoints from the Debug menu or press Ctrl+Shift+F9) and be sure to remove or comment out all Stop statements.

当您在断点处暂停或处于逐步模式时，可以更改要执行的下一行，可以在当前行之前重新运行一段代码，也可以在该行之后跳过语句.右键单击要恢复执行的行，然后右键单击并选择设置下一条语句或从运行菜单中选择设置下一条语句.执行将在选定的代码行处恢复.

When you are paused at a breakpoint or in step-by-step mode, you can change the next line to be executed, either before the current line to re-run a section of code, or after the line to skip statements. Right-click the line where you want execution to resume and right-click and choose Set Next Statement or choose Set Next Statement from the Run menu. Execution will resume at the selected line of code.

VBA为Debug对象提供了两个属性，即Print和Assert，您可以使用它们显示变量的值并控制程序流. Debug.Print 会将其后的内容写入立即窗口.代码执行不中断.在立即窗口中显示文本后，代码执行将继续运行.您可以在 Debug.Print 语句中将文字文本与变量名称混合在一起.例如，

VBA provides a Debug object with two properties, Print and Assert that you can use display a variable's value and to control the program flow. Debug.Print will write what follows it to the Immediate window. Code execution is not interrupted. After displaying the text in the Immediate window, code execution continues to run. You can mix literal text with variable names in the Debug.Print statement. For example,

Debug.Print "The value of variable X is: " & X

您可以在即时窗口中一次用逗号分隔显示多个变量.例如，

You can display several variables at once in the Immediate window by separating them with commas. For example,

Debug.Print X, Y, Z

Debug.Assert 命令是一个条件断点，如果Assert语句后的表达式为False，将导致在Debug语句上暂停执行.例如，

The Debug.Assert command is a conditional breakpoint that will cause execution to pause on the Debug statement if the expression that following the Assert statement is False. For example,

Debug.Assert Var >= 0

如果 Var> = 0 为 False ，这将在 Debug.Assert 语句上暂停；也就是说，如果 Var 为负数，它将暂停.当条件为False而不是True时，执行被暂停了，这似乎倒向后看，但是Assert方法是从C语言采用的，其用法与C中的用法相同.

This will pause on the Debug.Assert statement if Var >= 0 is False; that is, it will pause if Var is negative. It may seem backwards that execution is paused when the condition is False rather than True, but the Assert method was adopted from the C language, and its usage remained the same as in C.

完成调试后，请确保删除或注释掉 Debug.Print 和 Debug.Assert 语句.通常，您不希望这些语句在正常使用应用程序期间起作用.

Be sure to remove or comment out the Debug.Print and Debug.Assert statements when you are finished debugging. You generally don't want these statements to be operative during normal usage of your application.

通过局部程序"窗口，您可以在逐步执行过程时查看过程中所有变量的值.要显示本地"窗口，请从视图菜单中选择本地窗口.与从即时"窗口检查值相比，使用本地"窗口显示变量值更容易.对于简单变量类型(例如Long和String变量)，该值显示在一行上.对于复杂的类型或对象(例如Range变量)，其属性以可折叠的树状结构显示.

The Locals windows allows you to view the value of all the variables in a procedure when you are stepping through the procedure. To display the Locals window, choose Locals Window from the View menu. Using the Locals window is easier to display variable values than examining the value from the Immediate window. For simple variable types (e.g., Long and String variables), the value is displayed on one line. For complex types or objects (e.g., a Range variable), its properties are displayed in a collapsible tree-like structure.

监视"窗口显示所有有效的监视.您可以通过从视图菜单中选择监视窗口来显示监视窗口.监视是当表达式为True或正在监视的变量更改值时，向VBA暂停代码的指令.要在变量上创建监视，请打开监视窗口，然后在监视窗口中单击鼠标右键，然后从弹出菜单中选择添加监视... 或选择添加监视... 从调试"窗口.在添加监视"对话框中，在表达式"文本框中输入要监视其值的变量名称.然后选择值更改时中断.运行代码时，执行将在修改变量值的行之后的行暂停.代码暂停时，该变量的值将已经更新.

The Watch window displays all the Watches in effect. You can display the Watch window by choosing Watch Window from the View menu. A Watch is an instruction to VBA to pause code when an expression is True or when the variable being watched changes value. To create a Watch on a variable, open the Watch window and right-click in the Watch window and choose Add Watch... from the popup menu or choose Add Watch... from the Debug windows. In the Add Watch dialog, enter in the Expression text box a variable name whose value you want to watch. Then choose Break When Value Changes. When you run the code, execution will pause at the line after the line that modifies the variable's value. When code pauses, the value of the variable will have already been updated.

要删除手表，请在手表"窗口中右键单击它，然后从弹出菜单中选择删除手表.要修改手表，请在手表"窗口中右键单击它，然后从弹出菜单中选择编辑手表.

To remove a Watch, right-click it in the Watch window and choose Delete Watch from the popup menu. To modify a Watch, right-click it in the Watch window and choose Edit Watch from the popup menu.

调用堆栈是VBA维护的数据结构，该结构跟踪哪个过程称为另一个过程.例如，如果过程 AAA 调用了 CCB 的 BBB ，则调用堆栈"窗口将显示以最新过程开头的过程列表，以及在此之下，执行以达到当前位置的过程链.您可以通过从 View 菜单中选择 Call Stack 来查看Call Stack.这对于跟踪最终在当前位置执行的流程很有用.不幸的是，没有编程方法可以从调用堆栈中获取信息.

The Call Stack is a data structure maintained by VBA that tracks which procedure called another procedure. For example, if procedure AAA calls BBB which calls CCC, the Call Stack window will display the list of procedures starting with the most recent procedure and below that, the chain of procedures that were executed to get to the current position. You can view the Call Stack by choosing Call Stack from the View menu. This is useful to track the flow of execution that ended up in the current location. Unfortunately, there is no programmatic way to get information from the call stack.

_{来源:Pearson Software Consulting的Chip Pearson: http://www.cpearson.com/Excel/DebuggingVBA.aspx}

这篇关于调试VBA，查找问题和解决方法故障排除方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！