Return to VBA Code Examples

VBA Array of Objects

This tutorial will demonstrate how to create and use arrays of objects in VBA.

In VBA, Arrays are variables that store multiple values. You can store VBA Objects in arrays in the same way as you would store any other data.

Declaring the Object Array

In this example, we will declare an array of VBA worksheets:

Populating a Static Object Array

Declare the Object Array to be Static, and then you can populate the array with the sheets you select from your workbook.

Populating a Dynamic Object Array

You can declare the Object Array to be  Dynamic, and then count the sheets in the workbook before assigning the array size to the Object Array.

In the example above, we first declare the Worksheet Array.  We then count the number of sheets in the workbook, and assign that value minus one to the UBound of the Array.  This is due to the fact that the LBound of the Array starts as 0.  Finally, we loop through the sheets and add each sheet to the array.

Using the Object Array in VBA Code

Once we have populated the Worksheet array, we can use VBA to loop through the array.

In the example above, we loop through the array and Bold the first row of each sheet in the array.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!