Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell References in functions
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell References in functions
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" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell References in functions
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 *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell References in functions
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) "Andrew Smith" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add Subtotal functions using cell references | Excel Discussion (Misc queries) | |||
Moving worksheet references in functions | Excel Discussion (Misc queries) | |||
convert relative cell references to absolute cell references via amacro? | Excel Discussion (Misc queries) | |||
External References in Functions | Excel Worksheet Functions | |||
How to use complex no. functions that have cell references? | Excel Worksheet Functions |