How do I use a "Class Module" or a "Module" in Visual Basic For Applications?

Question

I'm trying to create a collection in vba and can't figure out how to do this. Can anyone either explain this to me or send me off to some links?

I've been working on this same "language issue" for several hours. I've checked SO, google, MSDN, and F1 help to no avail.

Answer 1

The basic approach is:

Declare a Collection object

Dim oCol As Collection

Create an instance of the object

Set oCol = New Collection

Add things to the collection

oCol.Add Item:=1, Key:="Item1IsANumber"
oCol.Add Item:="SomeString", Key:="Item2IsAString"

Refer to the items

z = oCol.Item(1)  ' z = 1
z = oCol.Item(2)  ' z = "SomeString"
z = oCol.Item("Item1IsANumber")  ' z = 1
z = oCol.Item("Item2IsAString")  ' z = "SomeString"

CPearson.com is a good reference for lots of things vba

Here's a Link to a collections page