vba - 超过 255 个字符的数组公式

Question

这个问题已经有答案了:

How to automatically input an array formula as string with more than 255 characters in length into an excel cell using VBA? (1 个回答)

已关闭 2 年前。

我在运行 VBA 代码时遇到错误。

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

我认为这是因为我的字符数超过 255 个。

如果是这种情况，有人知道我可以使用的解决方法吗？

我的代码是下面一团糟:

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

如果我将“.FormulaArray”替换为“.Formula”，然后手动将其设为数组(Ctrl+Shift+Enter)，则它可以正常工作，因此公式本身也可以正常工作。

不幸的是，我不能让它变得更简单，因为我可以有多达 10 个帐户，每个帐户都需要在每个单元格中引用(我当前用于测试的三个帐户有 525 个字符，但它会根据情况而变化)无论每个帐户的名称是什么)。

正如我所说，Excel 似乎对此没有问题...问题在于 VBA。

非常感谢

Answer 1

我发现您的公式中有一些乘法。您可以将公式拆分为多个命名范围，然后使用另一个范围将它们组合回来。例如，假设您的公式可以分为两部分，如下所示:

= formulaPart1 * formulaPart2

然后您可以通过以下方式定义两个命名范围:

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

然后您可以将最终结果设置为:

= firstPart * secondPart

编辑:您甚至可以为每个要求和的元素定义一个命名范围。例如，这将是一个要设置的命名范围，假设您将其命名为 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)"

那么公式将类似于“=-SUM(sumElement1, sumElement2,..., sumElementn)”。