VBA Declare Array

What is Excel VBA Declare Array?

VBA Declare Array is very similar to declaring variables; however, in array variables, we must set the limit of the array, i.e., we must decide the size of the array well in advance while declaring the variable. An array is nothing but a collection of data of the same type.

The main difference between regular variables and array variables is that in array variables, we can store more than one value, i.e., up to the limit of the array. However, in regular variables, we can store only one value at a time.

Using the VBA Declare array, we can limit the declaration of the number of variables. For example, if you want to store five animal names in different variables, you have to declare five variables. However, with VBA declare array variables, we can limit the declaration to one.

Sub VBA_Declare_Array()
  Dim Animals(1 To 5) As String
End Sub

For example, look at the following code. Here, the variable name is “Animal,” and we have set the size of the array from 1 to 5. All five values should be of a particular data type only, in this case, String.

What is Excel VBA Declare Array?

VBA array is a vast concept; you need to spend a considerable amount of time to get used to it.
We can declare an array variable in one dimension as well as multiple dimensions by stating the size of the array in both dimensions.
We can use loops to work with multiple values and multi-dimensions.

How to Use Excel VBA Declare Array?

We will show you a step-by-step approach to using the VBA Declare array. Let’s follow the steps.

Start the sub-procedure by naming the macro.
Assume we must store 5 animal names. For this, we usually declare five variables.
However, using array variables we can declare only one and assign all the five to the same variable.

Let’s define a variable and assign a string data type to it with VBA declare array of strings values.
Once the variable is defined, we have to convert it into an array variable by fixing the size of the array.
Since we have to save five animal names, let us fix the size of the array variable to five.

As we can see from the yellow-colored area above, we have initiated the start and end points of the array variable inside the parenthesis.
To assign a value to the array variable, enter the variable name and the position of the array.
For example, since we are assigning the first value, we can use the one inside the parenthesis to assign the first value to the array variable, as shown below. So, now the first position of the array variable contains the animal name “Lion.”
Similar to the previous step, assign all the remaining four animal names by replacing the serial number in the parenthesis.

As we can see, the variable name is the same, but we have used the size of the array to assign values. Thus, we can use VBA Declare array method.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

Examples of Excel VBA Declare Array

We will show you different sent of examples to understand the concept of VBA Declare Array variables.

Example #1 – Static Array Variables

There are multiple types of arrays in VBA. One such array is the static array, i.e., an array whose size is pre-defined while declaring the array variable. Hence, the size cannot be changed inside the procedure. For example, take a look at the following code.

In this example, the array variable “Cities” has a length of three, from 1 to 3. It can hold up to 3 string values only. Hence, it is called as a static array.

On a similar note, let’s assign some values to the array variable.

Sub VBA_Declare_Array_Ex1()
Dim Cities(1 To 3) As String
Cities(1) = “Washington D C”
Cities(2) = “Kensigton Oval”
Cities(3) = “Sydney Thunder”
Cities(4) = “New York”
MsgBox Cities(1) & vbNewLine & Cities(2) & vbNewLine & Cities(3) & vbNewLine & Cities(4)
End Sub

Even though the size of the array is 3, we have assigned a fourth value to the array. Let’s execute the code and see what happens.

Because the size of the array is out of range, we have got the error Run-time error ‘9’: Subscript out of range. Remove the fourth value and see what happens.

Sub VBA_Declare_Array_Ex1()
Dim Cities(1 To 3) As String
Cities(1) = “Washington D C”
Cities(2) = “Kensigton Oval”
Cities(3) = “Sydney Thunder”
MsgBox Cities(1) & vbNewLine & Cities(2) & vbNewLine & Cities(3)
End Sub

When we execute the code, we can see the array values in a message box.

Excel VBA Declare Array - Example 1 - Step 3

It’s that simple! Here, when we use a fixed length it becomes a static array and we assign values beyond the set limit.

Example #2 – Dynamic Array (VBA Declare Array without Size)

In the previous example, we have seen that once the size of an array is set, it becomes a static array. However, we can also make the array dynamic by not assigning any size at the time of declaring the variable.

With the dynamic array variable, we can change the size of the array during run time. For that, we must declare the variable without assigning the size to the variable.

For example, look at the following code.

As we can see inside the parenthesis, we have not assigned any size to the array variable. We can use the “ReDim” statement to decide the size of the array after it has been created.

Excel VBA Declare Array - Example 2 - Step 2

Now, for array size 5, we can assign up to 6 values if we start from 0, or we can assign only five values if we start assigning from 1.

The beauty of using the dynamic array is that we can resize them at any time. However, all the existing values will be erased once we use the ReDim statement for the second time. Hence, we can use the ReDim Preserve statement instead of just the ReDim statement. ReDim Preserve will preserve all the existing values.

Example #3 – Two Dimensional Arrays

So far, we have seen only the one-dimensional array. However, when we have to deal with both rows and columns, then we must use a two-dimensional array. Let’s explore more on this. For example, look at the following data in Excel.

We have 7 rows (excluding headers in the first row) and 3 columns of data. To store all the values of a column, we can define one array variable with the size equal to the number of rows in the data table in excel.

Since we have seven rows of data, we have set the size of the array to be 7. However, along with the seven rows, we have three columns. Hence, instead of defining another array variable, let’s set the size of the second dimension of the array to column number size.

Now this has become a two-dimensional array variable.

We can store the Excel table values in this array variable. For that we need to use loops. To initiate the loop, let’s declare two more variables.

We will copy the table values from one sheet to another using the following code.

Sub VBA_Declare_Array_Ex3()
‘Define a variable with two dimension
‘First dimension is equal to number of rows
‘Second dimension is equal to number of columns
Dim k(1 To 7, 1 To 3) As String
‘Define two variables to initiate the loop
Dim i As Long
Dim j As Long
‘Initiatte the loop for first column values
For j = 1 To 3
‘Initiate another loop to get row values
For i = 2 To 7
‘We have values in the worksheet Insights Summary, hence select this worksheet
Worksheets(“Insights Summary”).Select
k(i, j) = Cells(i, j).Value
‘We need to paste the values to Target Sheet, hence select this worksheet
Worksheets(“Target Sheet”).Select
Cells(i, j).Value = k(i, j)
Next i
Next j
End Sub

Now, this code will copy the data from the “Insights Summary” worksheet to the “Target Sheet.”

VBA Declare Array in Excel - Example 3 - Step 5

In this way, we can make use of array variables and avoid declaring multiple variables to store the data.

Important Things to Note

When the array size in VBA is fixed, we can assign as many values as the specified size to the array variable. If we try to assign more values, then we will end up getting subscript out of range error.
If we use the ReDim statement more than once, then it is likely that we will lose out on existing values. However, if we can use ReDim Preserve, then we can retain the existing values of the array variable.
Array variable name should be exactly equal to the name while declaring it. Otherwise, we will get the error.
In a static array, once the size of the variable is set, we cannot change it during the runtime.

Frequently Asked Questions (FAQs)

1. Can I declare an array without specifying its size in VBA?

Yes, we can declare an array without specifying its size, and it is called a dynamic array. However, we can decide on the size of the array during the run time by using the ReDim statement later on within the same sub-procedure.

2. How do I declare an array of strings in VBA?

An array of strings can be declared as follows in VBA.

Dim Str_Var(7) As String

The array index starts at 0. You can also declare a dynamic array without specifying the size.

Dim dynamicArray() As String

3. Why is declare an array in VBA not working?

The likely reason the VBA declare array is not working is because of its size. For example, look at the following code.

Dim MyVar (1 to 2) As String

For the variable “MyVar,” we can assign up to 2 string values; if we try to add the 3rd string value, then it will end up giving the subscript out-of-range error.

Download Template

This article must be helpful to understand the VBA Declare Array, with its features and examples. You can download the template here to use it instantly.

VBA Declare Array Download

VBA Declare Array