Not recalc in custom function

[Guest]

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

 

[Patrick Molloy]

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...

 

[Guest]

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...

 

[Patrick Molloy]

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...

 

[Tom Ogilvy]

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

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...

 

[Jean-Yves]

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

JY

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...

 

[Charles Williams]

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]

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

 

[Guest]

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

 

[Charles Williams]

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]

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

 

[Charles Williams]

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