Save a scripting dictionary to disk with VBA

Can you be more specific about the data in the dictionary?  Are you just storing a simple text value with a text key value?  Or numbers?  Or getting fancy and storing an object?  This would make a big difference to how easy it would be.

This feels doable, but need a bit more context and info.

~bp

Assuming you are just working with String values for key and values, then see if this gives you some ideas.  The delimited text string could be stored in the document someplace, or written and read from a text file.

Sub Test()
    Dim objDict As Object
    Dim strText As String
    
    ' Create dictionary
    Set objDict = CreateObject("Scripting.Dictionary")
    
    ' Add first set of data to dictionary manualy
    objDict.Add "k1", "v1"
    objDict.Add "k2", "v2"
    objDict.Add "k3", "v3"
    
    ' "Serialize" dictionary to delimited text string, and display it
    strText = Dict2String(objDict)
    MsgBox strText
    
    ' Build a new set of data as delimited key/value pairs
    strText = "key1" & vbTab & "value1" & vbCrLf
    strText = strText & "key2" & vbTab & "value2" & vbCrLf
    strText = strText & "key3" & vbTab & "value3" & vbCrLf
    strText = strText & "key4" & vbTab & "value4" & vbCrLf
    strText = strText & "key5" & vbTab & "value5" & vbCrLf
    
    ' Replace contents of dictionary you data in delimited text string
    String2Dict objDict, strText
    
    ' Extract new values from dictionary to text string, and display it
    strText = Dict2String(objDict)
    MsgBox strText
    
End Sub

Function Dict2String(objDict As Object) As String

    Dim strKey As Variant
    Dim strExport As String

    ' For each dictionary item, add to text string using delimiters
    For Each strKey In objDict.Keys()
        strExport = strExport & strKey & vbTab & objDict(strKey) & vbCrLf
    Next

    ' Return serialized string
    Dict2String = strExport

End Function

Sub String2Dict(objDict As Object, strText As String)

    Dim arrPair() As String
    Dim arrField() As String
    
    ' Empty dictionary
    objDict.RemoveAll
    
    ' First split text string into each data item
    arrPair = Split(strText, vbCrLf)

    ' Process each items data
    For Each strPair In arrPair
        If strPair <> "" Then
            ' Second split key and value values apart
            arrField = Split(strPair, vbTab)
            ' Add this paot to dictionary
            objDict.Add arrField(0), arrField(1)
        End If
    Next

End Sub

Select allOpen in new window

~bp

Comments on Bill Prew’s code:
     This may well be the solution to my problem, but there are two trivial errors in the suggested code.
     In the subroutine String2Dict the variable strPair is not declared. Since I always use Option Explicit as an informal spell check, I had to declare strPair. Despite its name, it is a variant, so I suggest changing the name to varPair.
     The other error is even more trivial. In that String2Dict subroutine we find this comment:
     ‘ Add this paot to dictionary
     I have no idea what “paot” was supposed to be, but the program works (with the above change) so it is not important.

     Bill Prew asked for more details about my query. Here they are:
     My purpose in asking the question is an application I’m writing that tests a user’s knowledge of French. It has a series of, say, 200 questions and answers. Each question may have variant forms to get the same answer. If a question has at least two variants, I want the program to go through the variants at random each time the user runs the program and comes to a particular question. With each session, when the computer comes to a multi-variant question, the computer has to choose a different variant for this session. The user finishes the session and goes on to something else. If she then wants to run the program again, I want the computer to choose a different variant than the last time. With each session of using the program the computer will choose a different variant until it has gone through all the variants for that question. Then in a new session it starts all over again to ask variants at random but at no point may the computer ask the same variant twice in succession. For example, if the last variant in a series is 2, then when the user starts a new session and comes to this question, if the computer tries to ask variant 2 again, we make it choose another question.
     To accomplish that, for each multi-variant question I create a “QuestionPicker” numeric array with two more items than the number of variants. Let’s take an example of a question with three variants. I put consecutive integers (1, 2, 3) in the first three elements of the array. The second last element has the number of variants, and the last item is empty. It looks like this: 1, 2, 3, 3, 0. When the computer needs to choose a variant, it checks the second last item to find how many variants it has. In this case it is 3, so it chooses a number at random between 1 and 3. Let’s say it chooses 2. It looks in cell 2 to find the number of the variant it uses, 2. Then to ensure that it will not choose 2 again, it takes the highest number in the sequence, the number in cell 3 and writes it over the current choice. The first three items are now 1, 3, 3. It also reduces the number in the second last cell by 1, so the whole array looks like this: 1, 3, 3, 2, 0.
     My problem was that I need to save the revised QuestionPicker for the next session. (I explain below how the QuestionPicker keeps choosing different questions, but here the problem is how to save the revised array.) All the QuestionPicker arrays are stored in a scripting dictionary accessed with a unique key for each question. When the computer comes to a new question, it gets the QuestionPicker for that question from the scripting dictionary. It gets the variant number and saves the revised array in the scripting dictionary. I therefore need to save the content of the scripting dictionary in a string that I can put into a document variable for the next time it is needed.
     I suspect that the solution proposed by Bill Prew will do the trick, but I want first to test it with my data.

     The following is a less-important explanation of how the QuestionPicker does its work. When the user runs the program again and comes to the same question as described above, the computer will have loaded the revised QuestionPicker array: 1, 3, 3, 2, 0. Now the machine needs a different variant than the Variant 2 it chose before. It checks the second last item, 2, and chooses a number between 1 and 2 where the choices are only 1 and 3. If it chose 1, the number in cell 1, it would use variant 1. It would also overwrite cell 1 with the highest number in this sequence, the number in cell 2 and reduce the second last item by 1 so that the sequence would now look like this: 3, 3, 3, 1, 0.
     On the other hand for the second choice, with a sequence of 1, 3, 3, 2, 0, it might have chosen the second item in the array, 3, to use the third variant. Normally it would then overwrite the current choice by the highest number in the sequence, but since our current choice is already the highest number, there would no need to overwrite it with itself. It would still reduces the second last item by 1 to get this sequence: 1, 3, 3, 1, 0. Let’s continue with that choice. So far, the variants chosen have been 2, then 3.
     The third time it needs a variant, it has only one choice, the number 1 in position 1. It chooses that and the three variants chosen were 2, 3, 1. The computer then resets the array. The array has five items, so there will be 3 choices again, 1 through 3. It writes those numbers in the first three cells, puts the number of variants, 3, in the second last place and puts the current choice, 1, in the last place. Now the array looks like this: 1, 2, 3, 3, 1.
     We start the procedure over again, but if the choice it makes matches the last choice, saved in the last item in the array, it has to choose another number. That extra step to avoid choosing the item stored in the last element of the array occurs only when we start a new session, i.e., when the second-last item in the array is 2 less than the number of items in the array.

     --John Robin (Allen)
     Priddis, Alberta, Canada

I have tested Bill Prew's code as supplied, and it works perfectly. When I put the code into my own data, it does not convert a string into a dictionary.
     I'll try to get a small extract of my code to try to nail down the problem.
     I changed the Dim statement for the code from "Dim MyDictionary as Object" to "Dim MyDictionary as New Scripting.dictionry" and when I specified in the subroutines that objDict was not an Object but a Scripting.Dictionary, the code worked perfectly, but not with my data.
     When I get my data reduced to something more workable, that should show what I am doing wrong.
     More tomorrow.
     j.r.a.

If you are storing an array as the "value" in the Dictionary, then my first approach would need some modification for that.  Once I get a look at your code we should be able to work that out.

Based on what you described, there must be another repository for the questions and answers and the variants?  And what exactly is the unique key you used for the dictionary - just wondering how that works,  but doesn't really affect the work of this specific question.

I think "paot" should have been "pair", was working that sample code up pretty quick as a sample.

~bp

Excellent solution. I'm grateful, for I initially thought the problem was unsolvable.
     J.R. in Priddis, Alberta

Welcome, glad that was useful.

~bp