在 Excel 中保存 VBA 字典对象

Question

作为 Excel 工作簿模板的一部分，创建并添加一个 Dictionary 对象（来自脚本运行时库）。是否可以以某种方式将其与工作簿一起保存，以便在启动工作簿时可用，或者我应该将数据导出到工作表并保存，然后在下次重新加载？

Answer 1

我认为工作表是最好的选择。您可能想使用非常隐藏的选项，这意味着工作表只能通过代码可见。

例如：

 Worksheets("System").Visible = xlVeryHidden

Answer 2

为什么不将其保存到文件中？

    Sub Save_Dict(aDict As Scripting.Dictionary, FileitAs As String, Data_ID As String)  
    Dim one, SaveStr() As String, s As Long  
    ReDim SaveStr(aDict.Count)  
    SaveStr(0) = Data_ID  
    s = 0  
    For Each one In aDict  
          s = s + 1  
          SaveStr(s) = one & vbBack & aDict(one)  
     Next one  

     Write Join(SaveStr, vbCrLf)) to FileitAs 'Method of choice  
    End Sub  

'~~~~~~~~~~~~~~~~

    sub Get_Dict(aDict as Scripting.Dictionary, FiledAs as String, Data_ID as String) as Long  
    Dim one, SavedString, nLng as long, i as integer  
    Read SavedString from FiledAs - 'Method of choice  
    SavedString = split(SavedString, vbCrLf)  
    If Ubound(SavedString) =>0 then  
       Data_ID = SavedString(0)  
       For nLng = 1 to ubound(SavedString)  
         i = instr(SavedString(nLng),vbBack)  
         adict.add left(SavedString(nLng),i-1, Mid(SavedString(nLng),i+1)  
       next Nlng  
     End If  
    End Sub