如何在VBA中将Dictionary对象保存至二进制文件？解决编译错误

错误原因 #

你遇到的编译错误是因为VBA的Put语句无法写入对象引用或包含对象引用的自定义类型。对象引用本质是内存中的指针，并非可持久化的二进制数据，直接写入文件后无法恢复为有效的对象实例。

解决方案（满足你的约束条件） #

使用晚期绑定的MSForms.PropertyBag对象序列化Dictionary内容，无需添加额外引用，也不需要将Dictionary转换为数组或文本格式，最终将序列化后的二进制内容写入文件，且文件仅包含Dictionary的相关数据。

保存Dictionary到二进制文件的代码 #

Public generalListOfAddIns As Object

Public Sub CreateGeneralListOfAddIns()
    Set generalListOfAddIns = CreateObject("Scripting.Dictionary")
    ' 示例：添加测试内容（可根据实际需求修改）
    generalListOfAddIns.Add "AddIn1", "C:\AddIns\AddIn1.xlam"
    generalListOfAddIns.Add "AddIn2", "C:\AddIns\AddIn2.xlam"
End Sub

Public Sub SaveDictAsBin()
    Dim filePath As String
    Dim fileNo As Integer
    Dim propertyBag As Object
    Dim key As Variant
    
    ' 晚期绑定创建PropertyBag对象
    Set propertyBag = CreateObject("MSForms.PropertyBag")
    
    ' 将Dictionary的键值对写入PropertyBag
    For Each key In generalListOfAddIns.Keys
        propertyBag.WriteProperty CStr(key), generalListOfAddIns(key)
    Next key
    
    filePath = "C:\TestFolder\ListOfAddIns.bin"
    fileNo = FreeFile
    
    ' 将PropertyBag的二进制内容写入文件
    Open filePath For Binary Lock Read Write As #fileNo
        Put #fileNo, , propertyBag.Contents
    Close #fileNo
    
    Set propertyBag = Nothing
End Sub

从二进制文件恢复Dictionary的代码 #

Public Sub LoadDictFromBin()
    Dim filePath As String
    Dim fileNo As Integer
    Dim propertyBag As Object
    Dim propertyNames() As String
    Dim i As Integer
    
    Set propertyBag = CreateObject("MSForms.PropertyBag")
    Set generalListOfAddIns = CreateObject("Scripting.Dictionary")
    
    filePath = "C:\TestFolder\ListOfAddIns.bin"
    fileNo = FreeFile
    
    ' 读取文件中的二进制内容到PropertyBag
    Open filePath For Binary Lock Read Write As #fileNo
        Dim binContent As String
        binContent = Space(LOF(fileNo))
        Get #fileNo, , binContent
        propertyBag.Contents = binContent
    Close #fileNo
    
    ' 从PropertyBag恢复键值对到Dictionary
    propertyNames = propertyBag.PropertyNames
    For i = LBound(propertyNames) To UBound(propertyNames)
        generalListOfAddIns.Add propertyNames(i), propertyBag.ReadProperty(propertyNames(i))
    Next i
    
    Set propertyBag = Nothing
End Sub

说明 #

• 此方法通过PropertyBag的二进制序列化能力，将Dictionary的键值对转换为可持久化的二进制数据，避免了直接操作对象引用的问题。
• 晚期绑定MSForms.PropertyBag无需在VBA项目中添加任何额外引用，符合你的约束要求。
• 文件仅包含序列化后的Dictionary数据，满足“二进制文件唯一内容”的需求。

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