Moving your VB6 COM collections to .Net under Vantive VBA

A pretty typical VB6 object model was to use a collection class to represent a group of objects. So you'd have a "Car" object with properties like "Colour", "Make", "Model" etc. and you'd store multiple of these in a "Cars" object, which would internally use a Collection and externally provide your "Add", "Count" "Remove" and enumerator methods. You could expose these as method parameters via COM and Vantive VBA would happily be able to enumerate the collection:

Dim oCars as Object
Dim oCar as Object
Set oCars = oComVB6.FetchCarsByColour("blue")
For Each oCar in oCars
  MsgBox "Make is " & oCar.Make
Next oCar

Skip forward a few years: Vantive is still kicking and it is time to convert your VB6 COM component to .Net. Our VB6 Collection object is gone, what will we use instead? What about an array of objects? So in .Net we have:

public class Car : ICar {...}
public Car[] FetchCarsByColour(string colour) {...}

To access these from Vantive VBA, this should do the trick:

Dim oCars() as Object
Dim oCar as Object
oCars = oComNet.FetchCarsByColour("blue")
For I = 0 to UBound(oCars)
...

This works fine via VB6, but when you try to compile this in Vantive VBA, you'll get a "Cannot assign whole array" error message on the oCars=... line.

The solution is to use the ArrayList class. Like this:

public ArrayList FetchCarsByColour(string colour) {...}

Which you can then access in Vantive VBA exactly as you could with the VB6 collection, including the "Count" method:

Dim oCars as Object
Dim oCar as Object
Set oCars = oComNet.FetchCarsByColour("blue")
For Each oCar in oCars
  MsgBox "Make is " & oCar.Make
Next oCar
MsgBox "You found " & oCars.Count & " cars"