Page 183 - Excel Progamming Weekend Crash Course

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

158 Saturday Afternoon

If you enter a circular reference from a VBA program, however, Excel does not object. The
involved cell, however, is not calculated but instead displays 0. To detect circular references
in code, use the Worksheet object’s CircularReference property. This property returns a
Range object that references the first circular reference on the worksheet, or Nothing if
there are no circular references. You can use this property to indicate the source of the cir-
cular reference in the worksheet. The program in Listing 12-1 shows how.

Listing 12-1 A program to detect circular references

Sub ShowCircularReferences()

If ActiveSheet.CircularReference = Nothing Then
MsgBox “No circular references on “ & ActiveSheet.Name
Else
ActiveSheet.CircularReference.Select
End If
End Sub

When run, this program checks for circular references on the active worksheet. If none is
found, a message to that effect is displayed. If one is found, it is selected in the worksheet.
Use of the CircularReference property is usually restricted to program development. As
a programmer, you can use the program in Listing 12-1 or something similar to it to verify
that your programs do not result in circular references in the project’s worksheets.

Controlling Formula Calculation

Excel’s default is to automatically recalculate all formulas in a workbook as needed. With
most workbooks this is fine, because calculation is performed so quickly that it does not
cause noticeable delays or other problems. There can be situations, however, in which auto-
matic calculation is not desirable. In particular, some large, complex workbooks with lots of
formulas and links can take a while to calculate, even on a fast computer. For this reason,
Excel offers you the option of manual recalculation.
When you are working in Excel, you set the calculation mode by selection Tools ➪
Options to display the Options dialog box; then, on the Calculation tab (see Figure 12-3),
select either Automatic or Manual.
When manual calculation mode has been selected, it affects all open workbooks. The cal-
culation mode is a property of the Excel application and not of individual workbooks or
worksheets. Then while still in Excel, do the following:

Press F9 to calculate all open workbooks.
Press Shift+F9 to calculate only the active worksheet.