VBA Tutorial => Adding Items To A Collection

Download VBA (PDF)

VBA

• Getting started with VBA
• Awesome Book
• Awesome Community
• Awesome Course
• Awesome Tutorial
• Awesome YouTube
• API Calls
• Arrays
• Assigning strings with repeated characters
• Attributes
• Automation or Using other applications Libraries
• Collections
• Adding Items to a Collection
• Clearing All Items From a Collection
• Determining if a Key or Item Exists in a Collection
• Getting the Item Count of a Collection
• Removing Items From a Collection
• Retrieving Items From a Collection
• Comments
• Concatenating strings
• Conditional Compilation
• Converting other types to strings
• Copying, returning and passing arrays
• CreateObject vs. GetObject
• Creating a Custom Class
• Creating a procedure
• Data Structures
• Data Types and Limits
• Date Time Manipulation
• Declaring and assigning strings
• Declaring Variables
• Error Handling
• Events
• Flow control structures
• Frequently used string manipulation
• Interfaces
• Macro security and signing of VBA-projects/-modules
• Measuring the length of strings
• Naming Conventions
• Non-Latin Characters
• Object-Oriented VBA
• Operators
• Passing Arguments ByRef or ByVal
• Procedure Calls
• Reading 2GB+ files in binary in VBA and File Hashes
• Recursion
• Scripting.Dictionary object
• Scripting.FileSystemObject
• Searching within strings for the presence of substrings
• Sorting
• String Literals - Escaping, non-printable characters and line-continuations
• Substrings
• User Forms
• VBA Option Keyword
• VBA Run-Time Errors
• Working with ADO
• Working With Files and Directories Without Using FileSystemObject

VBA

• Getting started with VBA
• Awesome Book
• Awesome Community
• Awesome Course
• Awesome Tutorial
• Awesome YouTube
• API Calls
• Arrays
• Assigning strings with repeated characters
• Attributes
• Automation or Using other applications Libraries
• Collections
• Adding Items to a Collection
• Clearing All Items From a Collection
• Determining if a Key or Item Exists in a Collection
• Getting the Item Count of a Collection
• Removing Items From a Collection
• Retrieving Items From a Collection
• Comments
• Concatenating strings
• Conditional Compilation
• Converting other types to strings
• Copying, returning and passing arrays
• CreateObject vs. GetObject
• Creating a Custom Class
• Creating a procedure
• Data Structures
• Data Types and Limits
• Date Time Manipulation
• Declaring and assigning strings
• Declaring Variables
• Error Handling
• Events
• Flow control structures
• Frequently used string manipulation
• Interfaces
• Macro security and signing of VBA-projects/-modules
• Measuring the length of strings
• Naming Conventions
• Non-Latin Characters
• Object-Oriented VBA
• Operators
• Passing Arguments ByRef or ByVal
• Procedure Calls
• Reading 2GB+ files in binary in VBA and File Hashes
• Recursion
• Scripting.Dictionary object
• Scripting.FileSystemObject
• Searching within strings for the presence of substrings
• Sorting
• String Literals - Escaping, non-printable characters and line-continuations
• Substrings
• User Forms
• VBA Option Keyword
• VBA Run-Time Errors
• Working with ADO
• Working With Files and Directories Without Using FileSystemObject

VBA Collections Adding Items to a Collection

Fastest Entity Framework Extensions

Bulk Insert Bulk Delete Bulk Update Bulk Merge

Example

Items are added to a Collection by calling its .Add method:

Syntax:

.Add(item, [key], [before, after])

Parameter	Description
item	The item to store in the Collection. This can be essentially any value that a variable can be assigned to, including primitive types, arrays, objects, and Nothing.
key	Optional. A String that serves as a unique identifier for retrieving items from the Collection. If the specified key already exists in the Collection, it will result in a Run-time error 457: "This key is already associated with an element of this collection".
before	Optional. An existing key (String value) or index (numeric value) to insert the item before in the Collection. If a value is given, the after parameter must be empty or a Run-time error 5: "Invalid procedure call or argument" will result. If a String key is passed that does not exist in the Collection, a Run-time error 5: "Invalid procedure call or argument" will result. If a numeric index is passed that is does not exist in the Collection, a Run-time error 9: "Subscript out of range" will result.
after	Optional. An existing key (String value) or index (numeric value) to insert the item after in the Collection. If a value is given, the before parameter must be empty. Errors raised are identical to the before parameter.

Notes:

• Keys are not case-sensitive. .Add "Bar", "Foo" and .Add "Baz", "foo" will result in a key collision.

• If neither of the optional before or after parameters are given, the item will be added after the last item in the Collection.

• Insertions made by specifying a before or after parameter will alter the numeric indexes of existing members to match thier new position. This means that care should be taken when making insertions in loops using numeric indexes.

Sample Usage:

Public Sub Example() Dim foo As New Collection With foo .Add "One" 'No key. This item can only be retrieved by index. .Add "Two", "Second" 'Key given. Can be retrieved by key or index. .Add "Three", , 1 'Inserted at the start of the collection. .Add "Four", , , 1 'Inserted at index 2. End With Dim member As Variant For Each member In foo Debug.Print member 'Prints "Three, Four, One, Two" Next End Sub

Got any VBA Question?

Ask any VBA Questions and Get Instant Answers from ChatGPT AI: ChatGPT answer me! PDF - Download VBA for free Previous Next This modified text is an extract of the original Stack Overflow Documentation created by following contributors and released under CC BY-SA 3.0 This website is not affiliated with Stack Overflow SUPPORT & PARTNERS

• Advertise with us
• Contact us
• Cookie Policy
• Privacy Policy

STAY CONNECTED

Get monthly updates about new articles, cheatsheets, and tricks.

Subscribe Cookie This website stores cookies on your computer. We use cookies to enhance your experience on our website and deliver personalized content. For more details on our cookie usage, please review our Cookie Policy and Privacy Policy Accept all Cookies Leave this website