Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

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.

Contact Form

Name

Email *

Message *

Popular Posts