Table Of Contents
Excel VBA Declare Array
The declaration of the array in VBA is similar to that of variables performed by the same dim statement or static public or private statement. The only difference between declaring an array and declaring a variable is that while declaring an array, we have to provide a size of an array which is the upper bound of the array and the lower bound of the array.
In VBA Code, we can declare a single variable array that can hold the number of variables instead of single variables. It can help to reduce the number of lines in the code.
The array is a variable that can hold more than one value, unlike regular variables that can hold only one value at a time. The array is an advanced version of declaring variables in VBA. For example, imagine a situation where you want to assign 5 students' names to variables. In general practice, we declare five variables. For all five variables, we assign individual student names one by one. Below is the example code of the same.
Code:
Sub Array_Example() Dim Student1 As String Dim Student2 As String Dim Student3 As String Dim Student4 As String Dim Student5 As String End Sub
How about the idea of declaring a single variable array that can hold all the student names? Instead of declaring so many variables.
Yes, this is possible by declaring the array in VBA.
Examples
Example #1
To declare, we need not do any special VBA coding. Rather, we need to follow simple concepts.
First, start the subprocedure.
Code:
Sub Array_Example() End Sub
Now, as usual, declare a variable as a string.
Code:
Sub Array_Example() Dim Student As String End Sub
Once the variable is declared, ensure how many values it should hold. In this case, we want to store five students' names, so now we need to fix the array size, i.e., 1 to 5. Then, supply the same thing to the variable in brackets.
Code:
Sub Array_Example() Dim Student(1 To 5) As String End Sub
Now, for this single variable, we can store 5 student names.
Code:
Sub Array_Example() Dim Student(1 To 5) As String Student(1) = "John" Student(2) = "Peter" Student(3) = "Ricky" Student(4) = "Michael" Student(5) = "Anderson" End Sub
Look how many lines we have reduced by declaring the variable as an array. This is one way of doing it. We can still shorten this code by enclosing this inside the loops in VBA.
For example, we have the same five names in worksheet cells.3
We want to show these numbers in the message box in VBA. So let's declare one more variable for loops as an Integer data type.
Code:
Sub Array_Example() Dim Student(1 To 5) As String Dim K As Integer End Sub
As usual, we have retained the array variable as 1 to 5 sizes.
Now, open FOR NEXT loop in VBA. Since we have five names, enter the limit as 1 to 5.
Code:
Sub Array_Example() Dim Student(1 To 5) As String Dim K As Integer For K = 1 To 5 Next K End Sub
To assign values to the array variable, we need not follow the previous way of showing Student(1) and Student(2) for numbers position supply loops variable "k."
Code:
Sub Array_Example() Dim Student(1 To 5) As String Dim K As Integer For K = 1 To 5 Student(K) = Next K End Sub
We need the values from the worksheet for this array variable, so using CELLS property gets the values from the worksheet.
Code:
Sub Array_Example() Dim Student(1 To 5) As String Dim K As Integer For K = 1 To 5 Student(K) = Cells(K, 1).Value Next K End Sub
Now, through the message box, show the value of the array variable.
Code:
Sub Array_Example() Dim Student(1 To 5) As String Dim K As Integer For K = 1 To 5 Student(K) = Cells(K, 1).Value MsgBox Student(K) Next K End Sub
Now, run the code. Again, in the message box, we will see the first name. Again press "OK" to see the second name. Like this, by pressing "OK," we can see all the five names.
Example #2 - Two Dimensional Arrays
We have seen above how the array works. Now, we will see two-dimensional arrays. The two-dimensional arrays concentrate on both rows and columns.
In the above example, we have determined the array's size as 1 to 5. It either concentrates on rows or columns.
By using two-dimensional arrays, we can concentrate on both rows and columns. For this, we need to enclose two loops.
First, define the variable, then later, we will decide on the size of the array.
Code:
Sub Two_Array_Example() Dim Student As String End Sub
First, decide on row size, then decide the column length.
Code:
Sub Two_Array_Example() Dim Student(1 To 5, 1 To 3) As String End Sub
We have structured student names, marks, and grade status data.
Now, come back to the coding window.
Declare two more variables for a loop.
Code:
Sub Two_Array_Example() Dim Student(1 To 5, 1 To 3) As String Dim K As Integer, J As Integer End Sub
Now enclose the loop, as shown below.
Code:
Sub Two_Array_Example() Dim Student(1 To 5, 1 To 3) As String Dim k As Integer, J As Integer For k = 1 To 5 For J = 1 To 3 Worksheets("Student List").Select Student(k, J) = Cells(k, J).Value Worksheets("Copy Sheet").Select Cells(k, J).Value = Student(k, J) Next J Next k End Sub
What this will do is it will copy the data from the "Student List" sheet and paste it into the "Copy Sheet."
Things to Remember
- The array is a vast concept. It is just an introductory part.
- We must learn advanced coding skills to understand the array declaration.
- The more we use arrays in the code, the more we will get used to it.