FormulaArray property on a range

[ElieAintabi]

I'm trying to assign (in excel 2003, sp3) a string to the FormulaArray property of a range. The length of the string is 305, causing a crash because it is maxed over 255 characters (the error and cause is documentated poorly at http://support.microsoft.com/kb/213181, I say poorly because at the top of the article it states

This problem occurs when you try to pass a formula that contains more than 255 characters, and you are using the FormulaArray property in Visual Basic for Applications.

with no solution.

So I ask, does anyone here have any ideas as to a solution?

 

[Colin Legg]

Yes there is a workaround using Range object's replace method

See here:
http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

HTH,
Colin

 

[ElieAintabi]

Thanks very much, going to adopt this method immediately. Appreciated.

 

[Colin Legg]

Glad to be of help.

 

[ElieAintabi]

Glad to be of help.

Not really working out so well here, my formula is dynamically created, I don't know how many parts it will consist of. Code below, any guidance appreciated.

    Dim denominatorFormula As String
    denominatorFormula = "=ABS(" & Chr(34) & NodeRange(childNodeIndexes(1)).Address(ReferenceStyle:=xlR1C1) & Chr(34)
    Dim x As Integer
    For x = 2 To UBound(childNodeIndexes)
        denominatorFormula = denominatorFormula & ")+ABS(" & Chr(34) & NodeRange(childNodeIndexes(x)).Address(ReferenceStyle:=xlR1C1) & Chr(34)
    Next
    denominatorFormula = denominatorFormula & ")"
        If (Len(denominatorFormula) > 255) Then
        Dim denomForm() As String
        ReDim denomForm((Len(denominatorFormula) / 150) + 1) As String
        SplitString denominatorFormula, denomForm
    End If
    ' Assign array formula to DENOMINATOR range.
    Range("DENOMINATOR").Clear
    Dim q As Integer
    Dim xxx As String
    For q = 1 To UBound(denomForm())
        xxx = Range("DENOMINATOR").FormulaArray
        Range("DENOMINATOR").FormulaArray = xxx & denomForm(q)
    Next

End Sub

 

[Colin Legg]

I think we can help you with this....

Can you give us more of an idea about how these variables are declared and what values they hold? Also please show the code for the SplitString function?

And can you give us a couple of corresponding array formulas, showing how you want the formulas to look in the worksheet itself? This should make it easier for us to piece it together...

Colin