CODEWITHSUNDEEP
arraysvbaclass
Barth
Barth

Reputation: 11

How to create 2D arrays or collections of custom classes in VBA?

I have a custom class, and 700 instances of this class. Some of these instances share the same value for a specific property (TourID). I want to assemble these instances in a 2D array or a collection based on the value of their TourID.

Creating a 2D array gives me a Type Mismatch error, and creating a Collection doesn't allow me to assign a key with the value of the TourID to the items.

Dim list_of_stops() As New Stops
For i = 0 To UBound(list_tours)
LastValue = Ubound(list_of_stops(list_tours(i).TourID))
Redim Preserve list_of_stops(list_tours(i).TourID, LastValue + 1)
list_of_stops(list_tours(i).TourID, LastValue) = list_tours(i)
Next

I would like to have list_of_stops() structured in the following way:

list_of_stops(1,0) = first stop with tour ID = 1
list_of_stops(1,1) = second stop with tour ID = 1
...
list_of_stops(1,n) = n-th stop with tour ID = 1
...
list_of_stops(n,n) = n-th stop with tour ID = n

Upvotes: 0

Views: 379

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

I can't explain why you would be getting a type mismatch error without seeing more of your code (declarations, mainly), but without a Set keyword the assignment is probably causing let-coercion, which fails (although, I'm missing something, because that would be error 91, or if the class has a default property, the closest I get to a type mismatch is a compile-time "invalid use of property").

Regardless, your life would be simplier with a Dictionary keyed with the unique TourID values, holding a Collection of all instances with that TourID value.

Reference the Microsoft Scripting Runtime library (Scripting):

Dim values As Dictionary
Set values = New Dictionary ' avoid "As New" unless you *NEED* an auto-instantiated object

Dim k As Variant
Dim currentGroup As Collection
For Each k In GetUniqueValuesForTourID 'TODO: a function that returns the unique IDs
    Set currentGroup = New Collection
    Dim v As Variant
    For Each v In GetAllValuesForTourID(k) 'TODO: a function that returns the records for given ID
        currentGroup.Add v, k 'note: Collection.Add value, key
    Next
    Set values(k) = currentGroup 'note the "Set" keyword; or: Dictionary.Add key, value
Next

Now if you iterate the dictionary entries, each item is a Collection, and in each collection each item is a Stops instance (why is it pluralized? is it a custom collection class?). This makes it very easy to instantly retrieve a collection of all Stops, given a TourID.

Upvotes: 2

Related Questions