Force UDF to re-evaluate

[Guest]

I am writing an automation add-in in Office 2007 that implements a number of
user defined functions that use a remote service to retrieve data.

When these functions are first entered, and when a workbook that contains
them is first opened, they evaluate correctly. Once evaluated however I do
not seem to be able to force a re-evaluation. The Calculate Now and Calculate
Sheet options have no effect.

Is there any way to flag all udf's for re-evaluation?

 

[Guest]

Functions are only called when the worksheet gets changed. You cannot force
the change. If you modifiy a UDF you must force a change in the worksheet to
get them to execute.

Usually a simple click on a cell where the UDF is located. then click on
the Fx box and press Enter on Keyboard.

 

[Peter T]

Try a full re-calc with Ctrl-Alt-F9 or
Application.CalculateFull

Regards,
Peter T

 

[Guest]

Which would mean that I need to be able to find and edit (without actually
changing) all the cells that contain my UDF in order to get them to
re-evaluate.

Yuk!

 

[Adrian C]

Yes. Put "Application.Volatile" at the start of the UDF.

 

[Guest]

Once update worksheet start to work all the cells with that formul get
changed. Not exactl;y sure why, I just know from experience that it does.

 

[Adrian C]

Yes. Put "Application.Volatile" at the start of the UDF.

Oh, and then do Application.CalculateFull ...

 

[Guest]

Thanks Adrian

This looks like the answer. I am assuming that the Application.Volatile
reference is switching this feature on for ALL udf's and can therefore be
placed in any startup code for the app.

Looking through some of the other posts relating to application.volatile it
appears that for Excel Services this can be declared using reflection for an
individual function. Are you aware of whether this technique could be used as
part of an automation add-in for use inside a normal Excel session?

 

[Adrian C]

Thanks Adrian

This looks like the answer. I am assuming that the Application.Volatile
reference is switching this feature on for ALL udf's and can therefore be
placed in any startup code for the app.

You'll have to test to verify it, but i don't think it's a global
application setting - at least what's mentioned in Excel's help file,
the setting goes inside the start of EACH UDF that requires notification
of sheet updates.

Looking through some of the other posts relating to application.volatile it
appears that for Excel Services this can be declared using reflection for an
individual function. Are you aware of whether this technique could be used as
part of an automation add-in for use inside a normal Excel session?

Depends on what the user is running in the 'normal Excel session'. If
he/she is running other data driven or calculated stuff, something like
Application.CalculateFull could be harmful if done at the wrong moment!

Instead of getting the cells to pull data from UDFs, can you just write
/ paste the data in from your remote service?