Page 184 - Excel Progamming Weekend Crash Course

k540629 ch12.qxd 9/2/03 9:34 AM Page 159

Session 12 — Programming with Custom Formulas 159

Figure 12-3 Selecting automatic or manual calculation in Excel

In your VBA programs, you can control workbook calculation as required by your pro-
gram. To set calculation to manual, set the Application object’s Calculation property to
xlCalculationManual:

Application.Calculation = xlCalculationManual
To return to automatic calculation, set this property to xlCalculationAutomatic.

If your program sets the calculation mode to manual, it should always set it
back to automatic before exiting.
Tip
When calculation is set to manual, a VBA program can force calculation to occur by call-
ing the Calculate method. This method can be called on three objects, as follows:
The Application object calculates all open workbooks.
The Worksheet object calculates a single worksheet.
The Range object calculates the specified range.
Here are some examples. This line of code calculates all open workbooks. Note that
because the Application reference is implicit, the Calculate keyword can be used alone:
Calculate
This code calculates all formulas in the Sales worksheet in the active workbook:

ActiveWorkbook.Worksheets(“Sales”).Calculate
This code calculates all formulas in columns A through D of the active worksheet:

ActiveSheet.Columns(“A:D”).Calculate