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.