Excel VBA Declare Array
There are some instances where we need to declare multiple variables. And in the process of declaring those variables, next we need to assign them with some value. This becomes very irritating when we are performing the same task again and again. To avoid such situations, in VBA we have Declare Array in variable name. Declare Array in Excel VBA is used when we need to declare multiple variables in a sequence. So, instead of writing the multiple variables one by one, we can declare one variable in such a way that it would satisfy the need of the situation. For example, if we want to assign 5 Employees names to variables, in a regular way we would do this one by one declaring the 5 variable for each employee name as shown below.
Now instead of writing and declaring a variable for each of the 5 employees, we can reduce this step to one line of code with VBA Declare Array.
How to Declare an Array in Excel VBA?
We will learn how to Declare an Array in Excel using the VBA Code.
VBA Declare Array – Example #1
In this example, we will see how simple it is to assign one variable to more than 1 value in VBA. For this, follow the below steps:
Step 1: Open a Module in VBA from Insert menu tab, as shown below.
Step 2: Write the subprocedure in the name of VBA Declare Array as shown below. We can choose any name as per our requirement.
Code:
Sub VBA_DeclareArray() End Sub
Step 3: Now declare a variable in which we will store the value of 5 Employees in a String. Let that variable be Employee(1 To 5) for 5 Employees.
Code:
Sub VBA_DeclareArray() Dim Employee(1 To 5) As String End Sub
Step 4: Now we can put any name under each Employee variable starting from Employee(1) to Employee(5).
Code:
Sub VBA_DeclareArray() Dim Employee(1 To 5) As String Employee(1) = "Ashwani" Employee(2) = "Aniket" Employee(3) = "Anand" Employee(4) = "Deepinder" Employee(5) = "Rosy" End Sub
See, how we have reduced the 5 lines of declaring variable to 1 line.
VBA Declare Array – Example #2
In this example, we will put the employee names as seen in example-1 into the loop.
Follow the below steps to declare arrays in excel VBA.
Step 1: Write the subprocedure in the name of VBA Declare Array as shown below.
Code:
Sub VBA_DeclareArray2()
End Sub
Step 2: Once done, now declare 2 variables in that. One for Employee details which we have seen in example-1 and other for cell out as Integer.
Code:
Sub VBA_DeclareArray2() Dim Employee(1 To 5) As String Dim A As Integer End Sub
Step 3: Now open a For-Next loop as shown below.
Code:
Sub VBA_DeclareArray2() Dim Employee(1 To 5) As String Dim A As Integer For Next A End Sub
Step 4: Now in the loop write the condition were selecting the cells from 1 to 5.
Code:
Sub VBA_DeclareArray2() Dim Employee(1 To 5) As String Dim A As Integer For A = 1 To 5 Next A End Sub
Step 5: Now select the value of Employee starting from cell 1 which is defined under variable A.
Code:
Sub VBA_DeclareArray2() Dim Employee(1 To 5) As String Dim A As Integer For A = 1 To 5 Employee(A) = Cells(A, 1).Value Next A End Sub
Step 6: Now there are two ways of seeing the output. One by MsgBox and others by Debug Print. We have been always using Msgbox which is the easiest approach of doing. Now we will use Debug Print to see the Employee names in one go.
Code:
Sub VBA_DeclareArray2() Dim Employee(1 To 5) As String Dim A As Integer For A = 1 To 5 Employee(A) = Cells(A, 1).Value Debug.Print Employee(A) Next A End Sub
Step 7: Once done, now open the immediate window as well to see the output. We will be opening the immediate window from the view menu bar as shown below.
Step 8: Once done, run the code by pressing the F5 key or by clicking on the Play button. We will see, in just one click all the Employee names are fetched from column A cell 1 to 5 into the Immediate window.
This is also known as One Dimensional Array Loop.
VBA Declare Array – Example #3
In this example, we will see how to declare an array in the form of a table. For that, we have a table with the employee details. Here the table consists of the name, id, and designation of the employees. The sheet is named as Sheet1 as default.
Follow the below steps to declare Arrays in Excel VBA.
Step 1: Consider the variable defined in example-1 here as well. But along with rows, insert column numbers as well to form a 2D matrix.
Code:
Sub VBA_DeclareArray3() Dim Employee(1 To 5, 1 To 3) As String Dim A As Integer End Sub
Step 2: Define another variable for storing the column sequence as Integer as shown below.
Code:
Sub VBA_DeclareArray3() Dim Employee(1 To 5, 1 To 3) As String Dim A As Integer Dim B As Integer End Sub
Step 3: Similar to example-1, we will use the For-Next loop here as well. But along with variable A (Rows), we will use the For-Next loop for variable B (Columns) as well. Here, the count of columns is 3.
Code:
Sub VBA_DeclareArray3() Dim Employee(1 To 5, 1 To 3) As String Dim A As Integer Dim B As Integer For A = 1 To 5 For B = 1 To 3 End Sub
Step 4: Now select the sheet from where we need to fetch the array matrix. Here, that sheet is Sheet1.
Code:
Sub VBA_DeclareArray3() Dim Employee(1 To 5, 1 To 3) As String Dim A As Integer Dim B As Integer For A = 1 To 5 For B = 1 To 3 Worksheets("Sheet1").Select End Sub
Step 5: Now select the values under the variable defined by considering the Employee (A, B) as variable here.
Code:
Sub VBA_DeclareArray3() Dim Employee(1 To 5, 1 To 3) As String Dim A As Integer Dim B As Integer For A = 1 To 5 For B = 1 To 3 Worksheets("Sheet1").Select Employee(A, B) = Cells(A, B).Value End Sub
Step 6: Similarly again, we will select the sheet where we need to paste the data from Sheet1. Here the sheet is Sheet2 and again consider the Cells(A, B) as input for Sheet2 which has the details from Sheet1.
Code:
Sub VBA_DeclareArray3() Dim Employee(1 To 5, 1 To 3) As String Dim A As Integer Dim B As Integer For A = 1 To 5 For B = 1 To 3 Worksheets("Sheet1").Select Employee(A, B) = Cells(A, B).Value Worksheets("Sheet2").Select Cells(A, B).Value = Employee(A, B) End Sub
Step 7: Close both the For-Next loops started for variables A and B as shown below.
Code:
Sub VBA_DeclareArray3() Dim Employee(1 To 5, 1 To 3) As String Dim A As Integer Dim B As Integer For A = 1 To 5 For B = 1 To 3 Worksheets("Sheet1").Select Employee(A, B) = Cells(A, B).Value Worksheets("Sheet2").Select Cells(A, B).Value = Employee(A, B) Next B Next A End Sub
Step 8: Now run the code by pressing the F5 key or by clicking on the Play button. We will see, the data from Sheet1 is now fetched to Sheet2 in the same format as it was there in Sheet1.
Pros of Excel VBA Declare Array
- The code is easy to write and implement.
- This works like copying the data and pasting it anywhere as per our needs.
- VBA Declare Array works with both 1D and 2D arrays.
Things to Remember
- The examples shown in the articles are the best for beginners who have just started using Arrays. VBA Declare Array is way more functional variable to have hands-on.
- VBA Declare Array can be understood more deeply if we use it more often.
- We can use Array for creating any database or matrix.
- Once done, please save the file in macro enable excel format to retain the written code.
- There is no limit of having variables in using VBA Declare Array. One variable can have any number of values in it.
Recommended Articles
This is a guide to VBA Declare Array. Here we discuss how to Declare Array in Excel using VBA code along with practical examples and downloadable excel template. You can also go through our other suggested articles –
