克服255个字符. Excel VBA中FormulaArray的限制 [英] Overcoming the 255 char. limit for formulaArray in Excel VBA

问题描述

我需要将一个数组插入到特定的单元格中，并且继续遇到1004错误.这是代码:

I need an array to be inserted into a specific cell and I keep running into the 1004 error. Here is the code:

Range("o37").FormulaArray = "=CONCATENATE(SUM(IF(('2016 Summary'!$T$4:$T$39<=" & mon & ")*" & _
    "('2016 Summary'!$D$4:$D$39>0)*('2016 Summary'!$T$4:$T$39+'2016 Summary'!$D$4:$D$39>" & mon & ")*" & _
    "('2016 Summary'!$S$4:$S$39=TRUE),(1),IF(('2016 Summary'!$T$4:$T$39<=" & mon & ")*('2016 Summary'!$S$4:$S$39=TRUE)*" & _
    "('2016 Summary'!$D$4:$D$39=0),(1),0)))&"" - Employees"")"

该数组应该使用基于activecell列的"mon"变量插入到O37中.当我手动将公式放在工作表中时，该公式将起作用.我尝试用.replace拆分它，但出现了同样的错误.我该怎么做才能与FormulaArray一起使用?

That array is supposed to be inserted into O37 with the "mon" variable based on the activecell column. The formula works when I manually put it in the sheet. I have tried splitting it with .replace and I get the same error. What can I do to make this work with formulaArray?

推荐答案

取决于 mon 变量中的值有多长时间，看来您的数组公式仅略微超出〜的限制290-310个字符，其中很大一部分由外部工作表名称(例如"2016摘要")组成.临时将工作表名称更改为单个字符就足以使公式降至〜190-210个字符范围；足以将公式填充到单元格的 Range.FormulaArray属性.

Depending upon how long the value in the mon variable is, it seems that your array formula is only marginally over the limit at ~290-310 characters and a large portion of that is comprised of the external worksheet name (e.g. '2016 Summary'). Changing the worksheet name temporarily to a single character is sufficient to bring the formula down to the ~190-210 character range; more than adequate to stuff the formula into the cell's Range.FormulaArray property.

    Dim strWS As String, chrWS As String

    strWS = "2016 Summary"
    chrWS = Chr(167)    '<~~ any unque, legal, single character that can be temporarily used as a worksheet name

    With Worksheets(strWS)
        .Name = Chr(167)
    End With

    With Worksheets("worksheet_with_array_formula")
        .Range("o37").FormulaArray = "=CONCATENATE(SUM(IF((" & chrWS & "!$T$4:$T$39<=" & mon & ")*" & _
            "(" & chrWS & "!$D$4:$D$39>0)*(" & chrWS & "!$T$4:$T$39+" & chrWS & "!$D$4:$D$39>" & mon & ")*" & _
            "(" & chrWS & "!$S$4:$S$39=TRUE),(1),IF((" & chrWS & "!$T$4:$T$39<=" & mon & ")*(" & chrWS & "!$S$4:$S$39=TRUE)*" & _
            "(" & chrWS & "!$D$4:$D$39=0),(1),0)))&"" - Employees"")"
    End With

    With Worksheets(chrWS)
        .Name = strWS
    End With

Excel会在公式中更改工作表名称，并添加换行标记(例如'2016 Summary'!$T$4:$T$39)以补偿新(旧)工作表名称中的空间.

Excel will change the worksheet name within the formula, adding wrapping ticks (e.g. '2016 Summary'!$T$4:$T$39) to compensate for the space in the new (old) worksheet name.

如果您提供有关 mon 的值的更多详细信息以及"2016年总结"工作表中的一些示例数据，则该公式也可能会得到改进.乍一看，看起来数组公式可能根本不是绝对必要.

If you provided more detail on the value of mon and some sample data from the '2016 Summary' worksheet, it also possible that the formula could be improved. At first glance, it looks like an array formula may not be absolutely necessary at all.

这篇关于克服255个字符. Excel VBA中FormulaArray的限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！