数组公式超过255个字符 [英] Array formula with more than 255 characters

问题描述

运行时错误'1004'：
无法使用设置Range类的FormulaArray属性

我假设这是因为我有超过255个字符。

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

我的代码是美丽的混乱： p>

  formula_string == -SUM（
 account_counter = 1 
 Do Until Range（tblAccounts [[＃ Header]，[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日期]> =预算！C $ 1）*（& account_name&[交易日期]< = 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 =NoThen 
 Do Until Range（tblBudget [[＃Headers]，[Ignore？]] ）.Offset（0，column_counter）.Value =
如果右（Range（tblBudget [[＃Headers]，[Ignore？]]）。Offset（0，column_counter）.Value，8）= Outflows然后
 Range（tblBudget [[Headers]，[Ignore？]]）。Offset（category_counter，column_counter）.Select 
 Selection.Formula = formula_string 
 End If 
 column_counter = column_counter + 1 
循环
结束如果
 category_counter = category_counter + 1 
循环
  

如果我用.Formula替换.FormulaArray，然后手动使其成为一个数组（Ctrl + Shift + Enter），它工作正常，因此公式本身工作正常。 p>

不幸的是，我不能让它更简单，因为我可以有多达10个帐户，每个需要在每个单元格中引用（目前我用于测试的三个字符有525个字符，但它会更改取决于每个帐户的名称）。

正如我所说，似乎Excel没有问题，这是VBA有问题。 / p>

非常感谢

解决方案

我看到你有一些乘法式。您可以将公式拆分成多个命名范围，然后使用另一个范围将它们组合起来。例如，让我们说你的公式可以分成两部分：

  = formulaPart1 * formulaPart2 
  

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

  ActiveWorkbook.Names.Add名称：=firstPartRefersToR1C1 = =formulaPart1
 ActiveWorkbook.Names.Add名称：=secondPartRefersToR1C1 = =formulaPart2
  

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

  = firstPart * secondPart 
  

编辑：您甚至可以定义一个命名范围对于您希望总结的每个元素。所以例如这将是一个命名范围设置，让我们说你命名它sumElement1：

 IF（IFERROR（& ; account_name&[Category] ​​= [@ Categories]，FALSE）*（& account_name _ 
&[Transaction date]> = Budget！C $ 1）*（& account_name& [交易日期]< = EOMONTH（Budget！C $ 1,0）），& account_name _ 
&[Outflow]，0）
  / pre> 
 
 
然后公式将是= -SUM（sumElement1，sumElement2，...，sumElementn）。
 
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
解决方案
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)".

                        
这篇关于数组公式超过255个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！