Преодоление 255 чар. предел для FormulaArray в Excel VBA

Мне нужно, чтобы массив был вставлен в определенную ячейку, и я продолжаю сталкиваться с ошибкой 1004. Вот код:

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 с переменной "mon" на основе столбца activecell. Формула работает, когда я вручную помещаю ее на лист. Я попытался разделить его с помощью .replace и получил ту же ошибку. Что я могу сделать, чтобы это работало с FormulaArray?


excel array-formulas vba
person fundesign    schedule 26.01.2016    source источник

Ответы (1)


arrow_upward
2
arrow_downward

В зависимости от того, насколько длинно значение в переменной mon, кажется, что ваша формула массива лишь незначительно превышает предел в ~ 290-310 символов, и большая часть этого состоит из имени внешнего рабочего листа. (например, «Резюме за 2016 г.»). Временного изменения имени рабочего листа на один символ достаточно, чтобы сократить формулу до диапазона ~ 190-210 символов; более чем достаточно, чтобы вставить формулу в Range.FormulaArray свойство ячейки.

    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), чтобы компенсировать пробел в новом (старом) имени рабочего листа.

Если вы предоставите более подробную информацию о значении mon и некоторые примеры данных из рабочего листа "Сводка за 2016 год", также возможно, что формулу можно будет улучшить. На первый взгляд может показаться, что формула массива вообще не нужна.

person Community    schedule 27.01.2016
comment
Это работает! Большое спасибо за ваш ответ. Mon — это только номер столбца из activecell.column. - person fundesign; 27.01.2016
comment
По общему признанию, это немного "хак" или "пластырь" подход. Однако, поскольку расширение символов формулы очень часто может быть связано с необходимым добавлением длинных имен рабочих листов, его следует рассматривать как альтернативу определенным именам или «партнерским формулам», содержащим части формулы полного массива. - person ; 27.01.2016