What cell is currently calculating? (custom worksheet function needs to know)

[Guest]

I am writing various helper functions in a VBA module. These functions will be used inside formulas as custom worksheet functions. In more than one of them it would be great to know what the current cell is, that is the cell that is currently calculating and thus calling this function. You might also think of it as the caller of the function. Ideally I'd like a range object that refers to the cell, but a cell address would be acceptable

I can't find any documentation or MSDN info or anything anywhere, so I'm posting here

Yes, I can work around this by passing the cell address into the function, but even that's awkward because the only way to do that is with ADDRESS(ROW(), COLUMN()) as a parameter to the function every time you call it. Kind of a drag, but certainly doable

An example of how this would be useful

I have a lot of workbooks where there are multiple detail sheets and a Total sheet that is formatted identically to the detail sheets (which are all formatted the same). I end up sticking a formula in every data cell in the total sheet that sums that same cell on every detail sheet. For example, in Total!D5 I would have the formula

SUM(Detail_1!D5, Detail_2!D5, Detail_N!D5
o
Detail_1!D5 + Detail_2!D5 + Detail_N!D

I'm sure many of you have done this many times. Wouldn't it be great to just be able to say

SUMSHEETS("Detail_1", "Detail_2", "Detail_N"

or something like that. Or better yet just have a custom one for this workbook where the VBA procedure knows which sheets to sum and the formula would look like

SUMSHEETS(

If I can't get an answer to this I end up writing somthing that takes the cell address as a parameter, and it will look like

SUMSHEETS(ADDRESS(ROW(), COLUMN())

Another use for this is getting the current sheet name in a formula. Currently I have a function called SheetName() which takes a range as a parameter. So you pass in any random range on the sheet and it returns Range.Parent.Name. But mostly I use it on the sheet itself, so it would be nice to just have a SheetName() function with no parameters. ...or to have a built-in Excel function that would provide the sheet name

Thanks for any responses

Jame

 

[Anders S]

Hi James

You might also think of it as the caller of the function.

I think you are close, try

 

[Harlan Grove]

I am writing various helper functions in a VBA module. These functions will
be used inside formulas as custom worksheet functions. In more than one of
them it would be great to know what the current cell is, that is the cell that
is currently calculating and thus calling this function. . . .

...

Application.Caller

 

[Guest]

Hey thanks guys for the prompt responses. It works. That's killer. Why was that so hard to find in the docs, dammit

Thanks again

James