如何将Excel多单元格数组公式结果导入VBA数组？

问题背景 #

需要通过VBA按钮触发代码，将以下复杂数组公式的结果存入VBA数组：

=FILTER(OFFSET(INDIRECT(Results!O22);0;-3);MOD(SEQUENCE(1;COLUMNS(INDIRECT(Results!O22)));5)=0)

其中Results!O22的公式会动态生成目标范围（最终返回SumSheet_OffTrt!$BY$14:$CR$68），手动输入该FILTER公式能正常生成多单元格数组，但用VBA实现时多次失败：

• 使用Application.Evaluate赋值数组返回Error 2015/VALUE!
• 设置Range的FormulaArray/Value/Formula属性触发“应用程序定义或对象定义错误”
• 调整Excel设置后问题依旧
• 粘贴公式文本后需手动回车才生效
  （使用瑞典版Excel，分隔符为;，也尝试过,）

可行解决方案 #

方法1：临时单元格中转法 #

利用空白临时单元格承载数组公式，再读取结果存入数组，避开直接解析复杂公式的问题：

Sub GetFilterResultViaTempCell()
    Dim tempCell As Range
    Dim resultArr As Variant
    
    ' 选择无业务关联的临时单元格（示例用Results工作表的ZZ1）
    Set tempCell = ThisWorkbook.Worksheets("Results").Range("ZZ1")
    
    ' 瑞典版Excel用分号分隔参数，设置数组公式
    tempCell.FormulaArray = "=FILTER(OFFSET(INDIRECT(Results!O22);0;-3);MOD(SEQUENCE(1;COLUMNS(INDIRECT(Results!O22)));5)=0)"
    
    ' 获取公式返回的多区域结果到数组
    resultArr = tempCell.CurrentRegion.Value
    
    ' 清理临时单元格
    tempCell.ClearContents
    
    ' 后续可对resultArr进行业务处理，示例：打印数组内容
    Dim i As Long, j As Long
    For i = LBound(resultArr, 1) To UBound(resultArr, 1)
        For j = LBound(resultArr, 2) To UBound(resultArr, 2)
            Debug.Print resultArr(i, j)
        Next j
    Next i
End Sub

方法2：VBA直接实现公式逻辑 #

拆解原公式的筛选逻辑，用VBA代码直接计算结果，完全脱离Excel公式解析：

Sub CalculateFilterResultDirectly()
    Dim targetRange As Range
    Dim filteredCols As Collection
    Dim resultArr() As Variant
    Dim colIdx As Long, rowIdx As Long, outCol As Long
    
    ' 解析Results!O22生成的目标区域
    Set targetRange = ThisWorkbook.Worksheets("Results").Range("O22").Value
    
    ' 筛选符合条件的列：从目标区域左移3列开始，每5列取一列
    Set filteredCols = New Collection
    For colIdx = 1 To targetRange.Columns.Count
        If colIdx Mod 5 = 0 Then
            filteredCols.Add targetRange.Offset(0, -3).Columns(colIdx)
        End If
    Next colIdx
    
    ' 初始化结果数组
    ReDim resultArr(1 To targetRange.Rows.Count, 1 To filteredCols.Count)
    
    ' 填充数组数据
    For outCol = 1 To filteredCols.Count
        For rowIdx = 1 To targetRange.Rows.Count
            resultArr(rowIdx, outCol) = filteredCols(outCol).Cells(rowIdx).Value
        Next rowIdx
    Next outCol
    
    ' 示例：输出数组维度信息
    Debug.Print "结果数组：" & UBound(resultArr, 1) & "行，" & UBound(resultArr, 2) & "列"
End Sub

方法3：调整Evaluate调用方式 #

针对长公式解析失败的问题，改用工作表级别的Evaluate，并确保分隔符匹配瑞典版Excel：

Sub EvaluateFormulaProperly()
    Dim fullFormula As String
    Dim resultArr As Variant
    
    ' 拼接完整公式（瑞典版用分号分隔参数）
    fullFormula = "FILTER(OFFSET(INDIRECT(Results!O22);0;-3);MOD(SEQUENCE(1;COLUMNS(INDIRECT(Results!O22)));5)=0)"
    
    ' 使用工作表Evaluate而非Application.Evaluate，减少环境差异
    resultArr = ThisWorkbook.Worksheets("Results").Evaluate(fullFormula)
    
    ' 检查结果有效性
    If Not IsError(resultArr) Then
        Debug.Print "数组获取成功"
    Else
        Debug.Print "获取失败：" & resultArr
    End If
End Sub

注意事项 #

• 瑞典版Excel设置数组公式必须用FormulaArray属性，参数分隔符保持;，不要替换为,
• 确保所有命名区域（如rng_Data_start_ALL、rng_AddRows_ALL）在VBA中可正常访问，避免作用域限制
• 临时单元格需选择绝对空白的位置，防止覆盖业务数据

内容的提问来源于stack exchange，提问作者geekeel