VBA:当工作簿/工作表中发生最后一个错误时，如何通过 VBA 代码获取最后使用的单元格?

本文介绍了VBA:当工作簿/工作表中发生最后一个错误时，如何通过 VBA 代码获取最后使用的单元格?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

最终，我想将单元格移动到最后一个错误发生的位置.忘了说我使用的是 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屋！