Big problem with custom function

Jibse

New Member
Joined
Jan 8, 2009
Messages
38
Hello,

I have one custom function (not volatile) on one sheet, in more than 2000 cells. It works, but if I do a calculation in an another sheet which has no relations with the first one, the calculation on this first sheet is wrong (something like not terminated) and I have to do a CalculateFullRebuild.

Thanks for your help.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
I suggest you post the function code.
 
Upvote 0
Here is the function code
Code:
Function Interpolation(r0 As Range, anbase As Range, r1 As Range, r2 As Range, r3 As Range)
Dim k As Long, p As Long
Dim ra0 As Long
Dim ra1()
Dim ra3()
Dim ra5()


Application.Volatile False
ra0 = r0
ra1 = r1.Value
ra3 = Union(r3, r3.Offset(0, 1)).FormulaLocal
ra5 = Range(Cells(5, r2.Column), Cells(5, 22)).Value
If ra1(1, 2) <> "" Then[INDENT]If ra1(1, 1) <> "" Then[/INDENT]
[INDENT=2]If ra1(1, 1) = ra5(1, 2) Then[/INDENT]
[INDENT=3]Interpolation = ra1(1, 2)[/INDENT]
[INDENT=3]Exit Function[/INDENT]
[INDENT=2]ElseIf ra1(1, 1) > ra5(1, 2) Then[/INDENT]
[INDENT=3]p = ra1(1, 1)[/INDENT]
[INDENT=2]End If[/INDENT]
[INDENT]Else[/INDENT]
[INDENT=2]If ra0 = ra5(1, 2) Then[/INDENT]
[INDENT=3]Interpolation = ra1(1, 2)[/INDENT]
[INDENT=3]Exit Function[/INDENT]
[INDENT=2]ElseIf ra0 > ra5(1, 2) Then[/INDENT]
[INDENT=3]p = ra0[/INDENT]
[INDENT=2]End If[/INDENT]
[INDENT]End If[/INDENT]
End If
For k = 1 To UBound(ra3, 2) - 2[INDENT]If p > ra5(1, k + 1) Or p = 0 Then
        [/INDENT]
[INDENT=2]If Not ra3(1, k) Like "=*" Then
            [/INDENT]
[INDENT=3]Interpolation = r2 + (ra3(1, k) - r2) / (k + 1)
            Exit Function[/INDENT]
[INDENT=2]End If[/INDENT]
[INDENT]            ElseIf p > 0 Then
        [/INDENT]
[INDENT=2]Interpolation = r2 + (ra1(1, 2) - r2) / k
        Exit Function[/INDENT]
[INDENT]    End If[/INDENT]
Next k
If p > 0 Then[INDENT]Interpolation = r2 + (ra1(1, 2) - r2) / (p - ra5(1, 1))[/INDENT]
Else[INDENT]Interpolation = r2[/INDENT]
End If
End Function
 
Last edited by a moderator:
Upvote 0
RoryA,

I found the explanation. But it is very annoying. If a cell (say A) which contain my custom function is dependant from a cell which contain an OFFSET function (which don’t concern A), I get the problem. Curiously, the problem arise only if I make a change on an another sheet than the one that contain A.
An another problem with OFFSET function ?
 
Upvote 0
No - you need to remove the
Code:
application.volatile false
line.
 
Upvote 0
No. If I remove application.volatile false, I got the same problem. And after changing the formula of the cell containing the OFFSET function, the problem is solved. All my calculations are correct and my function don't need to be volatile.
 
Upvote 0
You do not need to add Application.Volatile False to a function to not make it volatile - that is the default. If you add that, you make the function fail to recalculate when its precedent cells are volatile (like the OFFSET function) which is exactly what you describe. Remove the line and re-enter the function.
 
Upvote 0
Yes, I understand, but before I tried with volatile true and with nothing, but the problem remain. I can send to you a very little file where I reproduce the problem.
 
Upvote 0
Did you re-enter the function in the cell?
 
Upvote 0
Yes. I am trying to insert an image here so you can reproduce the file with my custom function (after replacing FormulaLocal by Formula). Please wait some time because I never do that (add an image from url).
 
Upvote 0

Forum statistics

Threads
1,214,653
Messages
6,120,750
Members
448,989
Latest member
mariah3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top