VBA 1004 错误 - 无法设置 Range 类的 FormulaArray 属性

Question

我正在使用 VBA 将以下 INDEX MATCH 公式插入到一组单元格中。

        cell2.FormulaArray = _
"=INDEX('[08 Debt Comparison & Provision Report.xlsx]Details by Bus Area &  Location'!AK:AK, _ 
    MATCH(1,('[08 Debt Comparison & Provision Report.xlsx]Details by Bus Area &  Location'!$A:$A = A " & value & ")* _
('[08 Debt Comparison & Provision Report.xlsx]Details by Bus Area &  Location'!$B:$B=""Total""),0))*1000"

运行时，我遇到错误 1004 -“无法设置 Range 类的 FormulaArray 属性”

我相当确定它与引用当前工作簿之外的工作簿有关，因为当我在同一个工作簿中进行索引匹配时它工作正常。

公式的精简版(为了阅读清晰)如下 -

=INDEX(Sheet2!AK:AK, MATCH(1,(Sheet2!A:A = A5)*(Sheet2!B:B="Total"),0))

然后在 vba -

cell2.FormulaArray = "=INDEX(Sheet2!C:C, MATCH(1,(Sheet2!A:A = A5)*(Sheet2!B:B=""Total""),0))"

Answer 1

如何进入FormulaArray超过 255 个字符，使用 VBA

在这种情况下，似乎有一个替代标准公式符合原始 FormulaArray 的要求。 .但是，可能存在没有替代公式的情况。
对于这些情况，我有以下方法输入FormulaArray使用 VBA 超过 255 个字符。

大多数时候，FormulaArray超过 255 个字符是由于 references 的长度它包含(因为它们可能与长常量数组有关)、具有大名称的外部工作簿(如本例中)或具有大名称的工作表(也在本例中)。该方法包括用较短的字符串替换这些长字符串，但是为了 FormulaArray (替换后)被接受为 FormulaArray那些较短的字符串也需要表示有效的 references .

根据上述，至少可能存在三种情况，references 长。 :

长常量数组:在这些情况下使用 Defined Names如此处所述

https://support.office.com/en-za/article/Guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7

Workbooks有大名和

Worksheets大名鼎鼎的

对于情况 2 和 3，同样的方法适用:使用短 references指向一个临时的 Worksheet作为临时替代品。

将方法应用于本例:

原创 FormulaArray : 使用变量 sFmlArray持有公式

Dim sFmlArray As String
sFmlArray = "=INDEX('[08 Debt Comparison & Provision Report.xlsx]Details by Bus Area &  Location'!AK:AK," & _
    "MATCH(1,('[08 Debt Comparison & Provision Report.xlsx]Details by Bus Area &  Location'!$A:$A = A" & bVal & ") * " & _
    "('[08 Debt Comparison & Provision Report.xlsx]Details by Bus Area &  Location'!$B:$B=""Total""),0)) * 1000"

我建议使用变量来保存工作簿和工作表的名称，以避免不得不多次编写它们。

Dim sFmlRng as string
sFmlRng = "'[08 Debt Comparison & Provision Report.xlsx]Details by Bus Area &  Location'!"

替换 FormulaArray 中的工作簿和工作表的名称与相应的变量:

sFmlAry = "=INDEX(" & sFmlRng & "AK:AK," & _
    "MATCH(1,(" & sFmlRng & "$A:$A = A" & bVal & ") * " & _
    "(" & sFmlRng & "$B:$B=""Total""),0)) * 1000"

假设我们要输入这么长的 FormulaArray在 D7:D10 范围内，让我们将它分配给一个变量

Dim rFmlAry as Range
Set rFmlAry = ActiveSheet.Range("D7:D10")

使用下面的函数添加临时工作表。此功能还提供临时 reference用作 FormulaArray 中的替换

Function WshTmp_Add(rFmlAry As Range, sFmlRngTmp As String) As Worksheet
    sFmlRngTmp = "@Tmp"
    With rFmlAry.Worksheet.Parent
        On Error Resume Next
        .Worksheets(sFmlRngTmp).Delete
        On Error GoTo 0
        Set WshTmp_Add = .Worksheets.Add(Before:=.Worksheets(1))
    End With
    WshTmp_Add.Name = sFmlRngTmp
    WshTmp_Add.Tab.Color = 255
    sFmlRngTmp = "'" & sFmlRngTmp & "'!"
    Application.Goto rFmlAry
End Function

在 FormulaArray 中替换, 长引用与较短的引用并输入临时 FormulaArray在 rFmlAry范围

sFmlAryTmp = WorksheetFunction.Substitute(sFmlAry, sFmlRng, sFmlRngTmp)
rFmlAry.FormulaArray = sFmlAryTmp

与 FormulaArray到位，更换临时短 references和原来的长的

rFmlAry.Replace What:=sFmlRngTmp, Replacement:=sFmlRng, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False

最后删除临时 Worksheet
这是整个过程(作为测试，最后添加了几行以验证结果)

Sub FormulaArray_Over255Chr()
Dim rFmlAry As Range, sFmlAry As String, bVal As Byte

Dim WshTmp As Worksheet, sFmlAryTmp As String
Dim sFmlRng As String, sFmlRngTmp As String
Dim blAppDisplayAlerts As Boolean
    blAppDisplayAlerts = Application.DisplayAlerts

    Rem Set Ranges & Values
    bVal = 5
    Set rFmlAry = ActiveSheet.Range("D2:D5")

    Rem Define External Reference Variable
    sFmlRng = "'[08 Debt Comparison & Provision Report.xlsx]Details by Bus Area &  Location'!"

    Rem Define FormulaArray (Original) - with References as Variables
    sFmlAry = "=INDEX(" & sFmlRng & "AK:AK," & _
        "MATCH(1,(" & sFmlRng & "$A:$A = A" & bVal & ") * " & _
        "(" & sFmlRng & "$B:$B=""Total""),0)) * 1000"

    Rem Set Range to Enter FormulaArray
    Set rFmlAry = ActiveSheet.Range("D7:D10")

    Rem Add Temporary Worksheet
    Application.DisplayAlerts = False
    Set WshTmp = WshTmp_Add(rFmlAry, sFmlRngTmp)

    Rem Set Temporary FormulaArray - Replace long references
    sFmlAryTmp = WorksheetFunction.Substitute(sFmlAry, sFmlRng, sFmlRngTmp)

    Rem Enter Temporary FormulaArray
    rFmlAry.FormulaArray = sFmlAryTmp

    Rem Set FormulaArray (Original) - Replace short references in situ
    rFmlAry.Replace What:=sFmlRngTmp, Replacement:=sFmlRng, _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False

    Rem Delete Temporary Worksheet
    WshTmp.Delete
    Application.DisplayAlerts = blAppDisplayAlerts

' ****************************************************************
' Lines for TESTING - Resulting FormulaArray - REMOVED when final
' ****************************************************************
    Rem Validate FormulaArray
    Debug.Print String(3, vbLf)
    Debug.Print "FormulaArray in Range: "
    Debug.Print rFmlAry.Cells(1).FormulaArray
    Debug.Print "FormulaArray VBA: "
    Debug.Print sFmlAry
    If rFmlAry.Cells(1).FormulaArray = sFmlAry Then
        MsgBox "FormulaArray with +255 entered successfully" & vbLf & _
            vbLf & rFmlAry.Cells(1).FormulaArray
    Else
        MsgBox "Something did not worked!" & vbLf & _
            vbLf & "Review formulas printed in the Immediate Window"
            SendKeys "^g": Stop
    End If
' ****************************************************************

End Sub