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
如何调试
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!
编写
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.
调试本质上是定位和修复错误(或某些人喜欢称之为缺陷)
1 DEBUG VBA: BASIC TERMS
调试
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:
让我们从
The buttons allow you to do the following:
这些按钮允许您执行以下操作:
Run – run your macro (Sub) or UserForm. This is equivalent to the key shortcut F5
运行
Break – pause a running macro +. You can also stop a running macro by hitting the buttonCTRLBreakEsc
中断
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.
断点指定调试
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.
要添加
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.
通常在可能发生错误的位置指定断点。当您正在运行循环并且不确定错误何时会发生时,或者如果您意识到导致错误但无法在正确的时刻捕获它的情况,这可能会很麻烦。这是您需要使用
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
调试的关键是熟练地通过行或整个函数
Step Into F8 – step into each procedure/function
单步
Step Over SHIFT+F8 – step over every procedure/function (run just the current procedure)
单步
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
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,但不显示任何弹出窗口)。
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代码,并有益于您的编码经验和效率!
【分享成果,随喜正能量】
我20多年的VBA实践经验,全部浓缩在下面的各个教程中:
原文地址:https://baijiahao.baidu.com/s?id=1736607013545882092