13

do I understand correctly, that if I use a command like

Set myRange.formula = “=ROW(mySheet!R12)”

my macro will cause #NAME? error appear in cells if it is run on, say, Russian Excel. I mean that in this case the above formula should be hard-coded like

Set myRange.formula = “=СТРОКА(mySheet!R12)”

where СТРОКА is the Russian analogue of the SUM function. I wouldn't anticipate Excel to be smart enough to translate the formulas in run-time. So is there any way around this and, most importantly, what is the most generic code to make the macro work correctly irrespective of languange ?

newishuser
  • 610
  • 4
  • 24
Edgar Navasardyan
  • 4,261
  • 8
  • 58
  • 121

1 Answers1

22

VBA is very EN-US-centric. VBA's .Formula and .FormulaR1C1 expect the ROW function. To use regional language function 'flavors' like СТРОКА then the Range.FormulaLocal property or Range.FormulaR1C1Local property should be employed instead.

The same holds true for list separator characters. Use a comma (e.g. ,) to separate the arguments in a function when using .Formula or .FormulaR1C1 regardless of system regional settings. If your system uses a semi-colon (e.g. ;) as the list separator character, this should only be used with .FormulaLocal or .FormulaR1C1Local.

The result on the worksheet will properly reflect the language settings of the Office installation.

myRange.Formula = "=ROW(mySheet!$12:$12)"
myRange.FormulaR1C1 = "=ROW(mySheet!R12)"
myRange.FormulaLocal  = "=СТРОКА(mySheet!$12:$12)"
myRange.FormulaR1C1Local= "=СТРОКА(mySheet!R12)"
  • So, do I understand you correctly, that using .FormulaLocal will make my code absolutely viable and not dependent on the languange system ? – Edgar Navasardyan Mar 01 '16 at 13:48
  • Then why do we need the method .Formula whatsoever ?)) And why don't they just integrate the funcionality of .FormulaLocal into the method .Formula ?)) Strange enough – Edgar Navasardyan Mar 01 '16 at 13:49
  • Yes, it should. All that is necessary would be to make sure that you are using either [.FormulaLocal](https://msdn.microsoft.com/en-us/library/office/ff838851.aspx) or [.FormulaR1C1Local](https://msdn.microsoft.com/en-us/library/office/ff838568.aspx) appropriately. The `R12` reference in your sample was a little ambiguous; I hope I guessed correctly. Also, any quotes within the formula need to be doubled up as they are going to be within a quoted string. e.g. `="abc"` becomes `.FormulaLocal = "=""abc"""`. –  Mar 01 '16 at 13:52
  • Formula Local is a relatively new addition. The VBA command interpreter had to be rewritten to accept language specific functions. –  Mar 01 '16 at 13:53
  • ok. Just to confirm - Set myRange.FormulaLocal = "=ROW(mySheet!$12:$12)" will work on all systems. – Edgar Navasardyan Mar 01 '16 at 13:55
  • 3
    Not if your Office installation is RU-RU or anything but EN-US. However, `myRange.Formula = "=ROW(mySheet!$12:$12)"` **will** work on any system. (do not use `Set` to assign a formula) –  Mar 01 '16 at 14:18
  • 1
    Thank you, thank you, thank you. I have struggled a lot with not get it to work with `Formula`. I have totally missed that it should be `","` instead of `";"`. I usually look in VBA debugger for values, but not in a thousand years I could have imagine that Microsoft have this difference between `Formula` and `FormulaLocal`. I know they are strange, but this strange... – 244an Nov 29 '17 at 13:32