FormulaArray property on a range

ElieAintabi

New Member
Joined
Jan 26, 2009
Messages
14
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?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

ElieAintabi

New Member
Joined
Jan 26, 2009
Messages
14
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.

Code:
    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

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Forum statistics

Threads
1,172,023
Messages
5,878,780
Members
433,370
Latest member
mcleven

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top