Below I have a function calculates the difference of 2 values based on a cell reference and an interval
For example: =ROC(A10,3) calculates the difference between A10 - A8 (which is what I'm after).....

Function ROC(val1 As Range, per As Integer

Set val2 = Cells(Range(val1.Address).Row - per + 1, Range(val1.Address).Column
If Range(val1.Address).Row - per < 1 The
R0C = "
Els

ROC = (val1 - val2
End I

End Functio

There are 2 problems with the above code

1) When the formula is first entered it calculates correctly, however when the 2nd parameter is referred to by reference the formula does not recalculate correctly when the value in this cell is changed. It infact shows the value of val1....I have checked the options and calculation is set to automatic and iterations are on and to 100..

2) It seems quite slow to run. Can it be optimised in anyway

Any help would be appreciate

Ma

Function ROC(val1 As Range, per As Integer) as Double
dim thisrow as long
on error goto trap
thisrow = val1.row - per




Set val2 = Cells(Range(val1.Address).Row - per + 1, Range
(val1.Address).Column)
If Range(val1.Address).Row - per < 1 Then
R0C = ""
Else

ROC = (val1 - val2)
End If
exit function
trap:msgboxerror

End Function

-----Original Message-----
Below I have a function calculates the difference of 2
values based on a cell reference and an interval.
For example: =ROC(A10,3) calculates the difference
between A10 - A8 (which is what I'm after)......


Function ROC(val1 As Range, per As Integer)

Set val2 = Cells(Range(val1.Address).Row - per + 1, Range
(val1.Address).Column)
If Range(val1.Address).Row - per < 1 Then
R0C = ""
Else

ROC = (val1 - val2)
End If

End Function


There are 2 problems with the above code.

1) When the formula is first entered it calculates
correctly, however when the 2nd parameter is referred to
by reference the formula does not recalculate correctly
when the value in this cell is changed. It infact shows
the value of val1....I have checked the options and
calculation is set to automatic and iterations are on and
to 100...

2) It seems quite slow to run. Can it be optimised in
anyway?

Any help would be appreciated

Max


.

Oops ... lost my mail ...try again:


Function ROC(val1 As Range, per As Integer) as double

Set val2 = Cells(Range(val1.Address).Row - per + 1,
Rangeval1.Address).Column)
f Range(val1.Address).Row - per < 1 Then
R0C = ""
Else

ROC = (val1 - val2)
End If

End Function



-----Original Message-----
Below I have a function calculates the difference of 2
values based on a cell reference and an interval.
For example: =ROC(A10,3) calculates the difference
between A10 - A8 (which is what I'm after)......


Function ROC(val1 As Range, per As Integer)

Set val2 = Cells(Range(val1.Address).Row - per + 1, Range
(val1.Address).Column)
If Range(val1.Address).Row - per < 1 Then
R0C = ""
Else

ROC = (val1 - val2)
End If

End Function


There are 2 problems with the above code.

1) When the formula is first entered it calculates
correctly, however when the 2nd parameter is referred to
by reference the formula does not recalculate correctly
when the value in this cell is changed. It infact shows
the value of val1....I have checked the options and
calculation is set to automatic and iterations are on and
to 100...

2) It seems quite slow to run. Can it be optimised in
anyway?

Any help would be appreciated

Max


.

phew 3rd time I tried this..bizarre behavious re my
server...sorry all.

Here, I hope is the new function that you want :)

Function ROC(val1 As Range, per As Integer) as double
Dim ThisRow as Long
On error goto Trap

ThisRow = val1.row - per

ROC = Cells(ThisRow,val1.column).Value

exit function
trap: msgbox error

End Function


Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
Below I have a function calculates the difference of 2
values based on a cell reference and an interval.
For example: =ROC(A10,3) calculates the difference
between A10 - A8 (which is what I'm after)......


Function ROC(val1 As Range, per As Integer)

Set val2 = Cells(Range(val1.Address).Row - per + 1, Range
(val1.Address).Column)
If Range(val1.Address).Row - per < 1 Then
R0C = ""
Else

ROC = (val1 - val2)
End If

End Function


There are 2 problems with the above code.

1) When the formula is first entered it calculates
correctly, however when the 2nd parameter is referred to
by reference the formula does not recalculate correctly
when the value in this cell is changed. It infact shows
the value of val1....I have checked the options and
calculation is set to automatic and iterations are on and
to 100...

2) It seems quite slow to run. Can it be optimised in
anyway?

Any help would be appreciated

Max


.

I would be interesting to know what makes you think it is slow. For me,
both mine and yours are pretty much instantaneouls and both update when I
change the per in a cell and pass it in as a cell reference (assume that is
what you are talking about. The procedure is simple so I am not sure what
optimzations you would do. Another approach:

Function ROC(val1 As Range, per As Variant)
' Application.Volatile
If val1.Row - per < 1 Then
ROC = ""
Else
ROC = val1 - val1.Offset(-1 * per + 1, 0).Value
End If
End Function

Yours causes an problem if the address resolves to less than row 1 because
you try to use it before you test for that condition.



--
Regards,
Tom Ogilvy


"Max" wrote in message
...
Below I have a function calculates the difference of 2 values based on a
cell reference and an interval.
For example: =ROC(A10,3) calculates the difference between A10 - A8 (which
is what I'm after)......


Function ROC(val1 As Range, per As Integer)

Set val2 = Cells(Range(val1.Address).Row - per + 1,
Range(val1.Address).Column)
If Range(val1.Address).Row - per < 1 Then
R0C = ""
Else

ROC = (val1 - val2)
End If

End Function


There are 2 problems with the above code.

1) When the formula is first entered it calculates correctly, however
when the 2nd parameter is referred to by reference the formula does not
recalculate correctly when the value in this cell is changed. It infact
shows the value of val1....I have checked the options and calculation is set
to automatic and iterations are on and to 100...

2) It seems quite slow to run. Can it be optimised in anyway?

Any help would be appreciated

Max

Hi Max
For the recalculation, Tom added "application.volatile"
Look in help
Regards,

JY

"Max" wrote in message
...
Below I have a function calculates the difference of 2 values based on a
cell reference and an interval.
For example: =ROC(A10,3) calculates the difference between A10 - A8 (which
is what I'm after)......


Function ROC(val1 As Range, per As Integer)

Set val2 = Cells(Range(val1.Address).Row - per + 1,
Range(val1.Address).Column)
If Range(val1.Address).Row - per < 1 Then
R0C = ""
Else

ROC = (val1 - val2)
End If

End Function


There are 2 problems with the above code.

1) When the formula is first entered it calculates correctly, however
when the 2nd parameter is referred to by reference the formula does not
recalculate correctly when the value in this cell is changed. It infact
shows the value of val1....I have checked the options and calculation is set
to automatic and iterations are on and to 100...

2) It seems quite slow to run. Can it be optimised in anyway?

Any help would be appreciated

Max

I would advise uncommenting the application.volatile because otherwise the
function will not return the correct answer when the value in the second
cell (the one referenced by the offset) is changed.


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Tom Ogilvy" wrote in message
...
I would be interesting to know what makes you think it is slow. For me,
both mine and yours are pretty much instantaneouls and both update when I
change the per in a cell and pass it in as a cell reference (assume that
is
what you are talking about. The procedure is simple so I am not sure
what
optimzations you would do. Another approach:

Function ROC(val1 As Range, per As Variant)
' Application.Volatile
If val1.Row - per < 1 Then
ROC = ""
Else
ROC = val1 - val1.Offset(-1 * per + 1, 0).Value
End If
End Function

Yours causes an problem if the address resolves to less than row 1 because
you try to use it before you test for that condition.



--
Regards,
Tom Ogilvy


"Max" wrote in message
...
Below I have a function calculates the difference of 2 values based on a
cell reference and an interval.
For example: =ROC(A10,3) calculates the difference between A10 - A8
(which
is what I'm after)......


Function ROC(val1 As Range, per As Integer)

Set val2 = Cells(Range(val1.Address).Row - per + 1,
Range(val1.Address).Column)
If Range(val1.Address).Row - per < 1 Then
R0C = ""
Else

ROC = (val1 - val2)
End If

End Function


There are 2 problems with the above code.

1) When the formula is first entered it calculates correctly, however
when the 2nd parameter is referred to by reference the formula does not
recalculate correctly when the value in this cell is changed. It infact
shows the value of val1....I have checked the options and calculation is
set
to automatic and iterations are on and to 100...

2) It seems quite slow to run. Can it be optimised in anyway?

Any help would be appreciated

Max

It updated for me with both versions of the function (without
application.Volatile)

=roc(A4,A1)


I thought the same, but I can't argue with results. Perhaps the function
didn't be recomiled after I commented out hte application.volatile, but I
pasted in the OP's version (with a different function name) and it updated
as well

--
Regards,
Tom Ogilvy


"Charles Williams" wrote in message
...
I would advise uncommenting the application.volatile because otherwise the
function will not return the correct answer when the value in the second
cell (the one referenced by the offset) is changed.


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Tom Ogilvy" wrote in message
...
I would be interesting to know what makes you think it is slow. For me,
both mine and yours are pretty much instantaneouls and both update when
I
change the per in a cell and pass it in as a cell reference (assume that
is
what you are talking about. The procedure is simple so I am not sure
what
optimzations you would do. Another approach:

Function ROC(val1 As Range, per As Variant)
' Application.Volatile
If val1.Row - per < 1 Then
ROC = ""
Else
ROC = val1 - val1.Offset(-1 * per + 1, 0).Value
End If
End Function

Yours causes an problem if the address resolves to less than row 1
because
you try to use it before you test for that condition.



--
Regards,
Tom Ogilvy


"Max" wrote in message
...
Below I have a function calculates the difference of 2 values based on
a
cell reference and an interval.
For example: =ROC(A10,3) calculates the difference between A10 - A8
(which
is what I'm after)......


Function ROC(val1 As Range, per As Integer)

Set val2 = Cells(Range(val1.Address).Row - per + 1,
Range(val1.Address).Column)
If Range(val1.Address).Row - per < 1 Then
R0C = ""
Else

ROC = (val1 - val2)
End If

End Function


There are 2 problems with the above code.

1) When the formula is first entered it calculates correctly, however
when the 2nd parameter is referred to by reference the formula does not
recalculate correctly when the value in this cell is changed. It infact
shows the value of val1....I have checked the options and calculation is
set
to automatic and iterations are on and to 100...

2) It seems quite slow to run. Can it be optimised in anyway?

Any help would be appreciated

Max

Thanks everyone for your feeback.

For the performance issue I had this filled down 500 rows and was an it took a few seconds tro update. This isn't an issue when I use standard excel functions.

I must say i achieved the best results with Tom's suggestions, recalc is now fine, is not slow etc.

Regards,

Max

It does not recalc for me when i change the cell referred to by the offset,
unless i have application.volatile:

For example using =roc(A4,A1) it does recalc if either cell A4 or A1 are
changed, but if A1 contains 3 it does not recalc when A2 is changed.

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Tom Ogilvy" wrote in message
...
It updated for me with both versions of the function (without
application.Volatile)

=roc(A4,A1)


I thought the same, but I can't argue with results. Perhaps the function
didn't be recomiled after I commented out hte application.volatile, but I
pasted in the OP's version (with a different function name) and it updated
as well

--
Regards,
Tom Ogilvy


"Charles Williams" wrote in message
...
I would advise uncommenting the application.volatile because otherwise
the
function will not return the correct answer when the value in the second
cell (the one referenced by the offset) is changed.


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Tom Ogilvy" wrote in message
...
I would be interesting to know what makes you think it is slow. For
me,
both mine and yours are pretty much instantaneouls and both update
when
I
change the per in a cell and pass it in as a cell reference (assume
that
is
what you are talking about. The procedure is simple so I am not sure
what
optimzations you would do. Another approach:

Function ROC(val1 As Range, per As Variant)
' Application.Volatile
If val1.Row - per < 1 Then
ROC = ""
Else
ROC = val1 - val1.Offset(-1 * per + 1, 0).Value
End If
End Function

Yours causes an problem if the address resolves to less than row 1
because
you try to use it before you test for that condition.



--
Regards,
Tom Ogilvy


"Max" wrote in message
...
Below I have a function calculates the difference of 2 values based
on
a
cell reference and an interval.
For example: =ROC(A10,3) calculates the difference between A10 - A8
(which
is what I'm after)......


Function ROC(val1 As Range, per As Integer)

Set val2 = Cells(Range(val1.Address).Row - per + 1,
Range(val1.Address).Column)
If Range(val1.Address).Row - per < 1 Then
R0C = ""
Else

ROC = (val1 - val2)
End If

End Function


There are 2 problems with the above code.

1) When the formula is first entered it calculates correctly,
however
when the 2nd parameter is referred to by reference the formula does
not
recalculate correctly when the value in this cell is changed. It
infact
shows the value of val1....I have checked the options and calculation
is
set
to automatic and iterations are on and to 100...

2) It seems quite slow to run. Can it be optimised in anyway?

Any help would be appreciated

Max

I didn't read any requirement for that. Maybe I missed it.

--
Regards,
Tom Ogilvy

"Charles Williams" wrote in message
...
It does not recalc for me when i change the cell referred to by the
offset,
unless i have application.volatile:

For example using =roc(A4,A1) it does recalc if either cell A4 or A1 are
changed, but if A1 contains 3 it does not recalc when A2 is changed.

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Tom Ogilvy" wrote in message
...
It updated for me with both versions of the function (without
application.Volatile)

=roc(A4,A1)


I thought the same, but I can't argue with results. Perhaps the
function
didn't be recomiled after I commented out hte application.volatile, but
I
pasted in the OP's version (with a different function name) and it
updated
as well

--
Regards,
Tom Ogilvy


"Charles Williams" wrote in message
...
I would advise uncommenting the application.volatile because otherwise
the
function will not return the correct answer when the value in the
second
cell (the one referenced by the offset) is changed.


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Tom Ogilvy" wrote in message
...
I would be interesting to know what makes you think it is slow. For
me,
both mine and yours are pretty much instantaneouls and both update
when
I
change the per in a cell and pass it in as a cell reference (assume
that
is
what you are talking about. The procedure is simple so I am not
sure
what
optimzations you would do. Another approach:

Function ROC(val1 As Range, per As Variant)
' Application.Volatile
If val1.Row - per < 1 Then
ROC = ""
Else
ROC = val1 - val1.Offset(-1 * per + 1, 0).Value
End If
End Function

Yours causes an problem if the address resolves to less than row 1
because
you try to use it before you test for that condition.



--
Regards,
Tom Ogilvy


"Max" wrote in message
...
Below I have a function calculates the difference of 2 values
based
on
a
cell reference and an interval.
For example: =ROC(A10,3) calculates the difference between A10 -
A8
(which
is what I'm after)......


Function ROC(val1 As Range, per As Integer)

Set val2 = Cells(Range(val1.Address).Row - per + 1,
Range(val1.Address).Column)
If Range(val1.Address).Row - per < 1 Then
R0C = ""
Else

ROC = (val1 - val2)
End If

End Function


There are 2 problems with the above code.

1) When the formula is first entered it calculates correctly,
however
when the 2nd parameter is referred to by reference the formula does
not
recalculate correctly when the value in this cell is changed. It
infact
shows the value of val1....I have checked the options and
calculation
is
set
to automatic and iterations are on and to 100...

2) It seems quite slow to run. Can it be optimised in anyway?

Any help would be appreciated

Max

The requirement was not stated by the OP: thats why I suggested uncommenting
the application.volatile as advice rather than a correction.


regds
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Tom Ogilvy" wrote in message
...
I didn't read any requirement for that. Maybe I missed it.

--
Regards,
Tom Ogilvy

"Charles Williams" wrote in message
...
It does not recalc for me when i change the cell referred to by the
offset,
unless i have application.volatile:

For example using =roc(A4,A1) it does recalc if either cell A4 or A1 are
changed, but if A1 contains 3 it does not recalc when A2 is changed.

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Tom Ogilvy" wrote in message
...
It updated for me with both versions of the function (without
application.Volatile)

=roc(A4,A1)


I thought the same, but I can't argue with results. Perhaps the
function
didn't be recomiled after I commented out hte application.volatile,
but
I
pasted in the OP's version (with a different function name) and it
updated
as well

--
Regards,
Tom Ogilvy


"Charles Williams" wrote in message
...
I would advise uncommenting the application.volatile because
otherwise
the
function will not return the correct answer when the value in the
second
cell (the one referenced by the offset) is changed.


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Tom Ogilvy" wrote in message
...
I would be interesting to know what makes you think it is slow.
For
me,
both mine and yours are pretty much instantaneouls and both update
when
I
change the per in a cell and pass it in as a cell reference
(assume
that
is
what you are talking about. The procedure is simple so I am not
sure
what
optimzations you would do. Another approach:

Function ROC(val1 As Range, per As Variant)
' Application.Volatile
If val1.Row - per < 1 Then
ROC = ""
Else
ROC = val1 - val1.Offset(-1 * per + 1, 0).Value
End If
End Function

Yours causes an problem if the address resolves to less than row 1
because
you try to use it before you test for that condition.



--
Regards,
Tom Ogilvy


"Max" wrote in message
...
Below I have a function calculates the difference of 2 values
based
on
a
cell reference and an interval.
For example: =ROC(A10,3) calculates the difference between A10 -
A8
(which
is what I'm after)......


Function ROC(val1 As Range, per As Integer)

Set val2 = Cells(Range(val1.Address).Row - per + 1,
Range(val1.Address).Column)
If Range(val1.Address).Row - per < 1 Then
R0C = ""
Else

ROC = (val1 - val2)
End If

End Function


There are 2 problems with the above code.

1) When the formula is first entered it calculates correctly,
however
when the 2nd parameter is referred to by reference the formula
does
not
recalculate correctly when the value in this cell is changed. It
infact
shows the value of val1....I have checked the options and
calculation
is
set
to automatic and iterations are on and to 100...

2) It seems quite slow to run. Can it be optimised in anyway?

Any help would be appreciated

Max