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:

Dim arWks(3) As Worksheet

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.

Sub TestObjArray() 
'define the array as a worksheet array 
   Dim arWks(1 to 3) As Worksheet
'add 3 sheet into the array
   set arWks(1) = Sheets(1)
   set arWks(2) = Sheets(2)
   Set arWks(3) = Sheets(3)
End Sub

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.

Sub TestObjArray()
'define the array as a worksheet array
   Dim arWks() As Worksheet
'count how many worksheets in the file, and re-dim the array
   Dim n As Integer
   Dim i As Integer
'count the sheets and minus one to set the bounds for the array
   n = Application.Sheets.Count - 1
   ReDim arWks(n)
'fill the worksheet array with all the sheets in the workbook
   For i = LBound(arWks) to UBound(arWks)
     Set arWks(i) = ActiveWorkbook.Sheets(i + 1)
   Next i
End Sub

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.

Sub TestObjArray() 
'define the array as a worksheet array 
   Dim arWks() As Worksheet 
'count how many worksheets in the file, and re-dim the array 
   Dim n As Integer 
   Dim i As Integer 
'count the sheets and minus one to set the bounds for the array 
   n = Application.Sheets.Count - 1 
   ReDim arWks(n) 
'fill the worksheet array with all the sheets in the workbook 
   For i = LBound(arWks) to UBound(arWks) 
      Set arWks(i) = ActiveWorkbook.Sheets(i + 1) 
   Next i
'do something to every sheet in the array 
   For i = LBound(arWks) to UBound(arWks) 
      arWks(i).Range("A1:H1").Font.Bold = True 
   Next i
End Sub

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!


Learn More!