Archive of Mr Excel Message Board


Back to General Excel archive index
Back to archive home

Woorksheet Calculate after every entry

Posted by Leslie on March 08, 2001 12:41 PM
Why does my worksheet calculate after every entry?
It didn't before. I know there might be a macro somewhere, but how do I find it and turn it off?
Thanks

Re: Woorksheet Calculate after every entry

Posted by Wayneo on March 08, 2001 12:53 PM

Leslie,

One way to avoid this problem is to go to "Tools"
on the Menu bar....then select "Options"...then "Calculation". Select "Manual" and this will take care of the probelm. When you want the sheet to calculate, select "F9". Hope this helps


Re: Woorksheet Calculate after every entry

Posted by Aladin Akyurek on March 08, 2001 1:22 PM

One possibility is that your workbook contains named dynamic ranges. You can see them via Insert|Define|Name. It seems Excel has to check whether or not one of these ranges expanded AFTER ANY ENTRY ANYWHERE. One solution is, I guess, to use a faster computer.

Aladin


Re: Woorksheet Calculate after every entry

Posted by Mark W. on March 08, 2001 1:55 PM
Are you using a volatile function? What are the
functions that you're using?

Re: Woorksheet Calculate after every entry

Posted by Aladin Akyurek on March 08, 2001 2:26 PM

Mark: If the answer is yes (because of the use of OFFSET, INDIRECT, INDEX, etc.), what is the solution, save the manual calc or a faster computer?

Aladin


Re: Woorksheet Calculate after every entry

Posted by Mark W. on March 08, 2001 2:39 PM
Aladin, I don't believe OFFSET, INDIRECT, INDEX
are volatile. RAND and NOW are... and user-defined
functions that are explicitly set as volatile.
F2+Enter will cause this function to be re-evaluated.


Re: Woorksheet Calculate after every entry

Posted by Aladin Akyurek on March 08, 2001 2:56 PM

Mark: Maybe they are in Excel 97. Anycase, I've recently built a model that has a few named dynamic ranges (OFFSET) and INDIRECTs. etc., but no UDF's. Excel now keeps recalculating after any entry anywhere followed by just Enter. I suspect dynamic ranges to be the main reason for recalculations.


Also...

Posted by Mark W. on March 08, 2001 2:57 PM
From the Using the CALL and REGISTER Function help
topic:

"On a worksheet, you can make a DLL function
or code resource volatile, which means that it
recalculates every time the worksheet recalculates."


Re: Woorksheet Calculate after every entry

Posted by Mark W. on March 08, 2001 3:02 PM
Do any of the cells within these ranges have
a volatile function? I can make =1+B1 volatile
just by entering =RAND()*0 into cell B1.

Here's the poop!

Posted by Mark W. on March 08, 2001 3:12 PM
See http://support.microsoft.com/support/kb/articles/Q248/1/79.ASP

Functions that are recalculated automatically when
data in the worksheet changes are called volatile
functions. The following functions are volatile:

AREAS()
INDEX()
OFFSET()
CELL()
INDIRECT()
ROWS()
COLUMNS()
NOW()
TODAY()
RAND()


One approach...

Posted by Mark W. on March 08, 2001 3:21 PM

> what is the solution, save the manual calc or a
> faster computer?

One approach would be to move them to another
workbook that can remain closed and reference
these values externally.


Re: Woorksheet Calculate after every entry

Posted by Aladin Akyurek on March 08, 2001 3:45 PM

No. Just OFFSET, used to create named dynamic ranges via Insert|Name|Define, plus dynamically computed addresses of ranges which then figure in various formulas, wrapped into INDIRECT (eg INDIRECT($B$2) where B2 contains the address of a range, say, $A$1:$A$12). I use the latter for computations that are local/restricted to a single sheet.


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.