VBA如何实现多次执行addCost子过程时持久存储数组用户值

问题背景 #

我正在尝试创建长度由用户设定的数组，支持用户向其中添加数值，数值保存后可输出到多个工作表。我是VBA初学者，不确定该需求是否可实现，此前尝试的多种方法均未成功。
数组长度对应项目生命周期，点击“add new cost”按钮会触发addCost宏，“clear costs”功能目前仍在开发中，核心需求为实现多次执行addCost子过程后，保留数组中存储的数据。

当前已编写代码 #

Sub addCost()
    Dim costArray() As Variant
    ReDim Preserve costArray(1 To Range("b2").Value)

    For i = 1 To Range("b2").Value
        costArray(i) = 0
    Next i

    Dim newCost As Variant
    Dim costYear As Variant

    newCost = InputBox("New cost amount:")
    costYear = InputBox("Year new cost is active:")

    costArray(costYear) = newCost
End Sub 

操作界面参考 #

Excel输入选项卡界面截图

问题原因 #

过程内部用Dim声明的局部变量，会在子过程执行完毕后自动释放内存；且现有代码每次运行都会重新执行ReDim语句将数组全量重置为0，之前录入的数值自然无法留存。

可落地实现方案 #

方案1：模块级变量存储（内存读写，速度快） #

将数组声明移到模块顶部作为模块级变量，增加初始化判断逻辑，避免每次运行都重置数组：

1. 打开VBA编辑器，在存放addCost宏的模块最顶部（所有Sub过程之外）声明变量：

' 模块级变量，本模块所有过程可访问，过程执行结束不会自动释放
Dim costArray() As Variant
Dim arrayInitialized As Boolean ' 标记数组是否完成初始化

2. 修改原有addCost过程代码，补充初始化判断和基础输入校验：

Sub addCost()
    Dim lifeCycleLength As Long
    Dim i As Long
    lifeCycleLength = CLng(Range("b2").Value)
    
    ' 校验生命周期输入合法性
    If lifeCycleLength <= 0 Then
        MsgBox "请先在B2单元格输入有效的项目生命周期年数"
        Exit Sub
    End If

    ' 仅在首次运行、或用户修改了生命周期长度时重定义数组
    If Not arrayInitialized Or UBound(costArray) <> lifeCycleLength Then
        ReDim costArray(1 To lifeCycleLength)
        ' 初始化所有年份成本为0
        For i = 1 To lifeCycleLength
            costArray(i) = 0
        Next i
        arrayInitialized = True
    End If

    Dim newCost As Double
    Dim costYear As Long
    ' 校验用户输入的成本、年份合法性
    On Error Resume Next
    newCost = CDbl(InputBox("New cost amount:"))
    If Err.Number <> 0 Then
        MsgBox "请输入有效的成本金额"
        Exit Sub
    End If
    costYear = CLng(InputBox("Year new cost is active:"))
    If Err.Number <> 0 Or costYear < 1 Or costYear > lifeCycleLength Then
        MsgBox "请输入1到" & lifeCycleLength & "范围内的有效年份"
        Exit Sub
    End If
    On Error GoTo 0

    ' 写入对应年份的成本值
    costArray(costYear) = newCost
End Sub

注意：该方案下，如果手动点击VBA编辑器的停止按钮、代码报错触发中断，模块级变量会被重置；且工作簿关闭后数据不会留存。如果需要持久化保存数据，使用方案2。

方案2：隐藏工作表存储（数据持久化，稳定性高） #

如果需要关闭工作簿后下次打开仍保留已录入的成本数据，不需要依赖数组存储，直接用隐藏工作表作为存储介质即可：

• 新建一个工作表，命名为CostStorage，将其Visible属性设置为xlSheetVeryHidden，普通用户操作时看不到该表
• 每次录入成本时，直接将数值写入该表对应年份的单元格，后续需要输出到多个工作表时，直接从该表读取数值即可，完全不需要担心过程运行结束、文件关闭导致的数据丢失
• 开发中的clear costs功能，只需要一键清空该存储表的对应单元格内容即可，逻辑简单不易出错

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