All geek questions in one place

When you enter formulas from VBA, different languages ​​appear

Do I understand correctly that if I use a command like

Set myRange.formula = "=ROW(mySheet!R12)"

will my macro cause #NAME? error #NAME? in cells, if it is running, say, in Russian Excel. I mean, in this case, the above formula must be hardcoded, for example

 Set myRange.formula = "=(mySheet!R12)"

where LINE is the Russian analogue of the SUM function. I would not expect Excel to be smart enough to translate formulas at run time. So, is there a way around this and, most importantly, what is the most common code for a macro to work correctly, regardless of language?

+5
source share
1 answer

VBA is very EN-US oriented. VBA Formula and . FormulaR1C1 expect ROW function . To use the regional aroma features , such as STRING , the Range.FormulaLocal property or the Range.FormulaR1C1Local value should be used instead.

The same applies to list separator characters. Use a comma (for example,) to separate arguments into functions when using .Formula or .FormulaR1C1, regardless of the regional settings of the system. If your system uses a semicolon as a list separator (for example,;), it should only be used with .FormulaLocal or .FormulaR1C1Local.

The result on the worksheet will correctly 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)"
+10
source

Source: https://habr.com/ru/post/1244219/

More articles:


All Articles