Microsoft Office Tutorials and References

In Depth Information

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

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
Wo rksheet
object’s
Calculate
method is used to recalculate all of the formulas in a

worksheet, 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

process 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: