Excel VBA Declare Array
Declaration of array in VBA is very similar to that of variables it is done by the same dim statement or static public or private statement, the only difference in declaring an array and declaring a variable is that while declaring an array we have to provide a size of an array which is 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 declaring single variables. This can help to reduce the number of lines in the code.
The array is a kind of 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 VBADeclaring Variables In VBAVariable declaration is necessary in VBA to define a variable for a specific data type so that it can hold values; any variable that is not defined in VBA cannot hold values.. For example, imagine a situation where you want to assign 5 students names to variables, and in general practice, we declare five variables for all the five variables we assign individual student names one by one; below is the example code of the same.
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
Instead of declaring so many variables, how about the idea of declaring a single variable array which can hold all the student names.
Yes, this is possible by declaring the array in VBAArray In VBAArray Variable is a variable which stores multiple values in a single variable. To use an array variable in VBA, you must first define it. You can define the array variable with or without its length..
To declare, we need not do any special VBA codingVBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.. Rather, we need to follow simple concepts.
First, start the subprocedure.
Sub Array_Example() End Sub
Now, as usual, declare a variable as a string.
Sub Array_Example() Dim Student As String End Sub
Once the variable is declared, now make sure how many values it should hold. In this case, I want to store five students’ names, so now we need to fix the array size i.e., 1 to 5. Supply the same thing to variable in brackets.
Sub Array_Example() Dim Student(1 To 5) As String End Sub
Now for this single variable, we can store 5 student names.
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. We can still shorten this code by enclosing this inside the loops in VBALoops In VBALoops are commonly used in all of the programming languages, where there is a certain need or a criteria when we need a certain code to run a certain times. In VBA, there are several different types of loops, including Do while, Do till, For Loop, and For each loop. These loops help code to execute until the condition is met..
Now for an example, the same five names I have in worksheet cells.3
Now I want to show these numbers in the message box in VBAMessage Box In VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.; ok, let’s declare one more variable for loops as Integer data type.
Sub Array_Example() Dim Student(1 To 5) As String Dim K As Integer End Sub
As usual, I have retained the array variable as 1 to 5 sizes.
Now open FOR NEXT loop in VBAOpen FOR NEXT Loop In VBAAll programming languages make use of the VBA For Next loop. After the FOR statement, there is a criterion in this loop, and the code loops until the criteria are reached. , and since we have five names enter the limit as 1 to 5.
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), Student(2) like this for numbers position supply loops variable “k.”
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
For this array variable, we need the values from the worksheet, so using CELLS propertyUsing CELLS PropertyCells are cells of the worksheet, and in VBA, when we refer to cells as a range property, we refer to the same cells. In VBA concepts, cells are also the same, no different from normal excel cells. gets the values from the worksheet.
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.
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. 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 to dimensional arrays. Two-dimensional arrays concentrate on both rows and columns.
In the above example, we have determined the array’s size as 1 to 5; this either concentrates on rows or columns.
By using two-dimensional arrays, we can concentrate on both rows and columnsRows And ColumnsA cell is the intersection of rows and columns. Rows and columns make the software that is called excel. The area of excel worksheet is divided into rows and columns and at any point in time, if we want to refer a particular location of this area, we need to refer a cell.. For this, we need to enclose two loops.
First, define the variable then late we will decide about the size of the array.
Sub Two_Array_Example() Dim Student As String End Sub
First, decide row size then decide the column length.
Sub Two_Array_Example() Dim Student(1 To 5, 1 To 3) As String End Sub
For this, I have structured the data for student names, marks, and grade status.
Now come back to the coding window.
Declare two more variables for a loop.
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.
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 in “Copy Sheet.”
Things to Remember
- The array is a vast concept; this is just an introductory part.
- You need advanced coding skills to understand the array declaration.
- The more you use arrays in your code, the more you will get used to it.
This has been a guide to VBA Declare Array. Here we learn how to declare one dimensional and two-dimensional arrays in VBA along with examples and a downloadable excel template. Below are some useful excel articles related to VBA –