triggering a non volatile UDF()

I'm not positive that I completely understand volatility yet.

I have 2 Dynamic Named Ranges (Code & Desc), which are obviously volatile. But the values in the Ranges are 99.9% static, so I'm trying to figure out how to keep formulas that refer to the Ranges non volatile.
But for that 0.1% of the time that the value changes I need the formulas to recalculate.
I think I have figured it out, but I'd like to see if anyone sees a problem with my solution or if there is a better solution somewhere.

Please Login or Register  to view this content.

The idea here is that when I select a code in cell $F$1, $G$1 obviously recalculates, just what I want.
But when I change cell $B$3 to "bcb" (about once a year), I need $G$1 to recalculate.

The only problem I can see with this is:
I sometimes work with EnableEvents turned off. If I change $B$3 while it's off, it won't update when I turn Events back on.

So I'm still working to find a way around that. Any suggestions?

Will this help ?

Please Login or Register  to view this content.

I don't want myUDF to be volatile. 99.9% of the time. I just want it to recalculate if one of the values in the Dynamic Range changes.

I've tried to imitate.

I think that's what I've done.
Code is stable. myRecalc is changed in the Worksheet_Change() whenever something in Desc is changed, which then forces myUDF() to recalculate.

I was hoping someone could look at my solution and confirm that there is nothing that could change that myUDF() did not pick up.

I also can't download HSV's file at this time.

A Dynamic Range is recalculated with every calculation in any open workbook, so Code and Desc are constantly being recalculated. My Worksheet_SelectionChange() stores the row & column numbers (for conditional formatting) so Code and Desc are being recalculated whenever I move the cursor in any open workbook. But they are static 99.9% of the time, which needlessly slows down all workbooks when this one is open.
By putting both Code and Desc inside the UDF, Excel doesn't keep recalculating the Range Names, which speeds up all my workbooks.

But I have to handle the 0.1% of the time when a value inside Desc changes. By having the Worksheet_Change() check to see if a value inside Desc has been edited and changing myReCalc when it does, it causes myUDF() to be recalculated only when a value inside Desc has changed (or when the size of the ranges change) which is what I've been trying to accomplish for over a year. It's not as easy as just letting Excel figure every thing out, but it speeds up my workbooks.

You're right that Worksheet_Change doesn't actually change myReCalc, but it does force Excel into marking the cell for recalculation. Excel doesn't keep track of whether the value has actually changed, only that the cell was edited.
Disabling events when changing myReCalc just prevents Worksheet_Change from being triggered again. This wouldn't do any harm in this situation, but the rest of the Worksheet_Change() might do harm.
If it doesn't disable events then the order of things would be:
1) Edit Desc which triggers 1st round of Worksheet_Change.
2) Worksheet_Change determines that Desc has been edited and changes myReCalc which triggers 2nd round of Worksheet_Change().
3) 2nd round of Worksheet_Change doesn't care that myReCalc has been edited so continues to end.
4) 1st round of Worksheet_Change finishes.

My English is not so good as well.
Your hole story is a bit to long for me to get de clou.
Maybe this will help.

Please Login or Register  to view this content.

HSV;
I don't want to mark the entire sheet Dirty. Then every formula that refers to anywhere on the sheet would be recalculated. That would probably be worse than what I already have.

Maybe?

Please Login or Register  to view this content.

That's the same thing as Range("G1").Value = Range("G1").Value
I keep forgetting about "Dirty" because I've never used it. I'll use it now, so maybe next time I'll remember it.

Hi,
What happens if you do an
Application.Calculate or Application.CalculateFull ?

Will this recalculate all those UDFs?

They do no calculate, because none of the precedents for the UDFs are Dirty. I do think they calculate once when the file is opened, so the file takes a long time to open, but then it runs faster after that.