VBA Declare Array

Updated on January 1, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

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 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.read more. 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 VBAArray In VBAA VBA array in excel is a storage unit or a variable which can store multiple data values. These values must necessarily be of the same data type. This implies that the related values are grouped together to be stored in an array variable.read more.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Examples

You can download this VBA Declare Array Excel Template here – VBA Declare Array Excel Template

Example #1

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.read more. Rather, we need to follow simple concepts.

First, start the subprocedure.

Code:

Sub Array_Example()

End Sub
VBA Declare Array Example 1

Now, as usual, declare a variable as a string.

Code:

Sub Array_Example()

  Dim Student As String

End Sub
VBA Declare Array Example 1-1

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
VBA Declare Array Example 1-2

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
VBA Declare Array Example 1-3

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 VBALoops In VBAA VBA loop in excel is an instruction to run a code or repeat an action multiple times.read more.

For example, we have the same five names in worksheet cells.3

VBA Declare Array Example 1-4

We 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.read more. 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
VBA Declare Array Example 1-5

As usual, we 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. read more. 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
VBA Declare Array Example 1-6

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
VBA Declare Array Example 1-7

We need the values from the worksheet for this array variable, 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.read more 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
VBA Declare Array Example 1-8

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
Example 1-9

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 1-10

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 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.read more. 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.

Two Dimensional Example 2

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.”

Two Dimensional Example 2-1.png

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.

Recommended Articles

This article is a guide to VBA Declare Array. Here, we learn how to declare one-dimensional and two-dimensional arrays in VBA, examples, and a downloadable Excel template. Below are some useful Excel articles related to VBA: –