Saving VBA Dictionary object in Excel

Question

As part of an Excel Workbook Template a Dictionary object (from the Scripting Runtime Library) is created and added to. Is it possible to save this in some way along with the Workbook such that it is available on starting up the Workbook, or should I just export the data to a worksheet and save it, then reload it in the next time?

Trim

you didnt tell us , a dictionnary of what kind of values ? strings? objects, dates? ... – Jodi

Answer 1

I reckon a worksheet is the best bet. You might like to use the very hidden option, which means the sheet can only be made visible by code.

For example:

 Worksheets("System").Visible = xlVeryHidden

Rhyne

I love the fact that they called it "xlVeryHidden" – Chrisse

@Alistair In my version of Excel, 2007, xlVeryHidden and xlSheetVeryHidden both equal 2. In support.microsoft.com/kb/213609#appliesto xlVeryHidden is suggested. – Rhyne

Answer 2

Why not save it to a file?

    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  

Niklaus