How do I stop formulas from calculating when I open a worksheet

[J. Caplan]

I have a worksheet that calls several user defined functions. Every time I
open up the file in Excel, I have to wait while it recalculates every cell
with a formula.

How do I prevent this from occurring on startup?

 

[XP]

Navigate to [Tools] then [Options] then [Calculation] tab. Click "Manual".

If you want this equivalent in code:

Application.Calculation = xlCalculationManual

to reset:

Application.Calculation = xlCalculationAutomatic

HTH

 

[J. Caplan]

I don't want to go through the menu option since I don't want my users to
have to do that.

If I used Application.Calculation = xlCalculationManual and put it in the
Workbook_Open event, this fires if I just open Excel and causes an error:
Method 'Calculation' of object '_Application' failed. If I open the XLS
file directly it still calculates. Plus I will want auto calculation on
once the document is open. Turning it back on is going to cause everything
to recalculate.

I could set a flag so that in my function, when called, it does nothing if
the flag is set. The problem is knowing when to flip the flag back on so
that when the function is called AFTER the doc is open, it does something
again.

Navigate to [Tools] then [Options] then [Calculation] tab. Click "Manual".

If you want this equivalent in code:

Application.Calculation = xlCalculationManual

to reset:

Application.Calculation = xlCalculationAutomatic

HTH

I have a worksheet that calls several user defined functions. Every time I
open up the file in Excel, I have to wait while it recalculates every cell
with a formula.

How do I prevent this from occurring on startup?

 

[PBezucha]

I usually solve your last problem by placing the flag into a cell on a
worksheet, where the background color is conditionally formated - hot red
when recalculation stopped - and this strikes me sufficiently to be aware of
possible improper result.
Petr Bezucha

I don't want to go through the menu option since I don't want my users to
have to do that.

If I used Application.Calculation = xlCalculationManual and put it in the
Workbook_Open event, this fires if I just open Excel and causes an error:
Method 'Calculation' of object '_Application' failed. If I open the XLS
file directly it still calculates. Plus I will want auto calculation on
once the document is open. Turning it back on is going to cause everything
to recalculate.

I could set a flag so that in my function, when called, it does nothing if
the flag is set. The problem is knowing when to flip the flag back on so
that when the function is called AFTER the doc is open, it does something
again.

Navigate to [Tools] then [Options] then [Calculation] tab. Click "Manual".

If you want this equivalent in code:

Application.Calculation = xlCalculationManual

to reset:

Application.Calculation = xlCalculationAutomatic

HTH

I have a worksheet that calls several user defined functions. Every time I
open up the file in Excel, I have to wait while it recalculates every cell
with a formula.

How do I prevent this from occurring on startup?

 

[J. Caplan]

I think that is a different issue.

I have found out more information on this though. I had one of my users
give me a spreadsheet that they have that calls UDFs in an Add-In that I do
not have. When I open the workbook, it opens fine even though I don't have
the Add-In. When I do something to a cell used in the formula, I get errors
in the cell, as expected. What this tells me though, is that when I open the
Workbook, the cells are NOT being recalculated. If I add calls to my UDF as
well on this workbook, it DOES recalculate them when I open the workbook. I
am trying to figure out why it wants to recalculate my formula's.

I usually solve your last problem by placing the flag into a cell on a
worksheet, where the background color is conditionally formated - hot red
when recalculation stopped - and this strikes me sufficiently to be aware of
possible improper result.
Petr Bezucha

I don't want to go through the menu option since I don't want my users to
have to do that.

If I used Application.Calculation = xlCalculationManual and put it in the
Workbook_Open event, this fires if I just open Excel and causes an error:
Method 'Calculation' of object '_Application' failed. If I open the XLS
file directly it still calculates. Plus I will want auto calculation on
once the document is open. Turning it back on is going to cause everything
to recalculate.

I could set a flag so that in my function, when called, it does nothing if
the flag is set. The problem is knowing when to flip the flag back on so
that when the function is called AFTER the doc is open, it does something
again.

Navigate to [Tools] then [Options] then [Calculation] tab. Click "Manual".

If you want this equivalent in code:

Application.Calculation = xlCalculationManual

to reset:

Application.Calculation = xlCalculationAutomatic

HTH


:

I have a worksheet that calls several user defined functions. Every time I
open up the file in Excel, I have to wait while it recalculates every cell
with a formula.

How do I prevent this from occurring on startup?