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 - ???


30433




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... :yes

[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

30434




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