Counting unique values in a VBA collection

Question

I have a VBA collection that contains several File items (I created a class module called File with several properties). I was curious if there is a way to use a For loop to iterate through all the items in the collection and do two things:

Get a list of all unique items based on a File property that I specify
Count the number of items that match each unique value from the above list

For example, let's say I have 4 File items in my collection:

File1 (File1.Pattern = "Test*.xlsx")
File2 (File2.Pattern = "Test*.xlsx")
File3 (File3.Pattern = "Test*.txt")
File4 (File4.Pattern = "Test*.csv")

The output I would want would be (ideally an array or separate collection):

Test*.xlsx (2)
Test*.txt (1)
Test*.csv (1)

Any thoughts on how I might implement something like this?

Declare variables for each pattern then increment them for each match through loop? — findwindow, Apr 04 '16 at 19:49
What if you don't know in advance what patterns there are @findwindow? — GSerg, Apr 04 '16 at 19:50
What @GSerg said. There can be an infinite amount of patterns. — Eli Greenberg, Apr 04 '16 at 19:50
Possible duplicate of [vba: get unique values from array](http://stackoverflow.com/questions/3017852/vba-get-unique-values-from-array) — GSerg, Apr 04 '16 at 19:51
@GSerg I guess the number of file extensions can approach infinity so make a loop first to grab all of them? — findwindow, Apr 04 '16 at 19:51
@EliGreenberg Just create a new collection, using the property that you want to get the unique count on as the `Key`. Use an `On error resume next` to ignore the errors caused by trying to add duplicate keys. — Ron Rosenfeld, Apr 04 '16 at 20:01
@RonRosenfeld Great idea until Tim gave me an even better idea :) — Eli Greenberg, Apr 04 '16 at 20:07

score 3 · Accepted Answer · answered Apr 04 '16 at 20:05

3

As long as you don't need to do this on a Mac:

Dim d, f, k
Set d = CreateObject("scripting.dictionary")

For Each f in myFiles
    d(f.Pattern) = d(f.Pattern)+1
Next f

For Each k in d
    Debug.Print k, d(k) 
Next k

answered Apr 04 '16 at 20:05

Tim Williams

154,628
8
97
125

Thank you very much Tim. – Eli Greenberg Apr 04 '16 at 20:07

Counting unique values in a VBA collection

1 Answers1