Page 146 - Microsoft Office Excel 2003 Programming Inside Out

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

Inside Out

Limiting the Calculations
The Calculate method can also be used on Worksheet and Range objects, and it allows you
to narrow down the number of calculations that are performed. Using a Worksheet object
will perform all needed calculations on the specified worksheet. The following example cal­
culates the active sheet:
Sub RecalcSheet()
If Application.Calculation <> xlCalculationAutomatic Then
ActiveSheet.Calculate
End If
End Sub
To calculate a limited number of cells, use a Range object to specify the cells you want to
recalculate. This example recalculates the formulas for all cells in the range C1:G13:
Sub Recalc()
ActiveSheet.Range("C1:G13").Cells.Calculate
End Sub

CalculateFull Method
CalculateFull forces all open workbooks to recalculate all cells regardless of the contents. The
following example recalculates all open workbooks:

Sub ReCalcFull()
Application.CalculateFull
End Sub

FindFile Method
The FindFile method displays the Open dialog box and opens the file selected by the user. It
returns a True value if a file was successfully opened or a False if the user clicked the Cancel
button. The following example prompts the user to locate the ProductList.xls file:

Sub FindProductList()
Dim bReturn As Boolean, strPrompt As String
strPrompt = "Please locate the ProductList.xls file."
MsgBox strPrompt, vbOKOnly + vbInformation, " Microsoft Office Excel 2003 _
Programming Inside Out"
bReturn = Application.FindFile
If Not bReturn Then
strPrompt = "File not opened."

Chapter 6

120