How Do You Redim a Multidimensional Array?
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.
Contents
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.
- Column 0 is “Food Items” and
- 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.
- Increase or decrease the number of dimensions
- 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.
One thought on “How Do You Redim a Multidimensional Array?”