0

In VBA for Excel 2017 on my iMac I have the following problem:

Sub UpdateSummary()
    Total = "=QUOTIENT(120;60)"
    Sheets("Summary").Range("H15").Formula = Total
End Sub

This gives run time error 1004

However if I do:

Sub UpdateSummary()
    Total = "QUOTIENT(120;60)"
    Sheets("Summary").Range("H15").Formula = Total
End Sub

and add the = sign manually in the cell H15, the formula works as expected.

What can I do to solve this problem?

PS: The original code is more complex than above obviously, but this code snippet is generating the error and probably easier to understand than the original code.

MPL
  • 384
  • 1
  • 4
  • 20

1 Answers1

4
Total = "=QUOTIENT(120;60)"

should be

Total = "=QUOTIENT(120,60)"

when adding formula via VBA you should always use the comma list separator (unless you use FormulaLocal (where you can use your local list separator)

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • This is a very confusing feature of Microsoft Windows/Microsoft Office/Excel VBA regarding regional settings. I had exactly the same problem until I discovered this answer.The point is that in Windows regional settings you can set the regional list separator as, i.e. "," or ";". I have set semicolon (;), if I write manually in excel cell using comma (,) it gives me an error. However, if I fill the cell from VBA I have to use comma, as semicolon gives me run time error 1004. Illogical behavior!!! – Guille Aug 03 '23 at 17:13
  • It's not illogical - VBA purposely has both `Formula` and `FormulaLocal` properties. The advantage of the way it's set up is you can write a formula to a cell using the US-style separator and it will *automatically* translate the formula to the user's locale settings (if those are different). So you can write one set of VBA which works across locales. If you really want to use a specific locale in your VBA then you can do that using `FormulaLocal`. That might not be super-intuitive, but it's well-documented and useful. – Tim Williams Aug 03 '23 at 17:40
  • OK, make sense but as you said, not intuitive at all! – Guille Aug 03 '23 at 17:57