问题描述
最终,我想将单元格移动到最后一个错误发生的位置.忘了说我使用的是 Excel 2003.
Eventually, I want to move the cell to the location where the last error occured. Forgot to say that I'm using Excel 2003.
推荐答案
根据评论中的要求...
As requested in comments...
在 Excel VBA 帮助中查找Application"对象的Caller"属性.当您从 VBA 例程中使用它时,它会告诉您对例程的调用来自何处 - 范围、图表等.
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.
在使用Application.Caller"时要注意的一件重要事情是它并不总是一个 Range 对象.查看帮助,但该属性返回一个 Variant 值,可以是 Range、String 或 Error.(在您感兴趣的情况下,它是一个 Range 对象,但您需要注意这一点.)
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.)
由于上述原因,以及 VBA 语法在对象与值方面的变幻莫测,使用Application.Caller"可能会很棘手.像这样放置一行:
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:
Debug.Print Application.Caller.Address
当调用者不是 Range 时,您的代码将失败.做类似的事情:
in your code will fail when the caller isn't a Range. Doing something like:
Dim v
v = Application.Caller
会编译",但是当调用者是一个 Range 时会创建循环引用,因为您试图访问调用 Range 的值.
will "compile", but will create circular references when the caller is a Range because you're trying to access the value of the calling 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.
还有一件事 - 显然这只能在实际调用 VBA 例程后才能告诉您调用者.如果您的调用公式中有错误,Excel 将向您的单元格返回错误值,而不会调用您的 VBA 例程.
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:当工作簿/工作表中发生最后一个错误时,如何通过 VBA 代码获取最后使用的单元格?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
更多推荐
[db:关键词]
发布评论