Dynamic arrays in VBA

In an earlier post, I explained about fixed size arrays. We can use fixed size arrays when we know the exact amount of records our database has. But there are instances where we are not certain about the amount of data user will input. For an example think about a database of a company employees. New employees will be recruited and some employees will resigned or retired. So the number of records in this database is not a constant. So we need to use dynamic array to store these data. In this post I will explain how to use dynamic arrays in VBA.

Above is a sample database which contains some information of employees of a company. So we can't tell the exact number of records we will have in this database. Because new records will be added to database and some records will be removed. Also there is a possibility of adding new columns in future to hold some other information. So we need to use dynamic array if we need to store this data.
Below example shows how to store above data in dynamic array and then how to transfer whole data to another sheet.

Dim WS As Worksheet Dim All_Data() As Variant Dim i, j As Long Set WS = Worksheets("Sheet1") 'Find upper bounds Dimension1_Upperbound = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row - 2 Dimension2_Upperbound = WS.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column - 1 ReDim All_Data(0 To Dimension1_Upperbound, 0 To Dimension2_Upperbound) For i = LBound(All_Data, 1) To UBound(All_Data, 1)     For j = LBound(All_Data, 2) To UBound(All_Data, 2)         All_Data(i, j) = WS.Range("A2").Offset(i, j).Value     Next j Next i Dim WS_New As Worksheet Set WS_New = Worksheets("Sheet3") For i = LBound(All_Data, 1) To UBound(All_Data, 1)     For j = LBound(All_Data, 2) To UBound(All_Data, 2)         WS_New.Range("A1").Offset(i, j).Value = All_Data(i, j)     Next j Next i

We declare fixed size arrays like 

Dim All_Data(0 To 17, 0 To 2) As Variant

But we don't use constants when declaring Dynamic arrays. We just declare it as follows.

Dim All_Data() As Variant

After that we use ReDim statement to assign the upper bound and lower bound.

ReDim All_Data(0 To Dimension1_Upperbound, 0 To Dimension2_Upperbound)

But we should find Dimension1_Upperbound and Dimension2_Upperbound prior to ReDim statement as we did in above example. So this is how to use dynamic arrays in VBA.

Multidimensional Arrays in VBA

        If an array has more than one dimension it is called multidimensional array. If you have a list of names which you want to put to an array, you can put them in an array which has single dimension. But think you need to store these names with address and age of each person separately, then the best way is to use an array with two dimensions. Two dimensional arrays are like tables, where rows of tables correspond to first dimension and columns corresponds to second dimension. But if an array has more than two dimensions, following is the best way to illustrate it's structure.

Consider this four dimensional array

Dim SampleArray(0 To 1, 0 To 3, 0 To 2, 0 To 5) As Variant

It's structure can be best explained as follows.

So now let's look at how we can declare and populate multidimensional array. I will use below sample data for the explanations.

So let's put all these data to an array called All_Data()

Dim WS As Worksheet Dim All_Data(0 To 17, 0 To 4) As Variant Dim i, j As Long Set WS = Worksheets("Sheet1") For i = 0 To 17     For j = 0 To 4         All_Data(i, j) = WS.Range("A2").Offset(i, j).Value     Next j Next i

It is a best practice to use LBound and UBound instead of constants. Because then we don't need to change whole code if we need to change the size of our array. So here is the modification of above code where constants are replaced with LBound and UBound functions.

Dim WS As Worksheet Dim All_Data(0 To 17, 0 To 4) As Variant Dim i, j As Long Set WS = Worksheets("Sheet1") For i = LBound(All_Data, 1) To UBound(All_Data, 1)     For j = LBound(All_Data, 2) To UBound(All_Data, 2)         All_Data(i, j) = WS.Range("A2").Offset(i, j).Value     Next j Next i

So now you know how to put values from worksheet to a multidimensional array. So what if you need to do the reversal. ie-What if you need to put values from an array to a worksheet range. Code is very much similar to the above example. You just need to do a change in one line. Following is an example. Here what we do is, first we get data from first 3 columns to an array. Then reads values from array and put them back to sheet 3.

Dim WS As Worksheet Dim All_Data(0 To 17, 0 To 2) As Variant Dim i, j As Long Set WS = Worksheets("Sheet1") For i = LBound(All_Data, 1) To UBound(All_Data, 1)     For j = LBound(All_Data, 2) To UBound(All_Data, 2)         All_Data(i, j) = WS.Range("A2").Offset(i, j).Value     Next j Next i Dim WS_New As Worksheet Set WS_New = Worksheets("Sheet3") For i = LBound(All_Data, 1) To UBound(All_Data, 1)     For j = LBound(All_Data, 2) To UBound(All_Data, 2)         WS_New.Range("A1").Offset(i, j).Value = All_Data(i, j)     Next j Next i

Here is the result you will get.