Function determining what cell it is being called from?

[PJStein12]

I'm writing some functions in VBA that determine return values based on the coordinates of the cell they are being called from. They currently accept the cells row and column as arguments, but actually using them in the sheet I am working on will be unfeasible--it has thousands of cells with unique coordinates. Is there anyway to determine the calling cell within a function? I've tried using the ActiveCell object, but it simply gives the cell currently highlighted, which is not what I need. Any help is greatly appreciated.

 

[Joe4]

The ActiveCell IS the cell that is currently highlighted. Perhaps if you explain the function you are trying to write and give a concrete example, we can assist you.

 

[PJStein12]

As an example, say the function returns a value that is the sum of the numerical row and column values. For example, when called from cell B3 (3,2), this hypothetical function would return a value of 5. The problem is, this function would be used in a number of cells on a worksheet. If the cell B3 were highlighted when the page was refreshed, this function would return 5 to every cell calling it. Thus, I am wondering if there is a method of determining the cell a function is called from, rather than just the active cell at the time the function is called.

 

[Richie(UK)]

Hi PJ,

Is this the sort of thing that you were thinking of:

Function AddRowCol() As Long
    Dim lRow As Long
    Dim iCol As Integer
    lRow = Application.Caller.Row
    iCol = Application.Caller.Column
    AddRowCol = lRow + iCol
End Function

In cell B3 enter =addrowcol()

HTH

 

[PJStein12]

I can't test it out right now, but that looks to be exactly what I was looking for. Thanks very much.

 

[Joe4]

I tested it out and it works great!

I was racking my brain trying out how to access the cell the formula is located in. I guess "Caller" is they key.

Richie, what exactly does "Caller" do/mean?

 

[NateO]

See the VBE help file for information on the caller property. Excerpt:

Returns information about how Visual Basic was called (for more information, see the Remarks section).

Also, these variables are chewing up memory, yes-no? I wonder if it makes sense to streamline this:

<font face=Courier New><SPAN style="color:darkblue">Function</SPAN> AddRowCol() <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>
AddRowCol = Application.Caller.Row + Application.Caller.Column
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Function</SPAN></FONT>

If you were using them over and over again, grabbing the variable should probably outperform calling the property, but on a single call, there's not much gettting around this...

 

[Richie(UK)]

Nate,

You are absolutely right. I coded it like that as I thought it helped to illustrate what was happening. It is, however, not particularly efficient!

Something like you suggested, or the code below, would be preferable in terms of efficiency.

Function AddRowCol() As Long
With Application.Caller
    AddRowCol = .Row + .Column
End With
End Function

 

[MorituriMax]

Nate,

You are absolutely right. I coded it like that as I thought it helped to illustrate what was happening. It is, however, not particularly efficient!

Something like you suggested, or the code below, would be preferable in terms of efficiency.

Function AddRowCol() As Long
With Application.Caller
    AddRowCol = .Row + .Column
End With
End Function

Hi! Found this and thought it might be useful... is the intent of the code to allow the user to use the address for calculations in the function.. it's really confusing me trying to see how you can do anything with the values passed back to the cell, as its not really row/col, but the summed values. Just checking.

Thanks

 

[Rick Rothstein]

Hi! Found this and thought it might be useful... is the intent of the code to allow the user to use the address for calculations in the function.. it's really confusing me trying to see how you can do anything with the values passed back to the cell, as its not really row/col, but the summed values. Just checking.

You are asking a question in a thread whose last post (before yours) was over 14 years ago. I know that one of the main posters in that thread unfortunately died some 6 years ago and the other main poster's last post was about 6 years ago to comment on the other's passing and whose post prior to that one was some 12 years ago. So neither of them can or will be answering you. As to the practicality of the AddRowCol function... there (probably) isn't one. That function appears to have been made up solely to demonstrate the Application.Caller function and what it is able to do, which is namely to pass into the UDF (user defined function) it is placed in information about the cell containing the UDF in it is in. To some extent, it serves the same purpose to a UDF as Target does to sheet event code procedures like Change, SheetChange, BeforeDoubleClick, etc. (although Target can reference not only a single cell, but a range of cells as well whereas Application.Caller only references a single cell).