Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force UDF to re-evaluate
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force UDF to re-evaluate
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. "Andrew Wiles" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force UDF to re-evaluate
Try a full re-calc with Ctrl-Alt-F9 or
Application.CalculateFull Regards, Peter T "Andrew Wiles" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force UDF to re-evaluate
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! "Joel" wrote: 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. "Andrew Wiles" wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force UDF to re-evaluate
Andrew Wiles wrote:
Is there any way to flag all udf's for re-evaluation? Yes. Put "Application.Volatile" at the start of the UDF. -- Adrian C |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force UDF to re-evaluate
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. "Andrew Wiles" wrote: 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! "Joel" wrote: 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. "Andrew Wiles" wrote: 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force UDF to re-evaluate
Adrian C wrote:
Andrew Wiles wrote: Is there any way to flag all udf's for re-evaluation? Yes. Put "Application.Volatile" at the start of the UDF. Oh, and then do Application.CalculateFull ... -- Adrian C |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force UDF to re-evaluate
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" wrote: Adrian C wrote: Andrew Wiles wrote: Is there any way to flag all udf's for re-evaluation? Yes. Put "Application.Volatile" at the start of the UDF. Oh, and then do Application.CalculateFull ... -- Adrian C |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force UDF to re-evaluate
Andrew Wiles wrote:
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? -- Adrian C |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
evaluate | Excel Worksheet Functions | |||
Formula will not evaluate in VBA | Excel Programming | |||
Why Evaluate? | Excel Programming | |||
Formula Will Not Evaluate | Excel Worksheet Functions | |||
evaluate a function | Excel Programming |