How to get a worksheet and a cell that the current VBA function returns to?

Question

Is there a variable anywhere that gives a worksheet and a cell that gets the result of a custom VBA function?

For example, if in A!B1 formula =MyCustomFunc() in my code:

 public function MyCustomFunc() 'what can I call here to get the values "A" and "B1"? end function 

Answer 1

Is that 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 

Answer 2

ActiveSheet.Name will give you the sheet name. ActiveCell.Row will give you the line number, and ActiveCell.Column will give you the letter designation. Then you can combine them to get the address of the cell.

Answer 3

You want Application.ThisCell .

This returns the cell that is currently being calculated.