Cycling Through Collections Using a VBA For Each Loop

As a VBA developer, you will work with collections of objects and arrays of data. There will frequently be a need to either list all items within the group, or extract a specific element from the group. One of the most effective ways of doing this is with a For Each Loop.


This post within the Getting Started with VBA series continues the focus on Loops in VBA.

For Each Loops can be used to cycle through collections and arrays. Collections are groups of objects with specific methods and properties. For example, if multiple workbooks are open, the workbooks are part of the Workbooks collection. If a workbook contains many sheets, each of those sheets is part of the Worksheets collection. Arrays are collection of variables of a specific type.

As a VBA developer, you will likely run into situations where you need to loop through collections to return values or find a specific item. For example, you may need to determine if a worksheet is currently open to copy data from one sheet to another, or you may want to provide users with specific details on shapes.

Read Objects Properties with a For Each Loop

With a For Each Loop, you can provide a user with details on specific objects in a worksheet. In the following example, a VBA script will list out the name, width, height, and color for shapes on a spreadsheet. You can download the sample file here. The code can be executed by pressing “Ctrl” and “t” simultaneously.

The above For Each Loop contains the following components:

  1. Group/Collection: The Shapes collection, Sheet1.Shapes, is used as the collection of objects to be cycled through in the loop. In this example, the Shapes collection stores all the shapes on Sheet1 in your computer’s stack (memory)
  2. Element: Each item within the Shapes collection is converted into an individual element, in this case, the Shape object. Each individual shape object is given the temporary object name of shapeDetail. For each iteration, the shapeDetail element (Shape object) will contain the properties and methods of the Shape object.
  3. Statements/Block of Code: Within each iteration, a script is executed which extracts name, width, height, and color data from the element (Shape object) shapeDetail. A counter, i, also exists to ensure that details are printed on a new spreadsheet row, starting at row 8. Any method or property within the Shape object can be extracted at this stage.
  4. Next: The next statement terminates the shapeDetail element (Shape object) and will return control flow to the start of the For Each statement. If an element still exists in the collection, the For Each Loop will assign the next Shape object to the shapeDetail element. If the collection has been fully cycled through, the For Each Loop will terminate and skip to the next line of code below the Next statement.

When executed, the sample file appears as follows:

Spreadsheet after executing a For Each Loop which cycles through a shapes collection.
Spreadsheet after executing a For Each Loop which cycles through a shapes collection.

As previously stated, the code can be executed by pressing “Ctrl” and “t” simultaneously. As an exercise, you should add shapes, modify their sizes, and modify their colors to see how changes impact the output.

Modify Objects with a For Each Loop

While the previous example focused on extracting property values from each object of the collection, object properties can also be updated using a For Each Loop. The following script includes two new lines (highlighted), which assign a random height and width to each shape:

The two new lines assign a random integer value (between 1 and 100) to the shape width and height properties. You can run this script in the sample file by pressing “Ctrl” and “r” simultaneously.

Read Array Values with a For Each Loop

Arrays are discussed in detail later in this series, but a quick overview is all you need for application in a For Each loop. In summary, an array is a collection of variables stored in a variable-like element. For example, an array named housePets can store a list of common house pets.

The general structure of a For Each Loop managing arrays is identical for a For Each Loop managing a standard collection of objects. For the purposes of explaining how array’s work with For Each Loops, consider that an array behaves similar to a collection, but only contains string or numeric (primitive data type) values. The primary difference is that given that primitive data types do not have properties or methods, the only thing you can do with an array in a For Each loop is read or update each element value.

The following code creates a string array of common house pets through the array variable name housePets. A For Each Loop will then cycle through the array to return individual items within the array through the element (individual variable) petType. Within the code, the petType value is printed in a cell, with the counter i increasing during each iteration to print the next value on a new spreadsheet row.

As previously stated, the components of this array For Each Loop are nearly identical to the first For Each Loop example, which looped through shapes. The primary difference is that the array housePets is not a collection, even though it behaves similar to a collection within a For Each Loop. Also, given that arrays do not contain properties or methods, we cannot extract multiple data points as we did with the Shape object, such as dimensions or color. The only item that can be extracted is the value that corresponds to the specific array index (element).

Other Notes

  • Including the element name in the Next statement is optional. However, this can add clarity to your code and reduce debug errors, especially when executing scripts with nested or subsequent loops.
  • In many instances (not all), the name of a collection is the plural of the individual object. In this post’s first example, the “Shape” object is part of the “Shapes” collection. Other single (object)/plural (collection) examples include Worksheet/Workshetes, Worksheet/Worksheets, and Chart/Charts, just to name a few. It’s important to distinguish between an object and collection as they do not rely on the same methods and properties.

Leave a Reply