Get Cell Address Where the UDF is Called From: Application.Caller

Posted: May 3, 2013 by Transformer in Excel, VBA
Tags: , , , ,

When a function is created in Excel VBA then it can be called from a sheet as well as from another procedure in the code.If one wants to check from where the function has been called then one can use the Application.Caller.

Its behavior depends on the caller. If it is called from:

  1. Range/Cell then it returns Range
  2. Shape or some control then it returns String(Name of the shape/Control)
  3. Some procedure or function then it returns Error.

The following function can be used to check how it works.

Function GetCaller()

    Dim strCallerTyp        As String

    strCallerTyp = TypeName(Application.Caller)
    Select Case strCallerTyp
        Case "Range"
            MsgBox "Called From a Range and the address is: " & Application.Caller.Address
        Case "String"
            MsgBox "Called from a control/Shape and the name is: " & Application.Caller
        Case "Error"
            MsgBox "Error:Not called from the sheet."
    End Select

End Function
Advertisements
Comments
  1. MaxFraudy says:

    Thanks for sharing this. This information is very difficult to find.

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s