Cannot get Application Caller to work

[cosmarchy]

Hi,

I have this code which is called from a cell on a worksheet:

Public Function CountT(iRow As Integer)
    Debug.Print Application.Caller
End Function

I want to get the address and worksheet name of the cell which called the function. This could be in multiple cells across many different worksheets.

when I set a breakpoint and add Application.Caller to the watch window, I get 'Error 2023'

Does anyone have any ideas how I can get this to work? Do I need any references set?

Thanks

[Paul_Hossler]

Used in s a WS function, the 'Caller' is a 'Cell', a 'Cell' is a 'Range', and needs to be treated Range-y

Option Explicit


Public Function CountT(iRow As Integer) As Variant
    Dim rCaller As Range
    
    CountT = 1234 * iRow
    
    Set rCaller = Application.Caller
    
    MsgBox rCaller.Address & " -- " & rCaller.Parent.Name


End Function

[SamT]

"Caller" is a Function. You can't "Print" a Function

Dim CellAddress as String
Dim WkShtName as String

On Error Resume Next
   CellAddress = Application.Caller.Address
   WkShtName = Application.Caller.Parent.Name
If Err Then Debug.Print Err
Err = 0

[Paul_Hossler]

@SamT - ???


Capture.JPG

Option Explicit

Public Function CountT(iRow As Integer) As Variant
    Dim rCaller As Range
    
    CountT = iRow
    
    Set rCaller = Application.Caller
    
    MsgBox rCaller.Address & " -- " & rCaller.Parent.Name

End Function

[cosmarchy]

Ah, I see.

Many thanks guys. I now know what is going on...

[snb]

In A1:


"=F=snb()"

In a macromodule:

Function F_snb()
   MsgBox Application.Caller.Address & vbTab & Application.Caller.Parent.Name
End Function

or

Function F_snb()
  With Application.Caller
    MsgBox .Address & vbTab & .Parent.Name
  End With
End Function

NB. 'Public' is redundant
'Irow' is redundant
Any variable is redundant
Keep your code concise.

[Paul_Hossler]

NB. 'Public' is redundant
'Irow' is redundant
Any variable is redundant
Keep your code concise.

Typo - should be F<underscore>snb

"=F=snb()"

NB. 'Public' is redundant
'Irow' is redundant
Any variable is redundant
Keep your code concise.

I think it depends on what you want the function to do

Capture.JPG

Option Explicit
Option Private Module




Public Function CountT(iRow As Integer) As Variant
    Dim rCaller As Range
    
    Set rCaller = Application.Caller
    
    CountT = "The cell " & iRow & " below me is cell " & rCaller.Offset(iRow, 0).Address


End Function

[georgiboy]

Any variable is redundant.

For me the above is untrue, especially for people trying to learn VBA. It is a matter of opinion and sometimes (most of the time) opinions are better off being private.

[kalyl]

you can pass the calling cell's address and worksheet name as additional parameters to your function.
Here's an example:

Public Function CountT(iRow As Integer, callerAddress As String, callerSheet As String) As Integer 
Debug.Print "Caller Address: " & callerAddress 
Debug.Print "Caller Worksheet: " & callerSheet 
' Your function logic here 
' For example, return the row count based on the provided parameters CountT = iRow 
End Function