Different languages issue when inserting formula from VBA

vba excel regional-settings
Edgar Navasardyan picture Edgar Navasardyan · Mar 1, 2016 · Viewed 9.2k times · Source

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 ?

Answer

user4039065 picture user4039065 · Mar 1, 2016

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)"

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation? In-cell function to return a matched pattern or replaced value in a string. Sub to loop through a column of data …

Read More