scripting - VBA: How to get the last used cell by VBA code when the last error occured in a Workbook/Worksheet?

Question

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.

Answer 1

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.

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.)

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

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 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.

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.