How to get worksheet and cell that the current VBA function is returning to?

excel, vba

Solution

Is this what you are trying?

Option Explicit

Public Function MyCustomFunc()
    '~~> Judicious use of 'Volatile' is advised.
    '~~> This will be called everytime the sheet is recalculated
    Application.Volatile

    MsgBox Application.Caller.Parent.Name & ", " & Application.Caller.Address
End Function

Problem

Is there a variable anywhere that gives the worksheet and cell that will recieve the result of a custom VBA function? For example, if in `A!B1` the formula is `=MyCustomFunc()` in my code: ``` public function MyCustomFunc() 'what can I call here to get the values "A" and "B1"? end function ```

Original source