您能否帮助我更多地了解有关VBA错误处理的良好做法？ [英] Can you help me understand more about good practise with VBA Error handling please?
问题描述
我已经花了最后两天的时间来尝试更好地了解VBA错误处理，但实际操作仍存在问题。下面的文字描述了我已经实现并嵌入到描述中的一些问题。我真的很想别人帮助我增进理解，并给我一些指导。我掌握了通常的基本知识，并试图将更多的精力放在微妙的功能上。
I've spent the last two days trying to better understand VBA error handling and still have issues with what is actually going on. The text below describes some of the realisations I've made and embedded in amongst the description are some questions. I'm really after people to help me refine my understanding and give me some pointers. I've got a hang of the usual basics and I'm trying to focus more on the subtle features.
我期待您的任何回答！
重要的是要意识到在VBA中发生错误时会发生两种截然不同的事情。
It is important to realise there are two distinct things that happen when an error occurs in VBA.
-
该错误对象被实例化并设置了属性（即err.number，err.desciption，err.source等）
The error object is instanciated and has it's properties set (ie err.number, err.desciption, err.source etc)
The
下一条要执行的行是由最后执行的出错时转到语句（如有）确定的。
The next line to be executed changes.
Which line is executed next is determined by the last "On Error Goto" statement that was executed - if any.
这些是相互独立但相关性很高的主题，实际上，您将编写不同但相互交织的代码来管理这两个主题。
These are separate but highly related topics and you will write, what is in effect, distinct but interwoven code to manage them both.
当发生任何错误或您使用Err.Raise时，总是设置Err对象。即使使用了 On Error Resume next或任何其他On Error语句。
When ANY error occurs or you use Err.Raise the Err object is ALWAYS set up. Even if "On Error Resume next" or any other On error statement has been used.
所以总是可以使用这样的代码：
So code like this could ALWAYS be used:
Dim i as integer
On error resume next
i = 100/0 ' raises error
if err.number <> 0 then
' respond to the error
end if
对错误
结尾进行响应，这确实很重要意识到当错误对象的err.number值非零时，会引发异常，并且如果您随后尝试执行任何 On Error Goto语句，这样做本身将引发错误，并且执行将传递给任何对象。调用当前过程的代码。 （或者在程序未通过任何代码调用的情况下，给出了通常的VBA错误对话框）。因此，在此示例情况下，出现错误时转到ALabel1不会将下一行更改为带有Label1的行。
It is really important to realise that when the error object has a non zero value for err.number an exception has been raised AND that if you then try and execute any "On Error Goto " statement doing so will itself raise an error and execution will be passed to any code that called the current procedure. (Or where the procedure not called by any code the usual VBA error dialogue is given). So in this example scenario below "On Error Goto ALabel1" would NOT change the next line to be the line with Label1: on it.
例如
Sub ErrorTest()
Dim dblValue As Double
On Error GoTo ErrHandler1
dblValue = 1 / 0
ErrHandler1:
debug.print "Exception Caught"
debug.print Err.Number
On Error GoTo ALabel1
dblValue = 1 / 0 '' THIS LINE ACTUALLY WILL RAISE AN unhandled ERROR!
Exit sub
ALabel1:
debug.print "Again caught it."
End Sub
一旦err.number属性设置为非零，您可以使用
Once the err.number property is set to non zero, you can reset it to zero by using
On Error Goto -1
请注意，Err.Clear还将其重置为零，但实际上等同于：
Note that Err.Clear also resets it to zero but it is actually equivalent to:
On Error Goto -1
On Error Goto 0
即Err.Clear删除当前已存在的 On Error Goto。因此，因此最好使用：
ie Err.Clear removes an "On Error Goto" that is currently in place. So therefore it is mostly best to use:
On Error Goto -1
就像使用Err.clear一样，您经常需要添加一条额外的行来恢复已安装的错误处理程序，或者反之。 >
as using Err.clear you would often need to add an extra line to re-instate the error handler that was in place or another one anyway.
Err.Clear
On Error Goto MyErrorHandlerLabel
或者，您可以将错误号设置为零（Err.Number = 0），但由于它不会清除description属性，因此其效果不如Clear方法。 （从MSDN页面开始）
"Alternatively, you can set the error number to zero (Err.Number = 0), but is not as effective as the Clear method since it does not clear the description property." (From MSDN pages)
我阅读了我认为的MSDN的下一段，并且不理解这种情况，就好像实例化了错误对象（即err .number<> 0），则在执行End Sub时将其值传递给任何调用过程。
I read the next paragraph on MSDN I think and don't understand how it can be the case as if the error object is instantiated (ie err.number <> 0) , it's values are passed to any calling procedure when End Sub is executed.
第一季度：帮助！
值得注意的是，Err.Clear在执行任何类型的Resume语句，Exit Sub，Exit Function，Exit Property或其他任何类型的VBA时都会隐式执行错误提示。 （来自MSDN页面）
"It is worth noting that Err.Clear is implicitly carried out by VBA whenever it executes any type of Resume statement, Exit Sub, Exit Function, Exit Property, or any On Error statement." (From MSDN pages)
您还可以将错误对象设置为您喜欢的任何数字
You can also set the error object it to whatever number you like using
Err Raise Number：=，Source：=，Description：=
Err.Raise Number:=, Source:=, Description:=
Err.Raise非常重要，因为它允许您将错误传播到调用程序，并且完全不方式向上到与用户打交道的顶级程序。您还可以提出自己的错误编号，称为用户定义的错误。这提供了一种告诉调用程序由于某种原因它无法继续运行的方法（即，发生意外错误或业务规则被破坏）。
Err.Raise is very important as it allows you to propagate an error to the calling program and unlimately all the way "up" to the top level program that is dealing with the user. You can also raise your own error numbers known as "user defined errors". This provide a means of telling the calling program that it could not continue for some reason (ie an unexpected error occurred or a business rule was broken).
通过这种方式，调用过程可以决定如何处理错误，而不是错误发生的过程（对用户身份一无所知）
In this way the calling procedure get to decide what to do about the error, not the procedure where the error happened (which doesn't understand anything about what the user is in the middle of doing, where as the very top most calling program does!)
第二季度：我对此是否正确？
第三季度：为此引发用户定义的异常？
Q2: Am I right about this?
Q3: Would you raise user defined exceptions for this purpose?
您可以使用以下语句来控制接下来执行哪一行代码
You can control which line of code is executed next using statements like
On Error Goto ALabelName
On Error Goto ANonZeroLineNumber
和
On Error Goto 0
On Error Goto 0是一个特例，因为它实际上表示在当前范围内（通常是子或函数），如果发生错误，则将错误对象传递回调用当前代码的代码子函数或子函数，不要在当前子函数或子函数中执行任何更多的代码。
On Error Goto 0 is a special case as it in effect says "within the current scope (typically a sub or function), in the event that an error happens pass the error object back to the code that called the current sub or function, don't execute any more code in the current sub or function.
在VBA中仅使用错误处理是很简单的，但是经常会发现自己在束缚自己
Using simply error handling in VBA is pretty straight forwards, but often find myself tying myself in knots when things get trickier.
不具备SQL Server和VBA常用的TRY CATCH语句是可耻的，所以我现在已经成功地进行了模拟此处：
Not having the commonly used TRY CATCH statement that SQL Server and VBA have is a shame, so I've successfully managed to simulate this now here:
我希望上面的帖子可以让您对我正在进行的实验有更多的了解
I hope the above post will give you some more insight into the experiments I've been doing if you're interested, and help you understand why I'm struggling.
简而言之，尽管那里有一些有用的网站，例如：
In short although there are some useful sites out there Like:
http://www.cpearson.com/excel/errorhandling.htm
但是，我觉得我可以提供更多指导。
but, I feel I could do with some more guidance.
Harvey
推荐答案
您询问有关良好实践的知识，这是我诚实的 answer：
You ask about good practise, here is my honest answer:
真正的好习惯是使用尽可能少的VBA。
如果您打算创建更大的项目t，然后建议在C＃中创建模块，然后在C＃中调用Office函数。
If you are planning to create a somewhat bigger project, then I recommend to create a module in C#, and call the office functions in C#.
然后将您的代码用作加载项或函数库。换句话说：仅使用VBA来调用模块的主要输入功能。但是要实现C＃的主要功能。
And then use your code as an add-in or a function library. In other words: Use VBA only to call the main entry functions of your module. But implement the main functionality in C#.
我们生活在2015年。借助C＃和Visual Studio，您现在手头上拥有了强大的强大工具。但是VBA是在90年代引入的。语言和IDE受限制。对于许多事情，例如正确的错误处理，您需要可怕的解决方法。
We are living in 2015. With C# and Visual Studio, you now have overwhelming powerful tools at hand. But VBA was introduced in the 90's. The language and the IDE are limited. For many things, like proper error handling, you need awful workarounds.
BTW，VBA语言在其诞生之时就已经过时了。在90年代，我们已经有了更好的面向对象的概念。遗憾的是，微软从未为其办公产品创建更好的语言。他们为了向后兼容而牺牲了更好的语言，并使他们的用户最容易过渡。恕我直言，这是一个错误的决定。因此，今天我们生活在全球数百万个以有限而简陋的语言编写的项目中。
BTW, the VBA language was even already outdated at the time of its birth. In the 90s, we had much better object-oriented concepts already. It is a pity that Microsoft did never create a better language for their office products. They sacrificed a better language for the sake of being "backward compatible" and making the transition for their users most easy. IMHO that was a bad decision. Therefore, today we live with millions of projects written worldwide in a limited and crappy language.
这篇关于您能否帮助我更多地了解有关VBA错误处理的良好做法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
