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.
