How do I force a named range to recalculate?

[foxguy]

I have a named range that uses a UDF(). I want it to recalculate at my choosing. I tried this.

Please Login or Register  to view this content.

I thought that changing the value of CodesCount would trigger myRange to recalculate since it is dependent on CodesCount, but it doesn't.

Why not?

I want to put several hundred named ranges like this in my workbook. I want them all to recalculate at the same time. How can I do it?

[watersev]

hi foxguy, is your UDF is set to Volatile?

To calculate named range:

Please Login or Register  to view this content.

[foxguy]

UDF() is not volatile.
I guess I didn't explain myself.
I have several hundred named ranges. I want them ALL to recalculate at the same time. It's impractical to tell each individual range to recalculate. That's why I made them all dependent on the same cell, so they would all recalculate when that cell changed, but it didn't work.

[watersev]

UDF should be set to volatile otherwise it will not recalculate on the change of involved cells

VB help file:

Volatile Method

Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell.

expression.Volatile(Volatile)
expression Required. An expression that returns an Application object.

Volatile Optional Variant. True to mark the function as volatile. False to mark the function as nonvolatile. The default value is True

Example
This example marks the user-defined function "My_Func" as volatile. The function will be recalculated whenever calculation occurs in any cells on the worksheet on which this function appears.

Please Login or Register  to view this content.

[watersev]

can you post sample workbook?

[foxguy]

Making it volatile will make it recalculate EVERY time the sheet calculates.

[watersev]

oh, ok, if all the ranges calculated by UDF(non volatile) dependent on one cell and calcaulation event is not trapped, you have enable events turned off

[foxguy]

It started working all of a sudden. I have no idea what I did to get it to work.

But it only works on one defined name.

Please Login or Register  to view this content.

myRange recalculates when CodesCount changes, but myRange2 doesn't recalculate.

[watersev]

as to my experimenting, the non-volatile function will be recalculated if range value that is argument of UDF has been changed. Enableevents does not influence UDF recalculation.

If the UDF argument cell is changed to the same value the UDF is not recalculated

[foxguy]

That's what I'm trying to do.
CodesCount changes about once every 3 months. I don't want all the named ranges to recalculate every time a Worksheet_Calculate() executes. I want them to only recalculate when CodesCount changes. Currently when CodesCount changes only myRange recalculates, not myRange2.

I have discovered that if I put a formula in a cell : =Rows(myRange2) then myRange2 recalculates when CodesCount changes. I don't like that solution, but at least it gets things done.

[Leith Ross]

Hello foxguy,

You can force any Range object to recalculate by using the Calculate method.

Please Login or Register  to view this content.

[foxguy]

I don't want to have to name each individual range to calculate it. There are at least 80 of them. I would rather have them all be recalculated because a precedent was recalculated. So having them all dependent on CodesCount and then replace CodesCount with the new value should do it (I hope).

[Leith Ross]

Hello foxguy,

VBA does not behave like Excel formulas. If you choose to use VBA then you are bound by its rules. At this point, I think you have tapped all your options for a solution.

[foxguy]

Hi Leith;

I just hope that this works the way I think it should. It seems like I've been trying to figure out how to make a non-volatile dynamic range for over a year. I'm keeping my fingers crossed that this works.