Application caller with typename range

YasserKhalil
PlatinumLounger
Posts: 4270
Joined: 31 Aug 2016, 09:02

Application caller with typename range

Post by YasserKhalil »

Hello everyone
I have read about Application Caller at this link
https://docs.microsoft.com/en-us/office ... ion.caller
And as for such a code

Code: Select all

Dim v
Select Case TypeName(Application.Caller)
 Case "Range"
 v = Application.Caller.Address
 Case "String"
 v = Application.Caller
 Case "Error"
 v = "Error"
 Case Else
 v = "unknown"
End Select
MsgBox "caller = " & v
I can draw a shape or a button and assign this macro to it.
But I wonder how can Application.Caller returns a Range
Can you give me an example of that.

User avatar
HansV
Administrator
Posts: 69429
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Application caller with typename range

Post by HansV »

In a UDF: when you use a VBA function in a cell formula, Application.Caller will be the cell (as a Range object) that contains the formula.

For example:

Code: Select all

Function FillColor() As Long
    FillColor = Application.Caller.Interior.Color
End Function
The formula =FillColor() returns the fill color value of the cell with the formula.

S3566.png
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

YasserKhalil
PlatinumLounger
Posts: 4270
Joined: 31 Aug 2016, 09:02

Re: Application caller with typename range

Post by YasserKhalil »

Amazing Mr. Hans
Thanks a lot.

YasserKhalil
PlatinumLounger
Posts: 4270
Joined: 31 Aug 2016, 09:02

Re: Application caller with typename range

Post by YasserKhalil »

Is not it possible to deal with Application.Caller (Range Type) with normal procedures .. Is it only for the UDFs?
And what if I need to locate the button row and column .. through that property?

User avatar
HansV
Administrator
Posts: 69429
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Application caller with typename range

Post by HansV »

When the macro assigned to a command button from Form Controls is executed, Application.Caller is the name of the command button.
Example:

Code: Select all

Sub ButtonClick()
    Dim btn As Button
    Dim rng As Range
    Dim lngRow As Long
    Dim lngCol As Long
    Set btn = ActiveSheet.Buttons(Application.Caller)
    Set rng = btn.TopLeftCell
    lngRow = rng.Row
    lngCol = rng.Column
    MsgBox "Top left corner of button is in row " & lngRow & " and column " & lngCol
End Sub
Remark: in the On Click event procedure of an ActiveX command button, Application.Caller returns an error.
Regards,
Hans

YasserKhalil
PlatinumLounger
Posts: 4270
Joined: 31 Aug 2016, 09:02

Re: Application caller with typename range

Post by YasserKhalil »

Thanks a lot. Now it is clear for me.
Best Regards