能否通过Excel单元格内公式访问VBA数组以保留撤销栈？

完全可以通过自定义Excel函数（UDF）实现从VBA数组提取值的需求，以此规避VBA直接修改单元格导致撤销栈清空的问题。核心逻辑是：让VBA仅在内存中维护目标数组，由单元格通过公式主动读取数组值，全程不直接编辑单元格内容。

实现步骤 #

1. 声明全局可访问的VBA数组

在标准模块中声明一个全局数组，确保自定义函数能读取到它：

' 标准模块（如Module1）中声明全局数组
Public myResultArray As Variant

2. 编写自定义取值函数

同样在标准模块中，实现一个UDF来处理数组取值逻辑，包含错误判断：

Function GetArrayValue(index As Integer) As Variant
    ' 检查数组是否已初始化
    If IsEmpty(myResultArray) Then
        GetArrayValue = "#未初始化"
        Exit Function
    End If
    
    ' 处理Excel公式常用的1-based索引与VBA 0-based数组的转换
    Dim arrIndex As Integer
    arrIndex = index - 1
    
    ' 检查索引是否在有效范围内
    If arrIndex < 0 Or arrIndex > UBound(myResultArray) Then
        GetArrayValue = "#索引越界"
        Exit Function
    End If
    
    ' 返回目标值
    GetArrayValue = myResultArray(arrIndex)
End Function

3. 修改触发事件逻辑

在工作表模块中，原有的Worksheet_Change事件不再直接修改单元格，而是更新全局数组：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 定义需要监控的n×m区域，根据实际情况修改
    Dim monitorRange As Range
    Set monitorRange = Me.Range("A1:C10")
    
    If Not Intersect(Target, monitorRange) Is Nothing Then
        Application.EnableEvents = False ' 防止循环触发
        
        ' 这里替换成你的业务逻辑：根据监控区域生成目标数组
        Dim rowCount As Integer
        rowCount = monitorRange.Rows.Count
        ReDim myResultArray(0 To rowCount - 1) As String ' 0-based一维数组
        
        ' 示例逻辑：将监控区域每行的第一个单元格值存入数组
        Dim i As Integer
        For i = 1 To rowCount
            myResultArray(i - 1) = monitorRange.Cells(i, 1).Text
        Next i
        
        Application.EnableEvents = True
    End If
End Sub

4. 在单元格中使用公式

将自定义函数作为公式填入需要展示结果的n×1区域，比如要读取数组第3个值，单元格公式写：

=GetArrayValue(3)

批量填充到目标区域即可，当监控区域变化时，数组更新后单元格会自动重新计算取值。

关键注意事项 #

• 自动重算设置：确保Excel的「自动重算」功能开启（默认开启），否则数组更新后单元格不会自动刷新。
• 数组生命周期：全局数组会在Excel重启、模块重新编译时清空，可在Workbook_Open事件中添加初始化逻辑避免异常。
• 索引一致性：函数中已处理1-based到0-based的转换，若你的数组是1-based，需调整代码中的索引计算。

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