VBA – Formulas in Excel LocalFormula

The formulas in Excel, used through VBA are not easy. You need to write a string like "=IFERROR(OR((1=A1),(2=A2)),""No Error"",""Error"")" and this is really not easy and fun to debug. Furthermore, if it is false, VBA will nicely throw a runtime error 1004 and you would start questioning yourself why have you started to program with VBA and you did not continue working as a translator from Greek to Bulgarian (for example).

And while translating to Greek can be sometimes fun, VBA programming is one step funnier. So, when you are using the formulas in Excel in VBA, you should be aware of the following 3 points:

• • Each language, which supports Excel has a different formula separator – for German it is “;“, for English it is “,“.
  • When you put inverted commas in a formula, you should always escape them with one more inverted commas. Thus, to write test"" you should Debug.Print "test""""". 5 times!
  • There are 4 types of formulas in Excel:
    • .Formula
    • .FormulaR1C1
    • .FormulaLocal
    • .FormulaR1C1Local

What all these do? Two of them show the formula in the Local language and the other two show it in the R1C1 format.

What is the local language?

If you have ever opened a German Excel, you would have somehow noticed, that the =IF() formula does not exist. There is something called =WENN(). And the same is with about 99% of the formulas. Fortunately, Excel is wise enough that if you send a German Excel with German formulas to an English user, the English user will open the Excel and will see the formulas in his own language. However, VBA generated formulas are not that kind. Thus, if you use .Formula = "=WENN(A1=1;""WAHR"";""FALSCH"") then you will see an error.

If you want to use the language specific formulas, you should use .FormulaLocal = "=WENN(A1=1;""WAHR"";""FALSCH"") as .Formula works only with the English formulas.

What is R1C1 Format?

Excel supports two types of referring to a cell range. The standard with a combination of the alpha column and the row number (A1, B2, C3, etc) and another one, called R1C1. There you give a reference to the row and the column. Thus, A1 is R1C1, B2 is R2C2, C3 is R3C3. So, .FormulaR1C1 = "=IF(R1C1 = 1,""WAHR"",""FALSCH"") would be a valid syntax.

How can you make your life easier?

In general, this is a simple algorithm to follow, which would always help you with the Excel formulas:

1. Write the formula, so it works in Excel;
2. Select it manually;
3. Run the code below;
4. It prints the formula as it should look like in the immediate window;
5. Copy it & use it;

Based on which type of formula you have selected, you would see the corresponding result:

• • .Formula
    • "=IF(B1=C1,""Equal"",""Not equal"")"
  • .FormulaR1C1
    • "=IF(RC[1]=RC[2],""Equal"",""Not equal"")"
  • .FormulaLocal
    • "=WENN(B1=C1,""Equal"",""Not equal"")"
  • .FormulaR1c1Local
    •  "=WENN(ZS(1)=ZS(2),""Equal"",""Not equal"")"

Once you start using it, you will never go back to writing formulas and contactenating strings manually!

Enjoy it! 🙂