VBA/Excel/Access/Word/Data Type/Collection
Содержание
- 1 Accessing an Item in a Custom Collection
- 2 Adding Items to a Custom Collection
- 3 Create collection
- 4 Creating and Working with Custom Collections
- 5 Item method is the default method of the Collection object
- 6 Iterating Through the Elements of a Custom Collection: use the For...Each loop to iterate through the items in a collection
- 7 Looping Through the Elements of a Custom Collection
- 8 Referencing Items in a Custom Collection
- 9 Refer to an item in a collection using its unique key
- 10 Remove all the elements of a collection in two ways:
- 11 Remove element with the key
- 12 Removing Items from a Custom Collection
- 13 Removing Objects from a Collection
- 14 Using a Collection to Manipulate Multiple Instances of the FileInformation Class
Accessing an Item in a Custom Collection
<source lang="vb">
Sub AccessCollection()
Dim colNames As Collection Set colNames = New Collection colNames.Add "A", "A" colNames.Add "D", "D" colNames.Add "A2", "A2" colNames.Add "B", "B" colNames.Add "S", "S" colNames.Add "S3", "S2" Debug.Print colNames.Item(1)
End Sub
</source>
Adding Items to a Custom Collection
<source lang="vb">
Sub NewCollection()
Dim colSports As New Collection colSports.Add "Basketball" colSports.Add "Skiing" colSports.Add "Skating", Before:=1 colSports.Add "Hockey", After:=2 End Sub </source>
Create collection
<source lang="vb">
Sub NewEmployees()
Dim colEmployees As New Collection Dim emp As Variant With colEmployees .Add Item:="John Collins", Key:="128634456" .Add Item:="Mary Poppins", Key:="223998765" .Add Item:="Karen Loza", Key:="120228876", Before:=2 End With For Each emp In colEmployees Debug.Print emp Next MsgBox "There are " & colEmployees.Count & " employees."
End Sub
</source>
Creating and Working with Custom Collections
<source lang="vb">
Sub AddToCollection()
Dim colNames As Collection Set colNames = New Collection colNames.Add "A", "B" colNames.Add "C", "D" colNames.Add "E", "F" colNames.Add "G", "H" colNames.Add "I", "J" colNames.Add "K", "ZL"
End Sub
</source>
Item method is the default method of the Collection object
<source lang="vb">
Sub AccessCollection()
Dim colNames As Collection Set colNames = New Collection colNames.Add "A", "A" colNames.Add "D", "Dan" colNames.Add "Al", "Alexis" colNames.Add "B", "Brendan" colNames.Add "S", "Sonia" colNames.Add "Su", "Sue" Debug.Print colNames(1) Debug.Print colNames.Item(1)
End Sub
</source>
Iterating Through the Elements of a Custom Collection: use the For...Each loop to iterate through the items in a collection
<source lang="vb">
Sub IterateCollection()
Dim colNames As Collection Dim varItem As Variant Set colNames = New Collection colNames.Add "A", "A" colNames.Add "Dan", "Dan" colNames.Add "Al", "Ale" colNames.Add "B", "Bren" colNames.Add "S", "Son" colNames.Add "Sue", "Su" For Each varItem In colNames Debug.Print varItem Next varItem
End Sub
</source>
Looping Through the Elements of a Custom Collection
<source lang="vb">
Sub LoopThroughCollection()
Dim colSports As New Collection Dim varSport As Variant colSports.Add "Basketball" colSports.Add "Skiing" colSports.Add "Skating", Before:=1 colSports.Add "Hockey", After:=2 For Each varSport In colSports Debug.Print varSport Next varSport
End Sub
</source>
Referencing Items in a Custom Collection
<source lang="vb">
Sub CustomKey()
Dim colSports As New Collection colSports.Add "Basketball", "B" colSports.Add "Skiing", "S1" colSports.Add "Skating", "S2" colSports.Add "Hockey", "H" Debug.Print colSports.Item("S1")
End Sub
</source>
Refer to an item in a collection using its unique key
<source lang="vb">
Sub AccessCollection()
Dim colNames As Collection Set colNames = New Collection colNames.Add "A", "A" colNames.Add "D", "Da" colNames.Add "Al", "Al" colNames.Add "Br", "Br" colNames.Add "S", "So" colNames.Add "Su", "Su" Debug.Print colNames(1) Debug.Print colNames.Item(1) Debug.Print colNames("Alexis")
End Sub
</source>
Remove all the elements of a collection in two ways:
<source lang="vb">
Sub AccessCollection()
Dim colNames As Collection Set colNames = New Collection colNames.Add "A", "A" colNames.Add "Dan", "Dan" colNames.Add "Al", "Ale" colNames.Add "B", "Bre" colNames.Add "S", "Son" colNames.Add "Sue", "Su" Set colNames = New Collection "Or Set colNames = Nothing
End Sub
</source>
Remove element with the key
<source lang="vb">
Sub AccessCollection()
Dim colNames As Collection Set colNames = New Collection colNames.Add "A", "A" colNames.Add "Dan", "Dan" colNames.Add "Al", "Ale" colNames.Add "B", "Bre" colNames.Add "S", "So" colNames.Add "Sue", "Su" colNames.Remove "Sonia" Debug.Print colNames(1) Debug.Print colNames.Item(1) Debug.Print colNames("Alexis")
End Sub
</source>
Removing Items from a Custom Collection
<source lang="vb">
Sub AccessCollection()
Dim colNames As Collection Set colNames = New Collection colNames.Add "A", "A" colNames.Add "Dan", "Dan" colNames.Add "Al", "Al" colNames.Add "B", "Br" colNames.Add "S", "So" colNames.Add "Sue", "Su" colNames.Remove 2 Debug.Print colNames(1) Debug.Print colNames.Item(1) Debug.Print colNames("Alexis")
End Sub
</source>
Removing Objects from a Collection
<source lang="vb">
Sub deleteEmployees()
" declare the employees collection Dim colEmployees As New Collection " declare a variable to hold each element of a collection Dim emp As Variant " Add 3 new employees to the collection With colEmployees .Add Item:="John Collins", Key:="128634456" .Add Item:="Mary Poppins", Key:="223998765" .Add Item:="Karen Loza", Key:="120228876", Before:=2 End With " list the members of the collection For Each emp In colEmployees Debug.Print emp Next MsgBox "There are " & colEmployees.Count & " employees." colEmployees.Remove (3) MsgBox colEmployees.Count & " employees remain."
End Sub
</source>
Using a Collection to Manipulate Multiple Instances of the FileInformation Class
<source lang="vb">
Sub FileInfoCollection()
Dim colFiles As Collection Dim objFileInfo As FileInformation Dim strFile As String Dim vntFile As Variant Set colFiles = New Collection strFile = Dir("c:\") Do Until Len(strFile) = 0 Set objFileInfo = New FileInformation objFileInfo.FullFileName = strDirName & strFile colFiles.Add objFileInfo strFile = Dir() Loop For Each vntFile In colFiles Debug.Print vntFile.Drive, vntFile.Path, vntFile.Name Next vntFile
End Sub
</source>