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

"Robin Hammond" wrote in message
...
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

please check your system date
--
Kind Regards,

Niek Otten

Microsoft MVP - Excel
"Robin Hammond" wrote in message
...
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

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

-----Original Message-----
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

"Robin Hammond" wrote
in message
...
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




.

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.

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