title: Excel VBA Collections and Custom Data Types
body: <p>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):</p>
  
  <pre><code>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 &amp; " - " &amp; vrecord.MyString &amp; " - " &amp;   vrecord.MyDoubleArr(0) &amp; ", " &amp; vrecord.MyDoubleArr(1) &amp; ", " &amp; vrecord.MyDoubleArr(0)
    Next x
  End Sub
  </code></pre>
  
  <p>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"</p>
  
  <p>I am not a novice with VBA, but I am trying to make the next step.</p>
  
  <p>Thanks in advance.</p>