With VBA and Word, how can I change the value of an item in a collection?
I have a number of short phrases, some of them repeated, that I have found in a document. I would like to make a list of the unique phrases, along with their frequencies.
I thought I should store the phrases in a collection, so I wrote a subroutine that tells me whether any given phrase is already in a collection. Let us say that
MsgsInDoc is the collection that holds the messages.
ACMsg is a string variable that will hold each message to store.
My subroutine tells me that ACMsg is not in the MsgsInDoc collection, so I add it.
MsgsInDoc.Add Item:=1, Key:=ACMsg
That specifies that ACMsg is the unique key, and its frequency of appearances is initially 1, since my other subroutine has told me that ACMsg is not yet in the collection.
I keep doing that with other phrases and at some point my other subroutine tells me that the current message is already in the collection. My task is then to increment the frequency value, say from 1 to 2.
I have no idea how to change the value of an item in a collection. My kludge is to note the current value, delete the item in the collection, and add a new entry with that name and with a different frequency:
Freq = MsgsInDoc.Item(ACMsg)
MsgsInDoc.Remove (ACMsg)
MsgsInDoc.Add Item:=Freq + 1, Key:=ACMsg
That seems like a slow way to change the value stored with a given key. There must be a more efficient way to solve this problem. Is there a better way to work with a collection? Is there some other way to solve the problem?
I hope so, and I am optimistic that once again EE will solve my problems.
--j.r.
I thought I should store the phrases in a collection, so I wrote a subroutine that tells me whether any given phrase is already in a collection. Let us say that
MsgsInDoc is the collection that holds the messages.
ACMsg is a string variable that will hold each message to store.
My subroutine tells me that ACMsg is not in the MsgsInDoc collection, so I add it.
MsgsInDoc.Add Item:=1, Key:=ACMsg
That specifies that ACMsg is the unique key, and its frequency of appearances is initially 1, since my other subroutine has told me that ACMsg is not yet in the collection.
I keep doing that with other phrases and at some point my other subroutine tells me that the current message is already in the collection. My task is then to increment the frequency value, say from 1 to 2.
I have no idea how to change the value of an item in a collection. My kludge is to note the current value, delete the item in the collection, and add a new entry with that name and with a different frequency:
Freq = MsgsInDoc.Item(ACMsg)
MsgsInDoc.Remove (ACMsg)
MsgsInDoc.Add Item:=Freq + 1, Key:=ACMsg
That seems like a slow way to change the value stored with a given key. There must be a more efficient way to solve this problem. Is there a better way to work with a collection? Is there some other way to solve the problem?
I hope so, and I am optimistic that once again EE will solve my problems.
--j.r.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
If one uses a collection, one should insert the unique key and a sequential number that increments each time one has a new entry. Then one can store the data for one or many fields in appropriate arrays. The element in the arrays is designated by the sequential number.
When a duplicate entry is found (using my now useless function that detects potential duplicates in a collection), then one changes the appropriate array.
I have not yet written that code, but Patrick's solution is much simpler and more elegant.
Thanks!
j.r. (for John Robin; I was "j.r." long before Dallas and J.R. Ewing ever existed.)