RCStyle References

The R1C1 style uses the letters R for row and C for column followed by numbers to reference spreadsheet cells. For example, R[-1]C[2] is a relative reference to the cell one row lower and two columns higher than the cell that contains this reference in a formula. To denote an absolute reference, leave off the brackets (for example, R-1C2 ). The R1C1 reference style can be turned on in the Excel application by clicking Tools, Options, General, and then clicking R1C1 reference style as shown in Figure 4.6.

You can use the R1C1 reference style in your VBA code any time. It can be a preferable style to use when dealing with references to columns, as the indices use a numerical value. The value of the string variable formulaString in the previous example can be assigned as shown here:

formulaString = "=R[0]C[-1]+ R[-1]C[0]" Cells(2, "B").FormulaR1C1 = formulaString

Although the Formula property of the Range object returned by the Cells property would work just as well, I have used the FormulaR1C1 property for consistency.

Selecting the R1C1 reference style

Selecting the R1C1 reference selection in the Excel application.

Selecting the R1C1 reference style

Selecting the R1C1 reference selection in the Excel application.

Whether you use the A1 style or R1C1 reference style in your VBA code is of no consequence to the user. The user will see whichever style they have set their Excel application to use.
Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Post a comment