Formulas In Excel VBA

This section explains the differences and similarities among the cell properties Formula, FormulaLocal, FormulaR1C1, and FormulaR1C1Local using an example with various assignments:

Sub FormulaVersions()
    ThisWorkbook.Worksheets("Sheet1").Activate
    Range("A13").Formula = "=A10 + A11 + A12"
    Range("A14").Formula = "=SUM(A10:A12)"
    Range("A15").FormulaLocal = "=A10 + A11 + A12"
    Range("A16").FormulaLocal = "=SUM(A10:A12)"
    Range("A17").FormulaR1C1 = "=R[-7]C + R[-6]C + R[-5]C"
    Range("A18").FormulaR1C1 = "=SUM(R[-8]C:R[-6]C)"
    Range("A19").FormulaR1C1Local = "=R[-9]C + R[-8]C + R[-7]C"
    Range("A20").FormulaR1C1Local = "=SUM(R[-10]C:R[-8]C)"
End Sub

Explanation:
Each line calculates the sum of the numbers in the three cells from A10 to A12, but using different ways to assign the formula.

• The Formula property expects the formula to be assigned in English notation. Inside the string, either a calculation expression using the plus operator (+) or the worksheet function SUM() can be used. The spaces inside the formula string are added only for readability and can be omitted without affecting functionality.
• The FormulaLocal property expects the formula to be assigned in the local language notation—in this case, German. The calculation expression with the plus operator (+) remains the same as in English. However, the worksheet function must be written as SUMME() instead of SUM().
• The FormulaR1C1 property expects the formula in English notation but uses relative cell references based on the R1C1 reference style. Here, R stands for « Row » and C for « Column ». Relative positions are specified inside square brackets. For example:
  • RC refers to the current cell.
  • R[-1]C refers to the cell one row above the current cell.
  • RC[-1] refers to the cell one column to the left.
  • R[-1]C[-1] refers to the cell diagonally up-left from the current cell.
• The FormulaR1C1Local property expects the formula in the local language notation and also uses relative cell references, but with a different syntax. Here, Z stands for « Zeile » (row) and S stands for « Spalte » (column). Relative positions are specified inside parentheses (round brackets), not square brackets. The examples above for R and C apply analogously to Z and S notation:
  • ZS is the current cell.
  • Z(-1)S is the cell one row above.
  • ZS(-1) is the cell one column to the left.
  • Z(-1)S(-1) is the cell diagonally up-left.