vba · DOCS

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 (Collection) is a group of objects that can contain elements of different data types. Elements are accessed by an index starting at 1 (or by a key), and adding or removing elements is easy with Add and Remove.

What is an Array?

An array is a data structure that holds elements in order, normally of the same data type (an array of type Variant can also store values of different types). Elements are accessed by index. Besides fixed-size arrays, whose size is set at declaration, there are dynamic arrays whose size can be changed with ReDim. Indexes normally start at 0, but you can specify a range such as Dim a(1 To 3).

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.

Adding and removing during the loop

Calling Add or Remove on a collection while processing it with For Each can cause skipped elements or unexpected behavior. If you need to remove items, loop backward with Remove, such as For i = myCollection.Count To 1 Step -1.

Processing Arrays Item by Item

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

Dim i As Long
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.

For Each also works with arrays. The variable that receives each element must be of type Variant. However, For Each cannot modify the element values and gives you no index, so use For...Next when you need to update values.

Dim v As Variant
For Each v In myArray
    Debug.Print v
Next v

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

Collection.Add raises runtime error 457 when you try to add an item with a key that already exists. You can use this behavior to remove duplicates. Keys are strings and are not case-sensitive.

Dim uniqueCollection As Collection
Set uniqueCollection = New Collection

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

Dim item As Variant
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
Dim i As Long
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

ReDim Preserve keeps the existing elements while resizing, but for arrays with two or more dimensions only the size of the last dimension can be changed. Also, the entire array is copied on every call, so avoid growing it one item at a time inside a loop and allocate a sufficiently large size in advance.

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