Volatile Functions (EXCEL)

[bryanbayfield]

OK dudes, riddle me this.

Volatile functions, VBA help:

[tt]Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet.[/tt]

I've marked my function as volatile - it is used on sheet 3, and takes two arguments - one is a reference to a range on sheet 3, the other is a byte.

The function uses range values on sheet 1 to come up with an answer. I have coded for the worksheet change event, just one line for when the worksheet changes, the value in sheet 4 changes, this value in sheet 4 serves as an indicator variable. If it is true then the user is asked whether they want to recalculate.

Given the above, I am perplexed as to why when I change values in sheet 1, sheet 3 automatically recalculates (I have two windows open on the workbook). Is the Excel help file incorrect (!), and should it actually read "whenever a change occurs on any cell in the workbook".

I am perplexed because, there is no direct dependency between sheet 1 values and the worksheet formulae in sheet 3, but there is a depeny between sheet 1 values and teh coding for the user-defined functions that are in sheet 3.

Irritating.

 

[Bowers74]

I am assuming it is because you have reference the range in Sheet 1 in your function. This makes Sheet 1 "belong" to the Volatile area (even though it doesn't "belong" to the calculation part of the function.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[bryanbayfield]

A range in Sheet 1 is included in the UDF code, but not the worksheet function.

So that makes it volatile with reference to sheet 1?

The VBA help file comes up trumps - again - not.

 

[Bowers74]

Since the user is going to be asked to confirm the calculation, why not just remove the Volatile from the function altogether?

Or did I miss something?

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[bryanbayfield]

I think I'd rather remove the confirmation part and just make the function volatile.

The reason I'd coded the confirmation part was to ensure that if the user changed cells on sheet 1, they were given the chance to make the formulae recalculate. But if that's done automatically, there's no point.

 

[terzaghi]

did you tried with

Application.Volatile True

inside the funcion... it makes your function be recalculated every time all is recalculated in excel (or every time you press F9)

Hope this will help

andrea