Arrays are simply a collection of values or items that are stored in continuous memory locations. They’re a data structure in which a group of items stored with the same data type.

This type of structure is highly useful in the programming world as it helps in organizing, sorting and retrieving data.

I’m going to show you how to redim a multidimensional array later in this article, but first I’m going to give you a couple examples to make sure you understand all the concepts correctly.

One dimensional array

The best way to think about this structure is as a list of items one below the other. An array is one column of elements that are of the same datatype. The index of an array always starts with 0; the elements are recognized using their respective indexes.

Here is an example of an array in VBA that has a list of foods. You can see that the five items are stored in indexes starting from 0 to 4 (5 total items). The “lbound” of an array is the index value with which the array items start and the “ubound” value of an array is the index with which the array list ends.

Sub array_snacks()

'declaring variable
Dim arr_snacks1(5)

'initialize array elements for the array
 arr_snacks1(0) = "Burger"
 arr_snacks1(1) = "Pizza"
 arr_snacks1(2) = "Pasta"
 arr_snacks1(3) = "Noodles"
 arr_snacks1(4) = "Sandwich"

'print all elements of array
For i = LBound(arr_snacks1) To UBound(arr_snacks1)
    Debug.Print arr_snacks1(i)
Next

End Sub

Output:

Burger

Pizza

Pasta

Noodles

Sandwich

Two dimensional array

These arrays can be imagined as values spread across rows and columns in two dimensions. The two dimensions can be indicated with the X and Y coordinates.

Declaration

To declare an array of more than 1 dimension, we use commas to separate the dimensions.

Eg: Dim arr_sample(3,2) as Integer

This declares an array of 3 rows and 2 columns that can hold integer values.

Example of a two dimensional array

Sub array_2d_snacks()

'declaring and defining size of an array
'4 means 5 rows starting from 0 to 4 and 1 means 2 columns starting from 0 to 1
Dim arr_sna(4, 1) As Variant

'initialize array elements
 arr_sna(0, 0) = "Burger"
 arr_sna(0, 1) = 5
 arr_sna(1, 0) = "Noodles"
 arr_sna(1, 1) = 7
 arr_sna(2, 0) = "Sandwich"
 arr_sna(2, 1) = 9
 arr_sna(3, 0) = "Pasta"
 arr_sna(3, 1) = 6
 arr_sna(4, 0) = "Pizza"
 arr_sna(4, 1) = 10
 
'print all elements
For i = LBound(arr_sna) To UBound(arr_sna)
    
    Debug.Print "Food Item : " & arr_sna(i, 0)
    Debug.Print "Price : " & arr_sna(i, 1)
    Debug.Print "End of record"
Next

End Sub

Below is the structure of the 2D array defined by the code above.

1. Column 0 is “Food Items” and
2. Column 1 is “Price of the item”

	Column 0	Column 1
Row 0	Burger	5
Row 1	Noodles	7
Row 2	Sandwich	9
Row 3	Pasta	6
Row 4	Pizza	10

Multidimensional Arrays

Arrays with more than 1 dimension are called multidimensional arrays. So, the 2D arrays we discussed above also fall under this category.

Generally, two or three dimensions can usually meet our purposes for programming. But an array can have a maximum of 32 dimensions!

Declaration – with indexes

As mentioned earlier in this article, the dimensions are separated by commas while declaring the array object. There is also another way of declaring the array object, by specifying the lbound and ubound. Let me explain this with an example.

Dim arr_sample( 1 to 5 , 1 to 2 ) as String

In this example, we say that index for the the items of the first dimension should start with 1 instead of 0 and procced with 5 items through to the index 5. This means that the lbound is 1 and ubound is 5 for the rows. The second dimension stands for column. The index should start with 1 and end with 2. The lbound is equal to 1 and ubound is 2.

Complete example of a 2D array with a “nested for” loop

• Here is an example of a 2D array with student names and their exam results.

Sub array_2d_demo()

'declaring and defining size of an array
'1 to 5 means 5 rows starting from 1 to 5 and 1 to 2 means 2 columns starting from 1 to 2
Dim arr_stu(1 To 5, 1 To 2) As Variant

'initialize array elements

 arr_stu(1, 1) = "Dave"
 arr_stu(1, 2) = "Fail"
 arr_stu(2, 1) = "Trumpo"
 arr_stu(2, 2) = "Pass"
 arr_stu(3, 1) = "Vincent"
 arr_stu(3, 2) = "Pass"
 arr_stu(4, 1) = "Rose Mary"
 arr_stu(4, 2) = "Pass"
 arr_stu(5, 1) = "Eliza"
 arr_stu(5, 2) = "Fail"
 
' print all elements
For i = 1 To 5   ' iterate through all rows
    For j = 1 To 2   ' iterate through all columns
        Debug.Print arr_stu(i, j)
    Next j
    Debug.Print "End of record"
Next

End Sub

• Here is an example of a three dimensional array.

You can think of this like a Rubik’s cube. It is a 2D array with layers from front to back (one behind the other). Let us assume it’s storing student information again.

Sub array_2d_demo()

' declaring and defining size of an array
' 1 to 5 means 5 rows starting from 1 to 5 and 1 to 2 means 2 columns starting from 1 upto 2
' 1 to 2  as the last parameter in the array object indicates 2 layers of the 2D array which makes it a 3D ( 3 dimensional ) array.
Dim arr_stu(1 To 5, 1 To 2, 1 To 2) As Variant

' initialize the array elements
 arr_stu(1, 1, 1) = "Dave"
 arr_stu(1, 1, 2) = "Stu_001"
 arr_stu(1, 2, 1) = "Fail" ' Result of first periodic test of Stu_001
 arr_stu(1, 2, 2) = "Pass" ' Result of second periodic test of Stu_001
 
 arr_stu(2, 1, 1) = "Diana"
 arr_stu(2, 1, 2) = "Stu_002"
 arr_stu(2, 2, 1) = "Fail" ' Result of first periodic test of Stu_002
 arr_stu(2, 2, 2) = "Pass" ' Result of second periodic test of Stu_002
 
 arr_stu(3, 1, 1) = "Elizabeth"
 arr_stu(3, 1, 2) = "Stu_003"
 arr_stu(3, 2, 1) = "Pass"  ' Result of first periodic test of Stu_003
 arr_stu(3, 2, 2) = "Pass"  ' Result of second periodic test of Stu_003
 
 arr_stu(4, 1, 1) = "Ricard"
 arr_stu(4, 1, 2) = "Stu_004"
 arr_stu(4, 2, 1) = "Fail"  ' Result of first periodic test of Stu_004
 arr_stu(4, 2, 2) = "Fail"  ' Result of second periodic test of Stu_004
 
 arr_stu(5, 1, 1) = "Trump"
 arr_stu(5, 1, 2) = "Stu_005"
 arr_stu(5, 2, 1) = "Pass"  ' Result of first periodic test of Stu_005
 arr_stu(5, 2, 2) = "Fail"  ' Result of second periodic test of Stu_005
 
' print all elements
For i = 1 To 5
    For j = 1 To 2
        For k = 1 To 2
            Debug.Print arr_stu(i, j, k)
        Next k
    Next j
    Debug.Print "End of record"
Next
End Sub

Here is the structure of the 3D array shown above.

1st layer from the front
	Column 1	Column 2
row 1	Dave	Fail
row 2	Diana	Fail
row 3	Elizabeth	Pass
row 4	Ricard	Fail
row 5	Trump	Pass

2nd layer from the front
	Column 1	Column 2
row 1	Stu_001	Pass
row 2	Stu_002	Pass
row 3	Stu_003	Pass
row 4	Stu_004	Fail
row 5	Stu_005	Fail

Resizing arrays

Resizing an array is nothing but modifying the size of an array that is already declared with or without dimensions. “ReDim” is the keyword used to do this.

Set the size of arrays just after declaration

' Declaration of array variable
Dim arr() As Variant

' Defining length of array
ReDim arr(8)

In this piece of code, we have just declared an array variable without mentioning the size. So, the “ReDim” keyword in the next line used to define its size. From then on, 8 elements of the variant type could be stored in the array object. These would have indexes starting from 0 to 7.

Increase the size of an array

Here is a piece of code that uses ReDim keyword to re-size an array. Because it’s resized, the contents of the array get erased. So, the array is empty from index 0 to 4 again. If needed, the values need to be initialized with the indexes of the array again.

Sub array_cars()

' declaring variable
Dim arr_cars1() As Variant
ReDim arr_cars1(5)

' initialize array elements for one array
 arr_cars1(0) = "Benz"
 arr_cars1(1) = "Maruthi"
 arr_cars1(2) = "Hyundai"
 arr_cars1(3) = "Ford"
 arr_cars1(4) = "Nissan"

'  increase the size of the array
ReDim arr_cars1(6)
 arr_cars1(5) = "Toyota"
    
' print all elements of first array
For i = LBound(arr_cars1) To UBound(arr_cars1)
    Debug.Print arr_cars1(i)
Next

End Sub

Output:

If we need the elements to be added to the array again, we need to insert this snippet again between the lines “ReDim arr_cars1(6)” and “arr_cars1(5) = “Toyota””.

arr_cars1(0) = "Benz"
 arr_cars1(1) = "Maruthi"
 arr_cars1(2) = "Hyundai"
 arr_cars1(3) = "Ford"
 arr_cars1(4) = "Nissan"

Decrease the size of an array

The size of an array can be reduced after assigning values to the array elements but, all the existing values will be cleared.

The example below shows this:

Sub array_cars()

'declaring variable
Dim arr_cars1() As Variant
ReDim arr_cars1(5)

' initialize array elements for the array ( all six elements are assigned values )
 arr_cars1(0) = "Benz"
 arr_cars1(1) = "Maruthi"
 arr_cars1(2) = "Hyundai"
 arr_cars1(3) = "Ford"
 arr_cars1(4) = "Nissan"
 arr_cars1(5) = "Toyota"
 
Debug.Print "Print the limits before changing size of the array"
Debug.Print UBound(arr_cars1)
Debug.Print LBound(arr_cars1)

' decrease the size of the array
ReDim arr_cars1(3)
Debug.Print "Print the limits after changing size of the array"
Debug.Print UBound(arr_cars1)
Debug.Print LBound(arr_cars1)

' initialize values again as the size has been modified and values are erased
 arr_cars1(0) = "Benz"
 arr_cars1(1) = "Maruthi"
 arr_cars1(2) = "Hyundai"
 arr_cars1(3) = "Ford"

Debug.Print "Print the limits after changing size of the array and assigning values again"
Debug.Print UBound(arr_cars1)
Debug.Print LBound(arr_cars1)
Debug.Print "Start printing"
' print all elements of first array
For i = LBound(arr_cars1) To UBound(arr_cars1)
    Debug.Print arr_cars1(i)
Next
Debug.Print "End Printing"
End Sub

The comments in the code explain what each line does and how the “ReDim” statement makes a difference in the size of the array.

The ReDim keyword with Multidimensional arrays

In case of a multidimensional array, the ReDim keyword can help in two aspects.

1. Increase or decrease the number of dimensions
2. Increase or decrease the length of a dimension

Fixed Arrays

These are the arrays in which the dimensions are specified while declaring with the Dim keyword.

Eg:

Dim Cars_arr(3,5) as String

Dim Cars_arr(1 to 4, 1 to 3) as String

Dynamic Arrays

These are arrays in which the dimensions are not specified during declaration using the Dim keyword.

Eg:

Dim fibo_arr() as Integer

Examples of Resizing

An example of a fixed array in which the last dimension cannot be changed:

Sub array_2d_redim_demo()

'declaring and defining the size of an array
Dim arr_sna(4, 1) As Variant

'initialize array elements
 arr_sna(0, 0) = "Burger"
 arr_sna(0, 1) = 5
 arr_sna(1, 0) = "Noodles"
 arr_sna(1, 1) = 7
 arr_sna(2, 0) = "Sandwich"
 arr_sna(2, 1) = 9
 arr_sna(3, 0) = "Pasta"
 arr_sna(3, 1) = 6
 arr_sna(4, 0) = "Pizza"
 arr_sna(4, 1) = 10
 
 ReDim arr_sna(4, 2)
 
 
'print all elements
For i = LBound(arr_sna) To UBound(arr_sna)
    
    Debug.Print "Food Item : " & arr_sna(i, 0)
    Debug.Print "s : " & arr_sna(i, 1)
    Debug.Print "End of record"
Next

End Sub

Even if the Redim statement is used immediately after declaration in a fixed array, the error above would be thrown.

'declaring and defining the size of an array
Dim arr_sna(4, 1) As Variant
ReDim arr_sna(4, 2)

Dynamic array in which dimensions are changed:

Sub array_2d_redim_demo()

' step 1 : declaring a dynamic array
Dim arr_sna() As Variant

' step 2 : defining the size of the array using redim keyword
ReDim arr_sna(4, 1)

' step 3 : initialize array elements for the first time

 arr_sna(0, 0) = "Burger"
 arr_sna(0, 1) = 5
 arr_sna(1, 0) = "Noodles"
 arr_sna(1, 1) = 7
 arr_sna(2, 0) = "Sandwich"
 arr_sna(2, 1) = 9
 arr_sna(3, 0) = "Pasta"
 arr_sna(3, 1) = 6
 arr_sna(4, 0) = "Pizza"
 arr_sna(4, 1) = 10
 
 ' step 4 : print all data and check
 Debug.Print "step 4"
 Debug.Print "Food items" & "              " & " Price"
 For i = LBound(arr_sna) To UBound(arr_sna)
    Debug.Print arr_sna(i, 0) & "            " & arr_sna(i, 1)
 Next

 ' Step 5 : redefine size of the same array trying to change both the dimensions
 ReDim arr_sna(7, 2)
 
 ' step 6 : all data would have got erased. confirm this by printing
 Debug.Print "step 6"
 Debug.Print "Food items" & "              " & " Price" & "              " & " Rating"
 For i = LBound(arr_sna) To UBound(arr_sna)
    Debug.Print arr_sna(i, 0) & "            " & arr_sna(i, 1) & "            " & arr_sna(i, 2)
 Next
 
 ' step 7 : initialize array elements as all data would have got cleared.
 arr_sna(0, 0) = "Burger"
 arr_sna(0, 1) = 5
 arr_sna(0, 2) = 3
 arr_sna(1, 0) = "Noodles"
 arr_sna(1, 1) = 7
 arr_sna(1, 2) = 4
 arr_sna(2, 0) = "Sandwich"
 arr_sna(2, 1) = 9
 arr_sna(2, 2) = 5
 arr_sna(3, 0) = "Pasta"
 arr_sna(3, 1) = 6
 arr_sna(3, 2) = 4
 arr_sna(4, 0) = "Veg Rice"
 arr_sna(4, 1) = 12
 arr_sna(4, 2) = 2
 arr_sna(5, 0) = "Fried Rice"
 arr_sna(5, 1) = 8
 arr_sna(5, 2) = 1
 arr_sna(6, 0) = "Curd Rice"
 arr_sna(6, 1) = 6
 arr_sna(6, 2) = 5
 arr_sna(7, 0) = "Sweet Pudding"
 arr_sna(7, 1) = 4
 arr_sna(7, 2) = 4
 
'step 8 print all elements to see the difference
Debug.Print "step 8"
 Debug.Print "Food items" & "              " & " Price" & "              " & " Rating"
 For i = LBound(arr_sna) To UBound(arr_sna)
    Debug.Print arr_sna(i, 0) & "            " & arr_sna(i, 1) & "            " & arr_sna(i, 2)
 Next
 
End Sub

Output in the Immediate window

Changing the number of dimensions:

Sub arr_redim_demo()

' step 1 : declare the array
Dim even_nos() As Variant

' step 2 : Set the size and number of dimensions
ReDim even_nos(4, 1)

' step 3 : initialize values in the array

even_nos(0, 0) = 2
even_nos(0, 1) = "one digit"
even_nos(1, 0) = 4
even_nos(1, 1) = "one digit"
even_nos(2, 0) = 6
even_nos(2, 1) = "one digit"
even_nos(3, 0) = 8
even_nos(3, 1) = "one digit"
even_nos(4, 0) = 10
even_nos(4, 1) = "two digits"

' step 5: print all elements once
Debug.Print "step 5"
For i = LBound(even_nos) To UBound(even_nos)
    Debug.Print even_nos(i, 0) & "               " & even_nos(i, 1)
Next
Debug.Print "End Printing"

' step 6 : Set the size and number of dimensions again using redim keyword
ReDim even_nos(5)

' step 7 : try printing the array to ensure that it is made empty because of redim keyword
Debug.Print "step 7"
For i = LBound(even_nos) To UBound(even_nos)
    Debug.Print even_nos(i)
Next
Debug.Print "End Printing"

' step 8 :  re-assign values as per the new dimension
even_nos(0) = 12
even_nos(1) = 14
even_nos(2) = 16
even_nos(3) = 18
even_nos(4) = 20
even_nos(5) = 22

' step 9 : Print again as per the new dimensions
Debug.Print "step 9"
For i = LBound(even_nos) To UBound(even_nos)
    Debug.Print even_nos(i)
Next
Debug.Print "End Printing"

End Sub

The output is shown below.

The Preserve Keyword

“Preserve” is a keyword that can be used to preserve the data in an array while changing its dimensions later.

Limitation: Only the length of the last dimension can be changed while using this keyword. If we try to change the size of other dimensions or change the number of dimensions, we see the error below.

Demo using the Preserve keyword:

Sub redim_pres_demo()

' step 1 : declare the array
Dim arr_furni() As Variant

' step 2 : set the size and dimensions using redim keyword
ReDim arr_furni(4, 2) As Variant

' step 3 : initialize the values

arr_furni(0, 0) = "table"
arr_furni(0, 1) = "$5"
arr_furni(0, 2) = "4 kg"

arr_furni(1, 0) = "chair"
arr_furni(1, 1) = "$6"
arr_furni(1, 2) = "3 kg"

arr_furni(2, 0) = "sofa set"
arr_furni(2, 1) = "$170"
arr_furni(2, 2) = "15 kg"

arr_furni(3, 0) = "double cot"
arr_furni(3, 1) = "$58"
arr_furni(3, 2) = "34 kg"

arr_furni(4, 0) = "Easy chair"
arr_furni(4, 1) = "$9"
arr_furni(4, 2) = "4 kg"

' step 4 : print all contents before re-sizing the array and changing dimensions
Debug.Print "step 4"
For i = LBound(arr_furni) To UBound(arr_furni)
    Debug.Print arr_furni(i, 0) & "; " & arr_furni(i, 1) & "; " & arr_furni(i, 2); ""
    Debug.Print "End of record"
Next i

' step 5 : Try to change the size of the last dimension while using the Preserve keyword
ReDim Preserve arr_furni(4, 3) As Variant

' step 6 : print all contents after re-sizing the array and changing last dimension's length
'contents should have not got erased
Debug.Print "step 6"
For i = LBound(arr_furni) To UBound(arr_furni)
    Debug.Print arr_furni(i, 0) & "; " & arr_furni(i, 1) & "; " & arr_furni(i, 2); ""
    Debug.Print "End of record"
Next i

End Sub

The output of the code is :

step 4

table; $5; 4 kg

End of record

chair; $6; 3 kg

End of record

sofa set; $170; 15 kg

End of record

double cot; $58; 34 kg

End of record

Easy chair; $9; 4 kg

End of record

step 6

table; $5; 4 kg

End of record

chair; $6; 3 kg

End of record

sofa set; $170; 15 kg

End of record

double cot; $58; 34 kg

End of record

Easy chair; $9; 4 kg

End of record

Conclusion

Arrays are really interesting objects to work with.

While the “Preserve” keyword retains the array’s contents, it comes with some limitations. If there are cases where we definitely need to change the size of the first dimension instead of the second, we could try to transpose the array, then modify the size and transpose it again to make it get back its old structure.