Results 1 to 5 of 5
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Custom Functions - Determining the 'Calling Cell' (Excel97)

    Is there a way to determine, from a function, attributes of the cell CONTAINING the function?

    For example, if I have created a function called UDF.
    In cell D1 I have:
    =UDF(A1:A10)

    Is there ANY way for the function to know what the calling cell (D1) is? I can pass the range (A1:A10) to the function, I can get the active cell, selection, etc, but how do you get information on the cell CALLING the function? This calling cell changes in the spreadsheet: for example: in G2 you might have:
    =UDF(B2:B5)

    so when the UDF in G2 is run it must get G2.

    I imagine it is one of those things that is a simple command or a nearly impossible procedure.
    Thanks,
    Steve

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Functions - Determining the 'Calling Cell' (Excel97)

    ----------------------------------------------------------------------------
    I imagine it is one of those things that is a simple command or a nearly impossible procedure.
    ---------------------------------------------------------------------------

    My guess is that it is probably the latter. Although Excel VB Help mentions that you should follow variable naming conventions when naming functions, I could not find any way to reference the function name or any properties of a function.

    Only reference you might could get would be from the referenced cell in the function and that would not be dynamic, i.e., cell.row or .column or .offset or some such.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Functions - Determining the 'Calling Cell' (Excel97)

    Try:

    <pre>Public Function UDF(oRng As Range) As Integer
    MsgBox "I was called from " & Application.Caller.Address
    End Function
    </pre>

    Legare Coleman

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Functions - Determining the 'Calling Cell' (Excel97)

    Cool, we both learned something with Legare's post...

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Custom Functions - Determining the 'Calling Cell' (Excel97)

    Legare,
    Thank you, thank you.
    This was just what I was looking for!

    Steve

Posting Permissions

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