克服 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"")"

该数组应该插入到 O37 中，并带有基于 activecell 列的mon"变量.当我手动将其放入工作表时，该公式有效.我试过用 .replace 拆分它，但我得到了同样的错误.我该怎么做才能使这个公式数组起作用?

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屋！