Using a Custom Function - determining the row that called it

[Masa Ito]

Simple function:

Function AddVisible(iTotal)
' code to add cells value if the column is visible.
End Function

in the Total column, I call this function with:
=AddVisible(SUM(AV3B3))

I need to know which row 'called' the function, so that I can check for
the visible status of the column, then grab the value.

Also, very open to any other, simpler ways to accomplish this. I have a
function that looks at todays date (NOW) when opening the Worksheet, and
hides all the columns with old data, and columns with data that is more
than 12 months in the future. I now need to show the totals of this
'subset' of the data, but am really struggling learning how to do this
with VBA. Scoured the Help files, and my two Excel 'Bibles' - obviously
need an Excel Programming book...

Assistance in the meantime much appreciated!

Masa

 

[Tom Ogilvy]

You shouldn't have a Sum function within your call to AddVisible. The
addvisible function should do the summing.

Function AddVisible(iTotal)
Dim rng as Range
set rng = Application.Caller
lRW = rng.row

.. . .
End Function

 

[Masa Ito]

Thanks for the reply Tom.

The reason I had the Sum function within the call was so that it would
automatically update when one of the values changed within the range
being added. Is there a proper way to accomplish this?

Also, I searched and could not find what lRW is referring to. I am sure
it will be something stupid that I should be able to think of, but if you
don't mind explaining...

Thanks.
M.I.

 

[Tom Ogilvy]

lRW was just a Variable of type long to hold the value of the row in which
the function resides - since that was your question.

the sum will pass in the value of all the cells in the specified range,
without consideration of whether they are visible or not.

When you use your addvisible function with the range of cells to consider,
excel will use changes in those cells to determine when to run you
addvisible function to recalculate the sum. What won't trigger it is
unhiding a column, but there isn't a work around for that since that doesn't
trigger a calculate.