VBA Array of Objects
In this Article
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:
|
1 |
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.
|
1 2 3 4 5 6 7 8 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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!
