Consulting

Results 1 to 9 of 9

Thread: Cannot get Application Caller to work

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Cannot get Application Caller to work

    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

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,747
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,709
    Location
    "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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,747
    Location
    @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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Ah, I see.

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

  6. #6
    snb
    Guest
    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.

  7. #7
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,246
    Location
    Quote Originally Posted by snb View Post
    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.
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2405, Build 17628.20102

  8. #8
    Banned VBAX Newbie
    Joined
    Nov 2023
    Posts
    2
    Location
    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
    Last edited by Aussiebear; 11-28-2023 at 12:53 AM. Reason: Removed yet another spam link in the post

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,747
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •