What is Collection in VBA?

In VBA collection is useful to strore the data
It is not editable like arrays
It worrks like an object, and useful to store the data, there after user can extract from that collection
Collection object consists of 4 methods

(i)Add

(ii)Count

(iii)Item

(iv)Remove

While working with collecton no need to import any Liabrary in VBA

Add Data to Collection:

Cn.Add "Apple"
Cn.Add "Banana"
Cn.Add "Grapes"

Add PAPAYA before Item 1

MsgBox Cn.Item(1)
Cn.Add "Papaya", , 1
MsgBox Cn.Item(1)

Loop through the Items in Collection

For i = 1 To Cn.Count
MsgBox Cn(i)
Next

Check whether data exists or not

For Each Item In Cn
If Item = "Apple" Then
MsgBox "Data Exists"
Exit For
End If
Next

Add Multiple values to the collection

Dim c As Collection
Set c = New Collection
c.Add "Apple", "Fruit"
c.Add Array("Apple", "Banana"), "Fruits"
MsgBox c.Item("Fruit")
MsgBox c.Item("Fruits")(0)
MsgBox c.Item("Fruits")(1)

Add Multiple items to single Key

Set A = New Collection
Set B = New Collection

For R = 1 To 5
A.Add Range("B" & R).Address, Range("A" & R).Address
B.Add Array(Range("B" & R).Address, Range("C" & R).Address, Range("D" & R).Address), Range("E" & R).Address
Next

For x = 1 To A.Count
MsgBox A(x)
MsgBox A.Item(x)
Next

For x = 1 To B.Count
MsgBox B(x)(x - 1)
MsgBox B.Item(x)(x - 1)
Next