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.


Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Patrick MatthewsConnect With a Mentor Commented:
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

JohnRobinAllenAuthor 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.)
All Courses

From novice to tech pro — start learning today.