Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Determining if Calculation is Necessary.

Determining if Calculation is Necessary

by Allen Wyatt
(last updated December 28, 2013)

Excel allows you to control when it recalculates a worksheet. Normally, Excel recalculates anytime you change something in a cell. If you are working with very large worksheets that have lots of formulas in them, you may want to turn off the automatic recalculation feature. You can turn off automatic recalculation using controls on the Calculation tab of the Options dialog box.

Your macros can also force Excel to recalculate your worksheet. If you have automatic recalculation turned on, then any change your macro makes in a worksheet will force Excel to recalculate. If you have automatic recalculation turned off, then you can use the Calculate method to recalculate a worksheet:

ActiveSheet.Calculate

Of course, if recalculation takes quite a while to perform, you might want to check to see if a recalculation is necessary before actually forcing one. It appears that there is no flag you can directly check to see if a recalculation is necessary. The closest thing is to check the Workbook object's Saved property. This property essentially acts as a "dirty flag" for the entire workbook. If there are unsaved changes in a workbook, then the Saved property is False; if everything is saved, then it is True.

How does this help you figure out if a recalculation is necessary? Remember that calculation is only necessary when there are changes in a worksheet. Changing anything in a worksheet will also set the workbook's Saved property to False. Thus, you could check the Saved property before doing the recalculation, as shown here:

If Not ActiveWorkbook.Saved Then
    ActiveSheet.Calculate
End If

There is only one problem with this approach, of course—the Saved property is only set to True if the workbook is actually saved. This means that you could recalculate multiple times without really needing to do so, unless you tie saving and recalculation together, as shown here:

If Not ActiveWorkbook.Saved Then
    ActiveSheet.Calculate
    ActiveWorkbook.Save
End If

The wisdom of approaching this problem in this manner depends on the nature of your particular situation. If it takes longer to save the workbook than it does to simply recalculate, then this approach won't work. If, however, recalculation takes longer (which is very possible with some types of operations), then this approach may work well.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2323) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Determining if Calculation is Necessary.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Maintaining Delivery Address Information for Envelopes

Using the Envelopes and Labels dialog box to print envelopes is quick and easy. It can also be frustrating, however, since ...

Discover More

Functions Within Functions

Functions are the heart of Excel's power. The program allows you to compound that power by handily putting one function ...

Discover More

Formatting an ASCII Table with Tabs

If you get a document from a coworker that has tabs used to line up tabular information, you might want to change that type ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

MORE EXCELTIPS (MENU)

Quickly Dumping Array Contents

Variable arrays are used quite often in macros. If you use an array once in your macro and then need to reuse it for ...

Discover More

Getting Big Macros to Run

Troubleshooting an Excel macro when it causes the entire computer to freeze can be a tedious affair. This tip provides some ...

Discover More

Running a Macro When a Worksheet is Deactivated

You can easily configure Excel so that it runs a specific macro whenever a worksheet is deactivated. Just follow the easy ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share