VBA：当工作簿/工作表中发生最后一个错误时，如何通过 VBA 代码获取最后使用的单元格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3861431/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA: How to get the last used cell by VBA code when the last error occured in a Workbook/Worksheet?
提问by Vantomex
Eventually, I want to move the cell to the location where the last error occured. Edit:Forgot to say that I'm using Excel 2003.
最终，我想将单元格移动到最后一个错误发生的位置。编辑：忘了说我使用的是 Excel 2003。
回答by jtolle
As requested in comments...
根据评论中的要求...
Look up the 'Caller' property of the 'Application' object in the Excel VBA help. When you use it from a VBA routine, it will tell you where the call to the routine came from - what Range, Chart, etc.
在 Excel VBA 帮助中查找“Application”对象的“Caller”属性。当您从 VBA 例程中使用它时，它会告诉您对例程的调用来自何处 - 范围、图表等。
An important thing to be aware of when using 'Application.Caller' is that it isn't always a Range object. Look at the help, but the property returns a Variant value that can be a Range, String, or Error. (It is a Range object in the case you're interested in, but you'll need to be aware of this.)
使用“Application.Caller”时要注意的一件重要事情是它并不总是一个 Range 对象。查看帮助，但该属性返回一个 Variant 值，该值可以是 Range、String 或 Error。（在您感兴趣的情况下，它是一个 Range 对象，但您需要注意这一点。）
Because of the above, and the vagaries of VBA syntax when it comes to objects vs. values, it can be tricky to use 'Application.Caller'. Putting a line like:
由于上述原因，以及 VBA 语法在对象与值方面的变幻莫测，使用“Application.Caller”可能会很棘手。放一行，如：
Debug.Print Application.Caller.Address
in your code will fail when the caller isn't a Range. Doing something like:
当调用者不是范围时，您的代码将失败。做类似的事情：
Dim v
v = Application.Caller
will "compile", but will create circular references when the caller isa Range because you're trying to access the value of the calling Range.
将“编译”，但当调用者是Range时会创建循环引用，因为您试图访问调用 Range 的值。
This all means that it's probably best to write a little utility function for yourself:
这一切都意味着最好为自己编写一个小实用程序函数：
Public Function currentCaller() As String
If TypeOf Application.Caller Is Range Then
Dim rng As Range
Set rng = Application.Caller
currentCaller = rng.Address(External:=True)
Else
currentCaller = CStr(Application.Caller)
End If
End Function
and then call it from your error handlers where you want to know where the call came from.
然后从您的错误处理程序中调用它，您想知道调用的来源。
One more thing - obviously this can only tell you the caller once a VBA routine has actually been called. If you have errors in your calling formulas, Excel will return error values to your cells without ever calling your VBA routines.
还有一件事 - 显然，这只能在实际调用 VBA 例程后才能告诉您调用者。如果您的调用公式中有错误，Excel 将向您的单元格返回错误值，而不会调用您的 VBA 例程。
回答by PowerUser
Wrap your VBA function in another function that stores the cell location and value as variants. Keep this 'wrapper' function as basic as possible so it won't cause any additional errors.
将您的 VBA 函数包装在另一个将单元格位置和值存储为变体的函数中。保持这个“包装器”功能尽可能基本，这样它就不会导致任何额外的错误。
If you're trying to debug app-crashing errors, the wrapper function could even store those values in a comma-delimited text file. Once stored, Excel can crash all it wants and you'll still know what the cell location and value were since you stored them outside of Excel beforehand.
如果您尝试调试应用程序崩溃错误，包装函数甚至可以将这些值存储在逗号分隔的文本文件中。一旦存储，Excel 可能会崩溃，而且您仍然会知道单元格位置和值是什么，因为您事先将它们存储在 Excel 之外。
回答by Waller
Could this be done with an error handler?
这可以用错误处理程序完成吗？
An example of what I mean below:
我在下面的意思的一个例子：
sub code1()
on error goto cell A1
end sub
