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.


JohnRobinAllenRetired professor of FrenchAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
I like to use a Dictionary for this myself...

Dim dic As Object, arr As Variant, x As Variant

Set dic = CreateObject("Scripting.Dictionary")

If dic.Exists(ACMsg) Then
    dic.Item(ACMsg) = dic.Item(ACMsg) + 1
    dic.Add ACMsg, 0
End If

'some code

arr = dic.Keys
For Each x In arr
    MsgBox "Phrase: '" & x & "'; Count: " & dic.Item(x)

Set dic = Nothing

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnRobinAllenRetired professor of FrenchAuthor Commented:
Your solution is much easier than another solution I thought of last night:
       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.
     j.r. (for John Robin; I was "j.r." long before Dallas and J.R. Ewing ever existed.)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.