Monday, February 20, 2017

Excel VBA array of collections






'Try this code in module
 
Dim ArrayOfCollections(100) As Collection
 
Sub AssignValues()
 
Dim Collection1 As New Collection    'Declare the first collection
 
Collection1.Add "Element [1] in collection [1]"
Collection1.Add "Element [2] in collection [1]"
 
' Set array value to the predefined collection
Set ArrayOfCollections(1) = Collection1
'ArrayOfCollections(1) = Collection1     'This code will return error, don't use it
 
' Show values of the first array element
For Each Element In ArrayOfCollections(1)
MsgBox (Element)
Next Element
 
Dim Collection2 As New Collection    'Declare the second collection
 
Collection2.Add "Element [1] in collection [2]"
Collection2.Add "Element [2] in collection [2]"
Collection2.Add "Element [3] in collection [2]"
 
Set ArrayOfCollections(2) = Collection2
 
' Show values of second array element
For i = 1 To ArrayOfCollections(2).Count
MsgBox (ArrayOfCollections(2).Item(i))
Next i
 
End Sub
  

Tags:

Access-Excel-Power Point-Word VBA array of collections

Access-Excel-Power Point-Word VBA collection of collections

Access-Excel-Power Point-Word VBA non-uniform array

Access-Excel-Power Point-Word VBA structured list