如何调试VBA及在Excel中调试VBA的方法

VBA语言専攻 2022-06-26

316

【分享成果，随喜正能量】有一些人的出现，就是来给我们开眼的。所以，人一定要禁得起假话，受得住敷衍，忍得住欺骗，忘得了承诺，放得下一切，百炼成精，淡定从容。。

VBA系列教程共十套，我已经全部完成，技术工具系列在不断推出。从这些教程及工具中，大家可以看到VBA确实是一门实用技术。正如我给VBA的定义：VBA是个人小型自动化处理的有效工具。利用好了，可以大大提高自己的劳动效率，而且可以提高数据的准确度。

对于学员而言，如果您是入门阶段可以打包选择7.1.3.9教程，第7是入门，第1是入门后的提高，第3字典是必备的VBA之精华，第9是实用的典型案例讲解。如果您有了VBA的一定基础可以根据自己的需要，进行教程的选择及工具的利用，教程提供的程序文件就如一座大型的代码库支持着大家的工作。

工具系列共分两类：一类是容易些的YZ系列，一类是较难些的NZ系列。这些工具是大家学习VBA的好帮手。对于急用的学员，可以立竿见影解决自己的工作效率问题。其中NZ系列工具还有专门版本资料，提供给已按部就班学习完我所有教程的学员。

VBA是面向对象编程，博大精深，包括我自己也在不断的学习和提高，这里我会分享些一些有用的片段，以弥补我教程的补足。这些片段为了大家更好的领会和掌握，我会用英语和汉语同时发布。今日的内容：如何调试VBA及在Excel中调试的方法

一 How to Debug VBA. Debugging VBA in Excel

如何调试VBA及在Excel中调试VBA的方法

Writing Visual Basic for Applications code is hard, but what about writing VBA code that works and to write it fast? Often I found many colleges struggling to get a few simple procedures to work. I was amazed that most of them preferred to keep at it, trying to pin down the one line of code the causes their issues, rather then spend a few minutes learning how to properly debug Excel code and get the job done much faster! Introducing today’s post on how to debug VBA code!

编写 Visual Basic for Applications 代码很困难，但是如何编写有效的VBA代码并能快速编写呢？我经常发现许多大学都在努力让一些简单的程序发挥作用。我很惊讶他们中的大多数人更愿意坚持，试图找出导致他们问题的一行代码，而不是花几分钟学习如何正确调试Excel代码并更快地完成工作！今天介绍关于如何调试VBA代码的！

Debugging is essentially a methodical process of locating and fixing bugs (or defects as some prefer). Basically find that bug and kill it! Excel VBA compared to other programming languages environments has one significant advantage – you can debug code on the fly without having to recompile the code. This makes life much easier and debugging some much more pleasant! So let’s jump right to it.

调试本质上是定位和修复错误（或某些人喜欢称之为缺陷）并使之能有条不紊的运行的过程。基本过程就是找到那个错误并杀死它！Excel VBA 与其他编程语言/环境相比有一个显着优势——您可以即时调试代码，而无需重新编译代码。这使我们的工作更轻松，调试更愉快！所以让我们直接开始吧。

1 DEBUG VBA: BASIC TERMS

调试VBA：基本术语

First we need to introduce some basic terms to facilitate the remaining part of this post:

首先，我们需要介绍一些基本术语，以方便本文的其余部分：

Executing/Running code – the process of running a macro

执行/运行代码 – 运行宏的过程

Debugging code– the process of finding and fixing bugs/defects

调试代码 – 查找和修复错误/缺陷的过程

Breakpoint – a line of code at which the execution of the macro will pause

断点 – 宏执行将暂停的一行代码

2 RUNNING BREAKING RESETING

运行/断开/复位

Let’s start with the tool bar at the top of the VBA Project Viewer window. You should find 3 buttons as shown below:

让我们从 VBA 项目查看器窗口顶部的工具栏开始。您应该找到3个按钮，如下所示：

The buttons allow you to do the following:

这些按钮允许您执行以下操作：

Run – run your macro (Sub) or UserForm. This is equivalent to the key shortcut F5

运行 – 运行宏（子）或用户窗体。这相当于按键快捷键 F5

Break – pause a running macro +. You can also stop a running macro by hitting the buttonCTRLBreakEsc

中断 – 暂停正在运行的宏 +。您还可以通过点击按钮来停止正在运行的宏CTRL Break Esc

Reset – reset a running/paused macro

重置 – 重置正在运行/暂停的宏

These are the basic commands for running macros.

这些是运行宏的基本命令。

3 BREAK POINTS

断点

Breakpoints specify lines of code at which the execution of your macro should pause when you debug VBA. They are convenient when you want to be sure your code does run through a certain loop of If statement.

断点指定调试 VBA 时宏执行应暂停的代码行。当您想要确保代码确实通过 If 语句的某个循环运行时，它们非常方便。

To add/remove a breakpoint simply left-click on the left gray bar in your VBA Project View next to your code. A red dot should appear indicating that you have specified a new breakpoint. Click on the dot again to remove the breakpoint.

要添加/删除断点，只需在 VBA 项目视图中代码旁边的左侧灰色栏上单击鼠标左键即可。应出现一个红点，指示您已指定新的断点。再次单击该点可删除断点。

Assertions – the right way to breakpoint errors

Assertions – 解决断点错误的正确方法

Often breakpoints are specified in places where error might occur. This may be cumbersome when you have loop running and are not sure when the error will occur or if you are aware of a condition that causes the error but are unable to catch it at the right moment. This is where you will want to use Debug.Assert.

通常在可能发生错误的位置指定断点。当您正在运行循环并且不确定错误何时会发生时，或者如果您意识到导致错误但无法在正确的时刻捕获它的情况，这可能会很麻烦。这是您需要使用 Debug.Assert 的地方。

How does Debug.Assert work? Say you are dividing to numbers and want to make sure the denominator is non-zero. Otherwise you want the code to pause. Consider the example below. In the first example the code will continue to execute normally, in the second example however the macro will immediately pause at the assertion as if a breakpoint was defined!

Debug.Assert 如何工作？假设您正在除以数字，并希望确保分母为非零。否则，您希望代码暂停。请考虑下面的示例。在第一个示例中，代码将继续正常执行，但是在第二个示例中，宏将立即在断言处暂停，就像定义了断点一样！

Sub mynzA()

x = 100

y = 10

Debug.Assert y <> 0 'Condition met: Continue!

x = 120

y = 0

Debug.Assert y <> 0 'Condition false!: Pause!

End Sub

运行结果：

4 STEPPING THROUGH CODE

单步执行代码

The key to debugging is to skillfully step through your code either by line or an entire function/procedure. Here are the basic commands found in the menu toolbar:Debug

调试的关键是熟练地通过行或整个函数/过程单步执行代码。以下是菜单工具栏中的基本命令：Debug

Step Into F8 – step into each procedure/function

单步F8 – 单步执行每个过程/函数

Step Over SHIFT+F8 – step over every procedure/function (run just the current procedure)

单步SHIFT+F8 – 单步执行每个过程/函数（仅运行当前过程）

Step Out CTRL+SHIFT+F8 – step out of the current running procedure

单步执行 CTRL+SHIFT+F8 – 单步执行当前正在运行的过程

Run to Cursor CTRL+F8 – execute and break at the line pointed by the cursor

运行到光标 CTRL+F8 – 在光标指向的行处执行并断开

Usually this is enough although you might want to get familiar with the other commands in the menu toolbar.Debug

通常，这就足够了，尽管您可能希望熟悉菜单工具栏中的其他命令。Debug

5 THE IMMEDIATE WINDOW AND DEBUG.PRINT

“即时”窗口和“调试.打印”

In the bottom left corner of VBA editor you should find the Immediate window. This panel can be used to execute immediately pieces of code (even your code is paused). Simply start typing and hit ! Additionally the Immediate window is the default output of the Debug.Print VBA command which prints a certain provided string (similarly like the MsgBox but does not display any pop-up). The Debug.Print command is very convenient for outputting VBA execution messages statuses or execution progress (e.g. number of processed items). ENTER

在VBA编辑器的左下角，您应该找到“即时”窗口。此面板可用于立即执行代码段（即使您的代码已暂停）。只需开始打字并点击！此外，“即时”窗口是 Debug.Print VBA 命令的默认输出，该命令打印某个提供的字符串（类似于 MsgBox，但不显示任何弹出窗口）。Debug.Print 命令对于输出 VBA 执行消息/状态或执行进度（例如，已处理项的数量）非常方便。ENTER

Sub mynzB()

Debug.Print "Hello there!"

End Sub

The output:

输出：

6 SUMMARY

总结

Debugging is an easy skill to learn. Knowing how to skillfully debug VBA code with benefit your coding experience and efficiency! Let me know what you think!

调试是一项易于学习的技能。了解如何熟练调试VBA代码，并有益于您的编码经验和效率！

【分享成果，随喜正能量】