Range.FormulaArray localization issues

[rdilipk]

To get around the limitation that Range.FormulaArray cannot accept a formula more than 255 chars in length, I use this work around:

long_formula_part1 = "=ZZZ(""a"",""b"",""c"",""somekey=tick,tock,jog"",dummy"
long_formula_part2 = ",""e"",""f"",""g"")"
to_replace = ",dummy"
act_formula = "ABC"

Range r = ' some range in excel

With r
.formulaArray = long_formula_part1
.Replace to_replace, long_formula_part_2
'I do the following because I don't want the actual formula to fire until I am done replacing everything
'My actual formula is a User Defined function written in C++
.Replace "ZZZ", act_formula
End With

My problem happens when the regional settings is not English US. If I switch to Germany, it looks like the list separator is a ';' as opposed to ','. So lets say I do this instead:

long_formula_part1 = "=ZZZ(""a"";""b"";""c"";""somekey=tick,tock,jog"";dummy"
long_formula_part2 = ";""e"";""f"";""g"")"
to_replace = ";dummy"
act_formula = "ABC"

Range r = ' some range in excel

With r
.formulaArray = long_formula_part1
'NOTE: The following line promptly fails if the Replace function sees any *semi-colon* in either its
'first parameter or its second
.Replace to_replace, long_formula_part_2
.Replace "ZZZ", act_formula
End With

Is there a way out?

 

[rdilipk]

How painful is it to pass the entire array formula as a string to a function? How much simpler could it be?

Its not that. With my limited vba skills I find it a little difficult to follow that function. What is the 3rd parameter to it and how does it handle localized list separators?

 

[rdilipk]

How painful is it to pass the entire array formula as a string to a function? How much simpler could it be?

Another thing that is hard for me to arrange is your requirement that VBE cannot have focus when this function runs. All our VBA modules sit in a common .xla file and many developers simultaneously work on it with the VBE constantly in focus for debugging. Will this be a problem?

 

[shg]

The third argument is optional, and allows you to pass a format string if you want to set the cell formatting. Ignore it if you don't need it.

As to localization, I don't know; I've never used Excel configured for other than US settings.

The VBE can be open, it just can't have focus. If your developers can't manage that, then it would be a problem.

 

[Nuesa]

I recommend you use https://poeditor.com/ for localization. You can translate your strings easily and you can export them to an excel file also.