Big problem with custom function

[Jibse]

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.

 

[RoryA]

It would be easier if you can upload a workbook to a site like OneDrive / Dropbox and then post a link here so I can see the function in a workbook that doesn't work for you.

 

[Jibse]

Then, if you do a calculation on Sheet2 (F9 for example, even a full calculation), the result will be 150. You have to go back to Sheet1 to recalculate. After, if you replace OFFSET(...) by the range, the problem is solved.

 

[RoryA]

OK I should have looked closer at the rest of the function code. You have an unqualified reference to ranges in your code here:

ra5 = Range(Cells(5, r2.Column), Cells(5, 22)).Value

when your code recalculates, it will refer to the active sheet, not the one containing the function. You also should actually make your function volatile if you refer to cells in the function that are not passed as arguments - otherwise your function will be incorrect when those cells are changed, unless the supplied arguments are also changed.

 

[Jibse]

Oh, Yes ! Thanks a lot. It works now. And thank you also because now I understand better the question of volatile.
Have a wonderful WE !

 

[Jibse]

You do not need to add Application.Volatile False to a function to not make it volatile - that is the default.

Are you sure ? Because I read the contrary in the VBA Help. Perhaps it's a false traduction ?

 

[RoryA]

Yes, I'm sure. The Help file means that the default value of the argument that you pass to Application.Volatile is True. That just means that if you use:

Application.Volatile

on its own, it's the same as using:

Application.Volatile True

It doesn't mean that all UDFs are volatile by default - that would be the worst design decision ever!

 

[Jibse]

Oh Yes ! Many thanks RoryA.