Array formula with more than 255 characters [duplicate]

Question

I'm getting an error when running my VBA code.

Run-time error '1004': Unable to set the FormulaArray property of the Range class

I assume this is because I have more than 255 characters.

If this is the case, does anyone know of a workaround I can use?

My code is the beautiful mess below:

formula_string = "=-SUM("
account_counter = 1
Do Until Range("tblAccounts[[#Headers],[Accounts]]").Offset(account_counter, 0).Value = ""
    account_name = Range("tblAccounts[[#Headers],[Accounts]]").Offset(account_counter, 0).Value
    formula_string = formula_string & "IF(IFERROR(" & account_name & "[Category]=[@Categories],FALSE)*(" & account_name _
        & "[Transaction date]>=Budget!C$1)*(" & account_name & "[Transaction date]<=EOMONTH(Budget!C$1,0))," & account_name _
        & "[Outflow],0),"
    account_counter = account_counter + 1
Loop
formula_string = Left(formula_string, Len(formula_string) - 1) & ")"

    Do Until Range("tblBudget[[#Headers],[Ignore?]]").Offset(category_counter, 0).Value = ""
        If Range("tblBudget[[#Headers],[Ignore?]]").Offset(category_counter, 0).Value = "No" Then
            Do Until Range("tblBudget[[#Headers],[Ignore?]]").Offset(0, column_counter).Value = ""
                If Right(Range("tblBudget[[#Headers],[Ignore?]]").Offset(0, column_counter).Value, 8) = "Outflows" Then
                    Range("tblBudget[[#Headers],[Ignore?]]").Offset(category_counter, column_counter).Select
                    Selection.Formula = formula_string
                End If
                column_counter = column_counter + 1
            Loop
        End If
        category_counter = category_counter + 1
    Loop

If I replace ".FormulaArray" with ".Formula" and then manually make it an array (Ctrl+Shift+Enter) it works fine so the formula itself works fine.

Unfortunately I can't make it much simpler as I could have as many as 10 accounts that each need to be referenced within each cell (the current three I'm using for testing has 525 characters but it'll change depending on whatever the name of each account is).

As I say, it seems Excel has no problem with this...it's VBA that has the issue.

Many thanks

Answer 1

I see that you have some multiplications in your formula. You could split the formula into multiple named ranges and then use another range to combine them back. For example, lets say that your formula can be broken into two parts like this:

= formulaPart1 * formulaPart2

You can then define two named ranges via:

ActiveWorkbook.Names.Add Name:="firstPart" RefersToR1C1:="formulaPart1"
ActiveWorkbook.Names.Add Name:="secondPart" RefersToR1C1:="formulaPart2"

And then you can set your final result as:

= firstPart * secondPart

Edit: You could even define a named range for each of the elements you wish to sum. So for example this would be one named range to set, lets say you named it sumElement1:

"IF(IFERROR(" & account_name & "[Category]=[@Categories],FALSE)*(" & account_name _
        & "[Transaction date]>=Budget!C$1)*(" & account_name & "[Transaction date]<=EOMONTH(Budget!C$1,0))," & account_name _
        & "[Outflow],0)"

Then the formula would be something like "=-SUM(sumElement1, sumElement2,..., sumElementn)".