< Day Day Up > |

In addition to dealing with the Excel objects, understanding how to use R1C1 format to write formulas makes coding much easier. For example, subtracting the cell one row above from the cell two rows above is very straightforward when using R1C1 notation . If you use A1 notation, you have to go through multiple steps to write the string. First, figure out what column and row you are in, and then turn the column number into a letter. Next, create strings by concatenating the column letter with the row numbers needed for your formula. Doing this for several columns would get tiresome, not to mention difficult to follow. You could also perform the calculation in VBA by using the row and column numbers with the
When you write formulas, there are several properties that you can set to write the formula or value (see Table 8-1). This chapter covers Formula, FormulaR1C1, and FormulaArray, which provide most of the functionality that you need.
Let's look at returning R1C1 formula values so that you can get a feel for how to write them. Specifically, you need to understand how to set fixed and relative references. Look at the formulas in Figure 8-1, shown in A1-style notation below each result. Create a workbook similar to this one and press Alt+F11 to go to the Visual Basic Editor. Then, press Ctrl-G to go to Immediate Window. The first formula to look at is cell B5, also shown as text in cell B6 in Figure 8-1. In this formula, use relative references so that if you copy them from left to right, they update to the correct formula. If you copy them down, this formula will not yield the correct result. Type the following line in the immediate window, and it shows you how to write this formula in R1C1-style notation: ? sheets("Sheet1").Range("B5").FormulaR1C1 The result comes back ## Figure 8-1. The worksheet showing similar formulas written a variety of different waysup in the current column. Next, look at the formula in cell C5. Type the following line in the immediate window: ? sheets("Sheet1").Range("C5").FormulaR1C1 This formula result comes back ? sheets("Sheet1").Range("D5").FormulaR1C1 The formula result comes back ? sheets("Sheet1").Range("B8").FormulaR1C1 The formula results come back Certainly look at the other formulas; looking at the resulting R1C1 formulas should help you write them yourself. I strongly encourage you to use this technique to write more complex formulas, since it is much easier to create the formula in the Excel GUI than to write it yourself. |

< Day Day Up > |

Integrating Excel and Access

ISBN: 0596009739

EAN: 2147483647

EAN: 2147483647

Year: 2005

Pages: 132

Pages: 132

Authors: Michael Schmalz

flylib.com © 2008-2017.

If you may any questions please contact us: flylib@qtcs.net

If you may any questions please contact us: flylib@qtcs.net