[VBA] Count number of occurances in an Array?

[Safety]

Hey guys,

I have a variable length array in VBA whose number of elements will change with usage. For example:

[ Apple, Orange, Banana, Banana, Apple, Banana ]

I would like to count the number of occurances of each element within the array and then return both the element and its count in a msgbox. For example

MsgBox

Apple x 2
Orange x 1
Banana x 3

I've searched the forums but could not find a similar solution.

Any advice would be greatly appreciated!

 

[VoG]

Maybe like this

MsgBox WorksheetFunction,CountIf(MyArray,"Apple")

 

[Safety]

Thanks for the suggestion Peter but I don't believe Worksheet functions work with an array.

I think the solution involves a combination of loops with counters but I'm having trouble with that part of the code. For example:

For i = LBound(MyArray) To UBound(MyArray)
For k = LBound(MyArray) To UBound(MyArray)
If MyArray(i) = MyArray(k) Then
counter = counter + 1
End If
NameString(i) = MyArray(i) + counter
Next k
Next i

The second part of the problem is figuring out how to removie the duplicates from the MsgBox. For Example:

MsgBox

Apple x 2
Orange x 1
Banana x 3
Banana x 3
Apple x 2
Banana x 3

 

[Marcelo Branco]

Maybe something like this

Sub aTest()
    Dim myArray() As Variant, dict As Object
    Dim i As Long, v As Variant
    
    myArray = Array("Apple", "Orange", "Banana", "Banana", "Apple", "Banana")
    Set dict = CreateObject("Scripting.Dictionary")
    
    For i = LBound(myArray) To UBound(myArray)
        If dict.exists(myArray(i)) Then
            dict.Item(myArray(i)) = dict.Item(myArray(i)) + 1
        Else
            dict.Add myArray(i), 1
        End If
    Next i
    
    For Each v In dict.keys
        MsgBox v & " " & dict.Item(v)
    Next v
End Sub

M.

 

[Safety]

It works perfectly Marcelo! Using the array elements as keys for a dictionary and then using that dictionary object to remove the duplicates was brilliant (!!!). Thank you

 

[Marcelo Branco]

It works perfectly Marcelo! Using the array elements as keys for a dictionary and then using that dictionary object to remove the duplicates was brilliant (!!!). Thank you

You are welcome and thanks for the feedback.

M.

 

[mahmed1]

You are welcome and thanks for the feedback.

M.

Hi marcelo

Is there any chance you can explain how the dict scripting and exits and jeys method works.

Hopefully you can help understand how this works.

Awesome code

 

[Marcelo Branco]

Hi marcelo

Is there any chance you can explain how the dict scripting and exits and jeys method works.

Hopefully you can help understand how this works.

Awesome code

Hi mahmed ,

Take a look at:

1. A very good introduction and explanation about Dictionary
http://www.techbookreport.com/tutorials/vba_dictionary.html

2. An excellent article
http://www.experts-exchange.com/Sof...A_3391-Using-the-Dictionary-Class-in-VBA.html

M.