Save a scripting dictionary to disk with VBA
With Word and VBA, I use a scripting dictionary to hold data that I frequently need to revise. When revised, is there a quick way to change the data (i.e., key and data) in the dictionary into a string that I could store as a document variable?
If needed, I can give specifics. I thought there must be a way I can go through all the items in a dictionary, retrieve the key and the data, and create a new scripting dictionary from the revised data.
I suspect the problem is unsolvable.
--J.r. allen
Priddis, Alberta, Canada
If needed, I can give specifics. I thought there must be a way I can go through all the items in a dictionary, retrieve the key and the data, and create a new scripting dictionary from the revised data.
I suspect the problem is unsolvable.
--J.r. allen
Priddis, Alberta, Canada
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 SubASKER
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
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
ASKER
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.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent solution. I'm grateful, for I initially thought the problem was unsolvable.
J.R. in Priddis, Alberta
J.R. in Priddis, Alberta
Welcome, glad that was useful.
~bp
~bp
This feels doable, but need a bit more context and info.
~bp