custom functions depending on location 1

[RobBroekhuis]

Hi,
I'm not sure if what I'm trying to accomplish is possible, but it's worth a try. I'm trying to make a table full of calculations more legible, by assigning names to variables used in the calculations. This would be easy enough if I had just one set of calculations. However, each calculation is repeated multiple times: each column has a different set of variables on which the same calculations need to be performed - so a named range won't work. So I thought I'd write custom functions for each of the variables I reference. The way I have it now, an example is:

Function TT(i As Integer)
  TT = Range("temperatures").Offset(, i - 1).Value
End Function

This way, I can use TT(1) to refer to the first temperature in my table, etc. Problem is, I still need to use the index, and because I want to be able to copy the calculations to the entire range of columns, this means I need to set up a separate row (say row 1) with values 1,2,3,4,... . Now if I have a calculation in cell E30 that needs to refer to temperature, I can use an expression like =TT(E1)+20. Unfortunately, this is still not particularly legible or user-friendly.
What I'd really like to be able to do is use an expression like =TT()+20, where the function itself would figure out which temperature value to use. Is there any way a user-defined function can figure out from which cell it is being called?
I know, a long-winded way to ask a simple question, but I figured I'd provide some context...
Thanks
Rob

 

[ilses]

Hi Rob,

"Is there any way a user-defined function can figure out from which cell it is being called?"

Function TT()
TT = Application.Caller.Column 'returns column number
'Application.Caller.Row 'returns row number
'Application.Caller.Address 'returns cell address
End Function

HTH

Ingrid

 

[RobBroekhuis]

Excellent! That works great. Now my next problem is that Excel is obviously unaware of the antecedent/precedent relationships between calculated cells when I start fiddling with these kinds of functions. The functions return the correct value when first used, but their value does not update when the underlying data changes. Even a manual recalculate (F9) does not do the trick. How can I trigger a full recalculation?
Rob

 

[ilses]

Courtesy of

http://www.decisionmodels.com:

You can request a full calculation (all formulae) by pressing Ctrl-Alt-F9, or a recalculation (all new/changed/volatile formulae, cells and their dependents) by pressing F9.

EXCELlent site, definitely worth a visit.

Best regards/Groetjes,

Ingrid