如何在Excel关闭后持久化存储Dictionary并重新加载？

我之前也遇到过一模一样的问题——用字典缓存慢查询结果，但Excel重启就丢数据，确实头疼。下面给你一套完整的实用方案，亲测靠谱：

1. 准备存储缓存的专用工作表 #

建议用一个隐藏工作表来存缓存数据，避免用户误编辑。先写个小代码确保这个表存在：

Sub EnsureCacheSheetExists()
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("UserCache")
    On Error GoTo 0
    
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        ws.Name = "UserCache"
        ' 设为深度隐藏，避免误操作
        ws.Visible = xlSheetVeryHidden
        ' 加个表头方便识别数据
        ws.Range("A1").Value = "UserKey"
        ws.Range("B1").Value = "UserName"
    End If
End Sub

2. 将字典数据写入工作表 #

写一个通用的保存方法，把你的userKeyToNameDict字典写入缓存表：

Sub SaveUserDictToSheet(ByVal dict As Dictionary)
    Dim ws As Worksheet
    Dim key As Variant
    Dim rowNum As Long
    
    ' 先确保缓存表存在
    EnsureCacheSheetExists()
    Set ws = ThisWorkbook.Worksheets("UserCache")
    
    ' 清空旧数据（保留表头）
    ws.Range("A2:B" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row).ClearContents
    
    ' 遍历字典写入数据
    rowNum = 2
    For Each key In dict.Keys
        ws.Cells(rowNum, "A").Value = key
        ws.Cells(rowNum, "B").Value = dict(key)
        rowNum = rowNum + 1
    Next key
End Sub

3. 从工作表加载字典到内存 #

写一个加载方法，在Excel启动时调用，重建你的缓存字典：

Function LoadUserDictFromSheet() As Dictionary
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rowNum As Long
    Dim newDict As New Dictionary
    
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("UserCache")
    On Error GoTo 0
    
    If Not ws Is Nothing Then
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        ' 从第2行开始读取（跳过表头）
        For rowNum = 2 To lastRow
            Dim userKey As String
            Dim userName As String
            userKey = ws.Cells(rowNum, "A").Value
            userName = ws.Cells(rowNum, "B").Value
            
            ' 跳过空行，避免无效数据
            If userKey <> "" And userName <> "" Then
                ' 确保键唯一（字典本身会处理，但保险起见）
                If Not newDict.Exists(userKey) Then
                    newDict.Add userKey, userName
                End If
            End If
        Next rowNum
    End If
    
    Set LoadUserDictFromSheet = newDict
End Function

4. 自动触发加载和保存 #

为了不用手动调用，把加载和保存绑定到工作簿事件：

1. 按下Alt + F11打开VBA编辑器
2. 在左侧工程窗口找到你的工作簿，双击ThisWorkbook
3. 粘贴以下代码：

Private Sub Workbook_Open()
    ' 启动时自动加载字典，假设你的全局字典变量名为userKeyToNameDict
    Set userKeyToNameDict = LoadUserDictFromSheet()
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' 关闭前自动保存字典，确保数据不丢失
    If Not userKeyToNameDict Is Nothing Then
        SaveUserDictToSheet userKeyToNameDict
    End If
End Sub

额外注意事项 #

• 记得在模块顶部声明全局字典变量：Public userKeyToNameDict As New Dictionary（需要先引用Microsoft Scripting Runtime；如果不想引用，用Late Binding：Public userKeyToNameDict As Object，创建时写Set userKeyToNameDict = CreateObject("Scripting.Dictionary")）
• 缓存表设为xlSheetVeryHidden后，只能通过VBA或者Excel选项里的取消隐藏来显示，安全性更高

这样一来，每次打开Excel都会自动加载之前的缓存数据，关闭时自动保存，完美解决重启丢失的问题！

内容的提问来源于stack exchange，提问作者VBA.starter