如何在VBA中程序化获取Excel溢出区域的值？

核心原因 #

Excel在自定义函数执行阶段，当前溢出区域的Value/Value2属性处于计算锁定状态，直接读取会返回EMPTY——这是因为函数还在运行，溢出结果尚未完全写入单元格，常规属性无法获取到实时值。

可行解决方案 #

1. 强制完成异步计算后读取 #

利用Application.CalculateUntilAsyncQueriesDone强制完成所有异步计算，确保溢出结果已写入单元格后再读取：

Function TestFunction(n As Long, bIsVertical As Boolean) As Variant
    Dim shortCircuitRange As Range
    Dim vals As Variant
    
    On Error Resume Next
    Set shortCircuitRange = ThisWorkbook.Names("SHORT_CIRCUIT").RefersToRange
    On Error GoTo 0
    
    If shortCircuitRange Is Nothing Or Not shortCircuitRange.Value Then
        ' 生成动态区域逻辑
        Dim resultArr() As Variant
        If bIsVertical Then
            ReDim resultArr(1 To n, 1 To 1)
        Else
            ReDim resultArr(1 To 1, 1 To n)
        End If
        Dim i As Long
        For i = 1 To n
            If bIsVertical Then
                resultArr(i, 1) = i
            Else
                resultArr(1, i) = i
            End If
        Next i
        TestFunction = resultArr
    Else
        ' 强制完成计算后读取溢出值
        Application.CalculateUntilAsyncQueriesDone
        vals = Application.Caller.SpillingParent.Value
        TestFunction = vals
    End If
End Function

2. 用工作表Evaluate方法间接读取 #

通过Worksheet.Evaluate绕开直接读取的限制，间接获取溢出区域的值：

Else
    Dim spillAddr As String
    spillAddr = Application.Caller.SpillingParent.Address
    vals = Application.Caller.Worksheet.Evaluate(spillAddr)
    TestFunction = vals
End If

3. 提前缓存溢出值到命名区域 #

如果前两种方法无效，可在生成溢出结果时同步缓存到命名区域，读取时直接调用：

• 生成结果时添加缓存逻辑：

' 生成动态区域逻辑后添加
ThisWorkbook.Names.Add Name:="TEMP_SPILL_CACHE", RefersTo:=Application.Caller.SpillingParent

• 读取时调用缓存：

Else
    On Error Resume Next
    vals = ThisWorkbook.Names("TEMP_SPILL_CACHE").RefersToRange.Value
    On Error GoTo 0
    TestFunction = vals
End If

注意事项 #

• 禁止在自定义函数中使用Application.Calculate，会触发循环计算报错；
• SpillingParent仅支持Excel 365及以上版本，需兼容旧版时要额外做版本判断；
• 调试时属性窗口能看到值，是因为调试器暂停了计算流程，此时结果已写入，但代码执行阶段仍处于未完成状态。

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