Cannot get Application Caller to work

Hi,

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

Code:

---

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

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

Code:

---

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

---

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

Code:

---

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

---

@SamT - ???


Attachment 30433

Code:

---

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

---

Ah, I see.

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

In A1:


"=F=snb()"

In a macromodule:

Code:

---

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

---

or

Code:

---

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.

Quote:

---

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

---

Typo - should be F<underscore>snb

"=F=snb()"

Quote:

---

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

Attachment 30434

Code:

---

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

---

Quote:

---

Originally Posted by snb

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.

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

Code:

---

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

---