What is FormulaR1C1 Property in Excel VBA?
In R1C1, “R” refers to row, while “C” refers to column. Formula R1C1 allows you set or retrieve formula for a cell, using relative row and column.
Why do you need FormulaR1C1?
For example, you want to set a formula in C2 to sum A2 and B2, you can type
Range("C2").formula= "=A2+B2" or
Range("C2").Value = "=A2+B2"
Instead of hard code “A2+B2”, you can also tell Excel to sum the two cells on the left of the formula cell using FormulaR1C1.
“A2+B2” works well until you insert a new column before A column, where the old A column becomes B, old B column becomes C ! Since you already hard code C2 = A2+B2, the formula will not change and the formula is messed up.
However, if your formula Cell C2 is dynamic, such as Named Range, after inserting a column the reference Range will become D2, and you can tell Excel to sum the two cells on the left, thats why FormulaR1C1 is useful.
Syntax
To retrieve a formula from a Range
variableName = Range.FormulaR1C1
To set a formula for a Range
Range.FormulaR1C1 = "=R[x]C[y]" or Range.Value = "=R[x]C[y]"
x and y are the coordinate relative to the formula Cell
where x is the number of rows right to formula Cell (negative value means left to the formula Cell)
where y is the column down from the formula Cell (negative value means up from the formula Cell)
You can omit the square brackets for x and y if the values are positive, which will cause the Cell to turn into absolute values with $
Note that when you set a formula, the result of Range.FormulaR1c1 and Range.Value are the same, the only difference is when you retrieve the value from the Range
| VBA code | Return |
| Msgbox(Range(“C2”).value) | numeric value of A2+B2 |
| Msgbox(Range(“C2”).formulaR1C1) | =RC[-2]+RC[-1] |
Example of using FormulaR1C1 to set formula
In worksheet, by default, Excel convert your formula to R1C1 but you cannot see it unless you enable “R1C1 Reference Style”. Therefore, type the below in worksheet is equivalent to adding R1C1 style.
In VBA
| Formula | Return | Explanation |
| Range(“C2”).FormulaR1C1 = “=RC[-2]” | 1 | Refer to the cell 2 columns left |
| Range(“C2”).FormulaR1C1 = “=R[1]C[-2]” | 2 | Refer to the cell 1 row down and 2 columns left |
Recommended Readings
Excel worksheet R1C1 reference style
Outbound References
http://msdn.microsoft.com/en-us/library/office/ff823188%28v=office.15%29.aspx
