Excel VBA Collections and Custom Data Types

Question

I'm trying to learn how to create a custom data type, then use that with a collection. I solved the problem another way, but this started with a timesheet report I was automating. I originally wanted a 2 dimensional array with varying data types. When I couldn't do that, some research led to the idea of a collection of custom data types. However the examples I have found keep pushing me to create a class. I am not yet comfortable with that and it seems like this should be doable. Here is kind of what I am a looking for (I started with an example I found on this site):

Option Explicit

'***** User defined type
Public Type MyType
  MyInt As Integer
  MyString As String
  MyDoubleArr(2) As Double
End Type

Public ColThings As Collection

Sub CollectionTest()
  Dim x As Integer
  Dim vrecord As MyType

  For x = 1 To 4
      vrecord.MyInt = x
      vrecord.MyString = "Matt"
      vrecord.MyDoubleArr(0) = x + 5
      vrecord.MyDoubleArr(1) = x + 6
      vrecord.MyDoubleArr(2) = x + 7
      ColThings.Add vrecord
  Next x

  For x = 1 To 4
     Debug.Assert vrecord.MyInt & " - " & vrecord.MyString & " - " &   vrecord.MyDoubleArr(0) & ", " & vrecord.MyDoubleArr(1) & ", " & vrecord.MyDoubleArr(0)
  Next x
End Sub

The error I get is: Compile Error: "Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions"

I am not a novice with VBA, but I am trying to make the next step.

Thanks in advance.

My understanding is (and I may certainly be mistaken here) that you cannot add a user-defined type into a collection, which itself is an object. I think you will have to create a class, instantiate it, then add it to your collection. You may be able to use an array instead of a collection to store your UDT though. — Soulfire
I'm unclear on why you are looping through vrecord and not ColThings at the end of your procedure. — user4039065
Stop practicing it wrong; create a class as your guides are recommending, and start practicing it right. Practicing your bad habits so strenuously, early on, will simply make it harder to break them later. — Pieter Geerkens
This weekend is my personal "class on classes". I am jumping into at the house. I do not intend to keep trying to go around things I am unfamiliar with. — Matthew Stockton

Soulfire Soulfire · Accepted Answer · 2015-05-08T19:03:35

I took a stab at it, ultimately I am not sure if this is what you want, but if you don't want to make a class then the only other option I can see is to store your types in an array instead of a collection. To my knowledge (please correct me if I am wrong) you cannot add a user-defined type to a collection, you must create it as a class, instantiate an object of that class, then add that to the collection.

Instead I declared the array Records() of MyType and added each MyType to that array.

Option Explicit

'***** User defined type
Public Type MyType
  MyInt As Integer
  MyString As String
  MyDoubleArr(2) As Double
End Type

Public ColThings As Collection

Sub CollectionTest()
  Dim x As Integer
  Dim Records() As MyType
  Dim vrecord As MyType

  For x = 1 To 4
      vrecord.MyInt = x
      vrecord.MyString = "Matt"
      vrecord.MyDoubleArr(0) = x + 5
      vrecord.MyDoubleArr(1) = x + 6
      vrecord.MyDoubleArr(2) = x + 7
      ReDim Preserve Records(x)
      Records(x) = vrecord
  Next x

  For x = 1 To 4
     Debug.Print Records(x).MyInt & " - " & Records(x).MyString & " - " & Records(x).MyDoubleArr(0) & ", " & Records(x).MyDoubleArr(1) & ", " & Records(x).MyDoubleArr(0)
  Next x

End Sub

I believe that is similar to what you want to accomplish. I haven't attempted the class method yet as you said you were not quite ready for that, but that would be a very good exercise in my humble opinion.

Excel VBA Collections and Custom Data Types

1 Answers