Difference between Formula and FormulaR1C1 in Microsoft Excel


To totally unlock this section you need to Log-in


Login

Question

Explain the difference between the excelrange.formula and excelrange.FomulaR1C1 in Microsoft Excel...

Answer

In VBA Formula returns the formula in A1 notation, FormulaR1C1 returns the formula in R1C1 notation.

By default, Excel uses the A1 reference style, which refers to columns with letters (A through XFD) and refers to rows with numbers (1 through 1048576). These letters and numbers are called row and column headings. To refer to a cell, enter the column letter followed by the row number. For example, D50 refers to the cell at the intersection of column D and row 50.

In the R1C1 reference style, both the rows and the columns on the worksheet are numbered. In the R1C1 style, Excel indicates the location of a cell with an "R" followed by a row number and a "C" followed by a column number.

For example, the absolute cell reference R1C1 is equivalent to the absolute reference $A$1 in A1 reference style.

Example

This example illustrates the difference between A1, R1C1 and R[1]C[1] style in Excel VBA.

A1 Style

Place a command button on your worksheet and add the following code line (A1 style):

Range("D4").Formula = "=B3*10"

Result:

Difference between Formula and FormulaR1C1 in Microsoft Excel

Difference between Formula and FormulaR1C1 in Microsoft Excel

R1C1 Style

Add the following code line (R1C1 style):

Range("D4").FormulaR1C1 = "=R3C2*10"

Result:

Difference between Formula and FormulaR1C1 in Microsoft Excel

Difference between Formula and FormulaR1C1 in Microsoft Excel

Explanation: cell D4 references cell B3 (row 3, column 2). This is an absolute reference ($ symbol in front of the row number and column letter).

R[1]C[1] Style

Add the following code line (R[1]C[1] style):

Range("D4").FormulaR1C1 = "=R[-1]C[-2]*10"

Result:

Difference between Formula and FormulaR1C1 in Microsoft Excel

Difference between Formula and FormulaR1C1 in Microsoft Excel

Explanation: cell D4 references cell B3 (one row above and 2 columns to the left). This is a relative reference. This code line gives the exact same result as the code line used at A1 style.

Why learning about this? Because the Macro Recorder uses the FormulaR1C1 property (R[1]C[1] style). The Macro Recorder in Excel creates the following code lines if you enter the formula =B3*10 into cell D4.

Difference between Formula and FormulaR1C1 in Microsoft Excel

Difference between Formula and FormulaR1C1 in Microsoft Excel

1 thought on “Difference between Formula and FormulaR1C1 in Microsoft Excel”

Comments are closed.