An Excel VBA macro that helps in the study of collections

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.

Advertisements

About Dimitrios Kalemis

I am a systems engineer specializing in Microsoft products and technologies. I am also an author. Please visit my blog to see the blog posts I have written, the books I have written and the applications I have created. I definitely recommend my blog posts under the category "Management", all my books and all my applications. I believe that you will find them interesting and useful. I am in the process of writing more blog posts and books, so please visit my blog from time to time to see what I come up with next. I am also active on other sites; links to those you can find in the "About me" page of my blog.
This entry was posted in Development. Bookmark the permalink.