Page 182 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Microsoft Office Excel 2003 Programming Inside Out

Worksheet Methods
Worksheets are the lifeblood of a workbook, and there are a number of methods you can call
to get the most out of your worksheets. Some of these methods are similar to those you’ll find
with the workbooks, but with subtle distinctions that make them unique to the worksheet. In
Chapter 7
this section, you’ll learn about the following methods:
● Calculate, which recalculates the results of all formulas in a worksheet
● Protect, which lets you require users to enter a password to modify specified elements
of a worksheet
● SaveAs, which saves a worksheet as a new workbook
● Select, which lets you work on more than one worksheet at a time

Calculate Method

The Worksheet object’s Calculate method is used to recalculate all of the formulas in a work-
sheet, which is handy if those formulas draw on data that might have changed since the last
time you opened the workbook containing those formulas. The Calculate method’s syntax is
straightforward; all you need to do is name the worksheet you want to calculate and append
the Calculate method, as in the following examples:

Worksheets(1).Calculate
Worksheets("Summary").Calculate
If you want to update the result of every formula in every open workbook, you can add the
single method Calculate (short for Application.Calculate) to your VBA code to have Excel
recalculate each of those values.

Normally Excel recalculates your formulas whenever you make a change, so you might want
to change when Excel recalculates your formulas. Choosing when to recalculate the values of
formulas in your worksheets is an inexact science; if you always need the more recent results
from formulas that could change from moment to moment, it makes sense to recalculate
every time your worksheet changes. Another approach would be to place a command button
on a worksheet that your users could click to recalculate the formulas whenever they needed
up to date values to make a decision. The difficulty with that approach is when you have a
large worksheet that draws data from several other sources. If your network is busy, or if you
have literally hundreds of formulas that need to be updated, you might want to create a pro-
cess that updates the formulas once overnight, bearing in mind that you would want to
update each of the source worksheets first to avoid any potential inconsistencies caused by
updating the summary worksheet before updating the source worksheets.
You can change when Excel recalculates a worksheet by setting the Application object’s Calculate
property (yes, it has the same name) to one of the XlCalculate constants. Those constants are:

156