CODEWITHSUNDEEP
excelvba
Jake
Jake

Reputation: 11430

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

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

Upvotes: 8

Views: 8576

Answers (3)

Marten Jacobs
Marten Jacobs

Reputation: 41

You want Application.ThisCell.

This returns the cell which is currently being calculated.

Upvotes: 1

tumchaaditya
tumchaaditya

Reputation: 1297

ActiveSheet.Name will give you sheet name. ActiveCell.Row will give you row number and ActiveCell.Column will give you column letter. Then you can combine them to get cell address.

Upvotes: -1

Siddharth Rout
Siddharth Rout

Reputation: 149287

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

Upvotes: 8

Related Questions