To work with the arrays in VBA, you need to have an initial understanding of what are they all about. An array is simply a type of variable. We use it so that we can store the list of particular data of the same type.
Usually, normal variable stores one value, but arrays store multiple.
We will explain everything in more detail with the example below, where we will show how can we create an array of sheets in VBA.
Create Static Array of Sheets in VBA
For the first example, we will show how to create a static array of sheets in VBA. This means that we know exactly how many sheets we have and which ones we want to put into our array. To do this, we first need to open VBA by clicking on ALT + F11, then right-click on the left window of the module and choose Insert >> Module:
When we click on it, another window will be opened on the right side, and we will be able to write our code in it. Our code for static worksheets arrays is as follows:
|
1 2 3 4 5 6 7 8 |
Sub Static_Array() 'defining the worksheet array Dim ar(1 To 3) As Worksheet 'adding three sheets into an array Set ar(1) = Sheets(1) Set ar(2) = Sheets(2) Set ar(3) = Sheets(3) End Sub |
In the module itself, the code looks like this:
As it can be seen, we basically just define our array that is called ar and in brackets, we insert how many sheets will be in its scope (three in our case), and we declared the variable as a Worksheet.
For the final thing, we set every variable to be equal to a certain sheet, and that is it.
To check if we have a proper setup, we need to step through our code and turn on the Locals window. This window gives us access to the value of the variables and objects in our code. To turn it on, we simply need to go to View >> Locals window in our module:
We can move this window wherever we want by the drag-and-drop option.
To step through our code, we position ourselves in it and then click F8 on the keyboard. When we start with it, a yellow line will appear and it will move down the line whenever we click F8 again.
Using it, we will see in the Locals window that the proper values are being assigned to our variables once we step through this part of the code. In the picture below you will notice that, as we have gone beyond defining variable ar(1), it was set to be equal to Worksheets/Sheet1:
This particular code has three disadvantages: variables are static (non-changeable), we must have three sheets in order for it to work, and we are doing nothing once we define the variables.
We will change those things in the examples below.
Create a Dynamic Array of Sheets in VBA
We will first paste the code here for everyone’s use:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Sub Dynamic_Array() 'define the worksheet array Dim ar1() As Worksheet 'define two integer variables Dim n As Integer Dim i As Integer 'count the total number of sheets in the workbook and minus one and then redim the array n = Application.Sheets.Count - 1 ReDim ar1(n) 'define the bounds and then fill the worksheet array with all the sheets in the workbook For i = LBound(ar1) To UBound(ar1) Set ar1(i) = ActiveWorkbook.Sheets(i + 1) Next i End Sub |
This is what it looks like in our module:
Everything that is done in this code is explained in the comments that are marked in green color. To create the comment in the VBA, we simply need to insert the (‘) sign before the sentence in which we want to write a comment.
For the first part of the code, we define our array as a worksheet. After that, we declare two integer variables, variable n, and variable i:
|
1 2 3 |
Dim ar1() As Worksheet Dim n As Integer Dim i As Integer |
We then set the n variable to be equal to the total number of sheets in our workbook minus one sheet. After this part, we use the ReDim statement to resize our dynamic array which we already declared in the first part of the code, and to make it equal to our variable n:
|
1 2 |
n = Application.Sheets.Count – 1 ReDim ar1(n) |
At this point, everything that we just changed the dimension of our array, but we still did not assign any values to our variables. For this, we use For…Next statement:
|
1 2 3 |
For i = LBound(ar1) To UBound(ar1) Set ar1(i) = ActiveWorkbook.Sheets(i + 1) Next i |
In this statement, we define that our i variable is an equal range from LBound (lowest value of our ar1 array) to UBound (highest value of our ar1 array).
For the last part, we declare that our ar1 array is equal to the first sheet in our workbook. We use the part Next i to declare the second variable in the ar1 array to be equal to the second sheet and so on.
Why is this code dynamic? It is because it counts the number of sheets in our workbook (no matter how many there are) and then assigns these sheets to variables in our array.
We still, however, did not do any action to our sheets. We will do it below.
Actions with the Array of Sheets
For this, we will create another code, just in case, anyone has other ideas about what to do with the code above. We will create a code where every sheet in our workbook will have the text in the first cell: “First cell of every sheet is blue”, and we will color the first cell (cell A1) of every sheet in blue.
This is our code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Sub Array_Actions() 'define the worksheet array Dim ar1() As Worksheet 'define two integer variables Dim n As Integer Dim i As Integer 'count the total number of sheets in the workbook and minus one and then redim the array n = Application.Sheets.Count - 1 ReDim ar1(n) 'define the bounds and then fill the worksheet array with all the sheets in the workbook For i = LBound(ar1) To UBound(ar1) Set ar1(i) = ActiveWorkbook.Sheets(i + 1) Next i 'do a certain action to every sheet in the array For i = LBound(ar1) To UBound(ar1) ar1(i).Range("A1") = "First cell of every sheet Is blue" ar1(i).Range("A1").Interior.Color = rgbBlue Next i End Sub |
This is what our code looks like in the module:
This is the same code as above, with the difference we have a certain action at the end of it, that does what we said (adds the text and colors the cells with the text in blue):
|
1 2 3 4 |
For i = LBound(ar1) To UBound(ar1) ar1(i).Range("A1") = "First cell of every sheet Is blue" ar1(i).Range("A1").Interior.Color = rgbBlue Next i |
We use For…Next statement is to go through all the sheets in our workbook and do these actions. Once we run this code, by pressing F5, this is what we end up with in the first cell of every sheet:
The actions that we want to do with our sheets are limitless.