Page 179 - Excel Progamming Weekend Crash Course

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

154 Saturday Afternoon

Many programmers find it useful to outline a program or procedure in
“pseudocode” before writing the actual VBA code. Pseudocode is nothing
Note more than plain English (or French, Spanish, and so on) descriptions of what
the program will do at each point.
Note that you can obtain the value of pi from the Excel function PI(). The initial for-
mula placed in cell C5 might be as follows:
=PI()*(B5^2)*C4

If, however, you were to copy this formula to the other cells in the table, the relative
ranges would be changed, and the formulas would not refer to the proper cells. For example,
when copied to cell D2, the result would be:

=PI()*(C6^2)*D5
Clearly this is not correct. The formula needs to use part absolute references, as follows:

=PI()*($B5^2)*C$4
When copied, the formula is correct, and the values in the table display properly.

Named Range References
As you learned in Session 10, you can assign names to ranges in a workbook. In your formu-
las, you can use an assigned name rather than a cell reference to refer to a cell. This applies
only to ranges that refer to a single cell.

Please refer to Session 10 for more information on naming ranges.

Cross-Ref
To refer to a named cell in a formula, simply use the range name in the formula. For
example, this formula displays the sum of the values in the cells SubTotal1 and SubTotal2:

=SubTotal1+SubTotal2
Using named cells instead of cell references offers several advantages. Cell references are
not adjusted if rows or columns are inserted or deleted in the worksheet; therefore, if a for-
mula refers to C5 and a row is inserted into the worksheet above row 5, the data that was in
C5 will now be in D5. Any formulas that reference C5 will still reference C5. In contrast, a
range name that had referred to C5 will refer to D5 after the row insertion, and the formula
will still work. Also, if data is moved from one location in the worksheet to another, the
assigned range names go along with it.
You can use multiple cell ranges in some of Excel’s built-in functions, which
are covered in Session 13.
Cross-Ref