Posts Tagged ‘Application.Caller’

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