Excel VBA – Dynamic arrays and ReDim make the code flexible

When I code in VBA, it is very rare that I will deal with a fixed number of the objects. I like to use dynamic arrays and then use ReDim to resize the array to the necessary dimensions, which makes core more flexible and adoptable in the different place.

For example,

  Dim PmtStream() As Variant

    '----we read the date from Excel sheet to PmtStream. The count variable is used to count the number of values read in.

    TotalCount = WorksheetFunction.CountIf(Range("F2:F100"), ">0")

    '-----ReDim PmtStream to the size which is needed.

    ReDim PmtStream(1 To TotalCount)

    Count = 1

    For i = 2 To 100

         If Cells(6, i).Value > 0 Then

         PmtStream(Count) = Cells(6, i).Value

        Count = Count + 1

        End If

     Next i

This is the good way to control the size of the array.

© Copyright Exceltipsandkeys All Rights Reserved.