Cell References in functions

[andrew]

Hi,
I am trying to create some time series functions in excel. I want
these functions to take advantage of the columns and rows in excel.
For example, the most basic function is the difference function, which
is just the difference between one value and the one above it. So for
example, I want to have the function d(a4) which would be equal to the
excel formula "=a4-a3". Is there any way that I can write a vba
function that will tell me what location the formula is referencing.
For instance, say I put the formula "=d(a4)" into cell g1 and the value
in cell a4 is 12. Is there any way for me to figure out that the
location of the cell being referenced is a4 instead of/in addition to
learning that there is a 12 in the cell being pointed to?

Thanks,
Andrew

 

[Bob Phillips]

Hi Andrew,

Like this


Function d(rng As Range)
If rng.Cells.Count > 1 Then
d = CVErr(xlErrRef)
Else
MsgBox rng.Address
d = rng.Value - rng.Offset(-1, 0).Value
End If
End Function




--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Andrew Smith]

Thanks, this is great! Suppose I wanted to add the possibility of
nested functions within the timeseries. For instance, d(a12^5). Do you
know of a command like isrange that would allow me to figure out that
the input wasn't a range and go through a separate processing loop. I
notice that if I dim rng as Variant it will still work ok for the range
way.

Thanks,
Andrew

 

[Bob Phillips]

Andrew,

If we pass the operator as another parameter, it is quite easy, such as

=d(A12,"^5")

The UDF is then

Function d(rng, op As String)
If rng.Cells.Count > 1 Then
d = CVErr(xlErrRef)
Else
MsgBox rng.Address
d = Evaluate("(" & rng.Value & op & ")-(" & rng.Offset(-1, 0).Value
& op & ")")
End If
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)