Iterate Collections and Arrays

Maintained on

Differences Between Collections and Arrays

First, let’s briefly explain the differences between collections and arrays.

What is a Collection?

A collection is a group of objects that can contain elements of different data types. Elements can be accessed using an index, and adding or removing elements is easy.

What is an Array?

An array is a fixed-size data structure containing elements of the same data type. Elements are accessed using an index, and the size is fixed.

Processing Collections Item by Item

A common method for processing collections item by item is to use the For Each...Next loop.

Dim item As Variant
Dim myCollection As Collection
Set myCollection = New Collection

'_Add_items_to_the_collection
myCollection.Add "Item1"
myCollection.Add "Item2"
myCollection.Add "Item3"

'_Process_item_by_item
For Each item In myCollection
    Debug.Print item
Next item

Advantages of For Each...Next

  • Concise and readable: The code is concise, and adding or removing items is easy.
  • Fewer errors: You don’t need to worry about collection size or out-of-range errors.

Processing Arrays Item by Item

To process arrays item by item, use the For...Next loop.

Dim i As Integer
Dim myArray(2) As String

'_Set_values_in_the_array
myArray(0) = "Element1"
myArray(1) = "Element2"
myArray(2) = "Element3"

'_Process_item_by_item
For i = LBound(myArray) To UBound(myArray)
    Debug.Print myArray(i)
Next i

Advantages of For...Next

  • Flexibility: Using indexes makes it easy to access specific elements.
  • Control: By specifying the loop range, you can process only a specific range.

Processing Elements with Specific Conditions

You can also set specific conditions when processing elements. For example, if you want to process only elements with a specific string in a collection.

Dim item As Variant
For Each item In myCollection
    If item Like "Item*" Then
        Debug.Print "Processing: " & item
    End If
Next item
Conditional Processing

By performing conditional processing, you can more efficiently filter data and process only the necessary data.

Advanced Array and Collection Operations

By utilizing arrays and collections, VBA programs become more powerful and flexible. Here are some advanced examples.

Removing Duplicates Using Collections

Collections can be used for duplicate removal because adding an element with the same key causes an error.

Dim uniqueCollection As Collection
Set uniqueCollection = New Collection

On Error Resume Next '_Ignore_errors
uniqueCollection.Add "Item1", "Item1"
uniqueCollection.Add "Item2", "Item2"
uniqueCollection.Add "Item1", "Item1" '_Not_added_due_to_duplication
On Error GoTo 0 '_Restore_error_handling

For Each item In uniqueCollection
    Debug.Print item
Next item

Dynamic Array Resizing

In VBA, you can dynamically change the size of an array using the ReDim keyword.

Dim dynamicArray() As Integer
ReDim dynamicArray(0 To 2)

dynamicArray(0) = 10
dynamicArray(1) = 20
dynamicArray(2) = 30

'_Resize_and_add_elements
ReDim Preserve dynamicArray(0 To 3)
dynamicArray(3) = 40

For i = LBound(dynamicArray) To UBound(dynamicArray)
    Debug.Print dynamicArray(i)
Next i
Note on ReDim Preserve

When using ReDim Preserve, elements from the beginning of the array are preserved, but when used with multi-dimensional arrays, only the last dimension’s size can be changed.

Summary

Processing collections and arrays item by item in VBA can be easily achieved using For Each...Next and For...Next loops. By using these methods, you can efficiently manipulate datasets and perform complex data processing. Mastering collection and array operations will greatly expand the possibilities of your VBA scripts.

#VBA #Excel #Collection #Array #Loop