Turning Calcuation Off Completely

  • Thread starter Thread starter Charles Williams
  • Start date Start date
C

Charles Williams

Hi Robin,

I do not know of a way to stop excel evaluating a formula as it is entered
(apart from fill which I do not think will help you).

Some ideas for exiting the UDF when it is you inserting it:

if application.caller.formula="" then exit function

or

if application.caller.formula like "=*Myfunc(*)*" then exit function

or put a hidden name in the workbook =true when you are inserting and =false
after you have inserted, and check that in the function.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com
 
Here's the problem...

Main routine does this

1. sets calculation to manual, saving old calc state (usually auto)
2. Loops through a region inserting a User Defined Function either cell by
cell as a formula or range by range as an array formula
3. Even though calculation is set to manual, on each insert the UDF triggers
and calculates even though I don't want it to.
4. The UDF is hitting a web server to return a value from a database and is
not the quickest thing in the world. It's pretty cool though.
5. Routine completes (slowly because of all the calculations as it goes)
6. Calculation is reset to its old state, and if this was automatic, the udf
then recalculates all the values even though the udf is not flagged as
volatile and nothing else has changed. If it was manual, I could of course
recalculate at this stage if I needed to.

One problem is that the UDF is in a different addin so I cannot run a public
variable and use this as a get out clause when the UDF triggers.

Any ideas anybody on how I can really turn calculation off or restructure
this to avoid a double calculation loop.

Thanks as always,

Robin Hammond
www.enhanceddatasystems.com
 
How about mis-naming the function, then doing a global
find and replace with the correct function name after all
have been entered?

Alternatively, if you're willing to play wih fire, you may
want to look at VBA Extensibility and have self-modifying
code...
With ThisWorkbook.VBProject.VBComponents
("mACM_special")
n = .CodeModule.ProcStartLine("User Defined
Function", vbext_pk_Proc) + 2
.CodeModule.ReplaceLine n, "Exit Function"
End With

Kevin Beckham
 
Hi Robin,
One problem is that the UDF is in a different addin so I cannot run a public
variable and use this as a get out clause when the UDF triggers.
Click to expand...

If there's nothing you can do modify the UDF source, one option is to wrap
your own UDF around it, within which you can respond to have a public 'DoCalc'
boolean.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk
 
Back
Top