In this blog post I will present a small Excel VBA macro that demonstrates how collections can be used in Excel VBA. To run the macro, you first have to create a Class Module named TestClass and insert the following code to it:
Option Explicit Public Name As String Public Sum1 As Double Public Sum2 As Double Public Sum3 As Double
Next, you have to create a module (its name is not important) and insert the following code to it:
Option Explicit Sub Macro1() Dim myCollection As New Collection AddObject myCollection, "One", 1, 1, 1 AddObject myCollection, "Two", 1, 1, 1 AddObject myCollection, "Three", 1, 1, 1 AddObject myCollection, "Two", 1, 1, 1 AddObject myCollection, "Three", 1, 1, 1 AddObject myCollection, "One", 1, 1, 1 MsgBox myCollection.Count Dim myObject As TestClass For Each myObject In myCollection MsgBox myObject.Name & "-" & CStr(myObject.Sum1) & "-" & CStr(myObject.Sum2) & "-" & CStr(myObject.Sum3) Next myObject End Sub Sub AddObject(aCollection, aName, aSum1, aSum2, aSum3) Dim myObject As TestClass Dim objectFound As Boolean objectFound = False For Each myObject In aCollection If myObject.Name = aName Then objectFound = True myObject.Sum1 = myObject.Sum1 + aSum1 myObject.Sum2 = myObject.Sum2 + aSum1 myObject.Sum3 = myObject.Sum3 + aSum1 Exit For End If Next If Not objectFound Then Dim myClass As New TestClass myClass.Name = aName myClass.Sum1 = aSum1 myClass.Sum2 = aSum2 myClass.Sum3 = aSum3 aCollection.add myClass Set myClass = Nothing End If End Sub
If you run Macro1(), you will see a few message boxes that show the contents of the collection that I use. In this example, I wanted to show how to use a collection of objects in which you want to insert a new object if its name does not already exist, but you want to update an object if its name already exists.