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?

 

[Norie]

Why are you switching the separator?

I know on a worksheet it will be ; but in VBA you should still use ,.

 

[rdilipk]

It doesn't seem to work that way. If the list separator happens to be ';' and I try to set FormulaArray with a formula that has ',' as separator, the assignment promptly fails with "Unable to set FormulaArray property of Range Object"

 

[pgc01]

It doesn't seem to work that way. If the list separator happens to be ';' and I try to set FormulaArray with a formula that has ',' as separator, the assignment promptly fails with "Unable to set FormulaArray property of Range Object"

Just to clarify

You have tested the first code you posted in a computer with the English settings and it worked ok?

 

[Norie]

Are you sure the reason the formula fails is because of the spearator?

 

[rdilipk]

Just to clarify

You have tested the first code you posted in a computer with the English settings and it worked ok?

Yes sir. Absolutely. It only fails in the second case where I replace all applicable ',' separators with ';'

 

[rdilipk]

Are you sure the reason the formula fails is because of the spearator?

The formula fails because the second Replace inside the With range block refuses to replace anything that's got a semi-colon in it. That renders my formula incomplete. The straight assignment to the FormulaArray property in the first line of my With block works just fine (even if it has a ';' as separator)

Basically it looks like Range.Replace does not like semicolons.

 

[shg]

Amenable to a UDF to insert the formula?

[FONT=Consolas][COLOR=#595959]Sub demo()[/COLOR][/FONT]
[COLOR=#595959][FONT=Consolas]    Const sFrm      As String = _[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]          "=IF(AND(RC[-12]:RC[-1]=""""),""No RoB assessments performed""," & vbLf & _[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]          "IF(OR(CHOOSE({1,2,3,4,5},RC[-10],RC[-8],RC[-6],RC[-4],RC[-2])=""no""),""HIGH""," & vbLf & _[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]          "IF(OR(CHOOSE({1,2,3,4,5},RC[-10],RC[-8],RC[-6],RC[-4],RC[-2])=""High Risk""),""HIGH""," & vbLf & _[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]          "IF(OR(CHOOSE({1,2,3,4,5},RC[-10],RC8],RC[-6],RC[-4],RC[-2])=""UNCLEAR""),""UNCLEAR""," & vbLf & _[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]          "IF(OR(CHOOSE({1,2,3,4,5},RC[-10],RC[-8],RC[-6],RC[-4],RC[-2])=""Unclear Risk""),""UNCLEAR"",""LOW"")))))"[/FONT][/COLOR]
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p>[FONT=Consolas][COLOR=#595959] [/COLOR][/FONT]</o:p>
[COLOR=#595959][FONT=Consolas]    Debug.Print InsertArrayFormula(Range("M1"), sFrm)[/FONT][/COLOR]
[FONT=Consolas][COLOR=#595959]End Sub[/COLOR][/FONT]
<o:p>[FONT=Consolas][COLOR=#595959] [/COLOR][/FONT]</o:p>
[FONT=Consolas][COLOR=#595959]Function InsertArrayFormula(r As Range, _[/COLOR][/FONT]
[COLOR=#595959][FONT=Consolas]                            sFrm As String, _[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]                            Optional ByVal sFmt As String = "") As Boolean[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]        ' shg 2009[/FONT][/COLOR]
<o:p>[FONT=Consolas][COLOR=#595959] [/COLOR][/FONT]</o:p>
[COLOR=#595959][FONT=Consolas]    ' Inserts the R1C1 array formula sFrm into r[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]    ' The VBE CANNOT have focus when this runs![/FONT][/COLOR]
[FONT=Consolas][COLOR=#595959]    [/COLOR][/FONT]
[COLOR=#595959][FONT=Consolas]    Dim iRef        As XlReferenceStyle[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]    Dim rSel        As Range[/FONT][/COLOR]
<o:p>[FONT=Consolas][COLOR=#595959] [/COLOR][/FONT]</o:p>
[COLOR=#595959][FONT=Consolas]    On Error GoTo Oops[/FONT][/COLOR]
<o:p>[FONT=Consolas][COLOR=#595959] [/COLOR][/FONT]</o:p>
[COLOR=#595959][FONT=Consolas]    With Application[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]        Set rSel = ActiveWindow.RangeSelection[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]        iRef = .ReferenceStyle[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]        .ReferenceStyle = xlR1C1[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]        .ScreenUpdating = False[/FONT][/COLOR]
<o:p>[FONT=Consolas][COLOR=#595959] [/COLOR][/FONT]</o:p>
[COLOR=#595959][FONT=Consolas]        With r.Areas(1)[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]            ' Can't put an array formula in cells that are[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]            ' not either all locked or all unlocked, so ...[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]            .Locked = .Cells(1).Locked[/FONT][/COLOR]
[FONT=Consolas][COLOR=#595959]            [/COLOR][/FONT]
[COLOR=#595959][FONT=Consolas]            ' Cache the number format, set to text, insert formula, restore format[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]            If Len(sFmt) = 0 Then sFmt = .NumberFormat[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]            .NumberFormat = "@"[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]            .Value = sFrm[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]            .NumberFormat = sFmt[/FONT][/COLOR]
[FONT=Consolas][COLOR=#595959]            [/COLOR][/FONT]
[COLOR=#595959][FONT=Consolas]            Application.Goto .Cells[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]        End With[/FONT][/COLOR]
<o:p>[FONT=Consolas][COLOR=#595959] [/COLOR][/FONT]</o:p>
[COLOR=#595959][FONT=Consolas]        DoEvents[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]        .SendKeys "{F2}^+~"[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]        DoEvents[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]        .ReferenceStyle = iRef[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]        .Goto rSel[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]        .ScreenUpdating = True[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]    End With[/FONT][/COLOR]
<o:p>[FONT=Consolas][COLOR=#595959] [/COLOR][/FONT]</o:p>
[COLOR=#595959][FONT=Consolas]    InsertArrayFormula = True[/FONT][/COLOR]
[FONT=Consolas][COLOR=#595959]Oops:[/COLOR][/FONT]
[COLOR=#595959][FONT=Consolas]    Exit Function[/FONT][/COLOR]
[FONT=Consolas][COLOR=#595959]End Function[/COLOR][/FONT]

 

[rdilipk]

Amenable to a UDF to insert the formula?

SHG -- thanks for the effort. Is there a slightly less painful alternative?

 

[shg]

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