VBA-Excel: Arrays – One Dimension, Dynamic Array

Dynamic array means when size of the array is not fixed at the start of the program,  the size changes dynamically.

Use ReDim, For Resizing the size of the array.

If you just you ReDim to changing the size of an array, the already stored values in array will be deleted, if you want to keep the old values of an array while resizing the array, use Presereve keyword along with ReDim

ReDim Preserve arrArrayName(newSize)

Example: Store all the available values in column A, into an Array.

For creating one dimensional static array, follow the below steps

• Declare an Array
• Resize the array
• Store values in array
• Retrieve values from array.

Declare an Array

Dim arrDynArray()

Resize the array

intCounter = 2

ReDim Preserve arrDynArray(intCounter)

Store values in array

arrDynArray(intCounter) = 4

Retrieve values from array.

MsgBox arrDynArray (1)

Example: Store all the available values in column A, into an Array.

Complete Code:

Function FnSingleDynamicArray()

   Dim arrDynArray()

   Dim mainWorkBook As Workbook

   Dim intRows

   Dim intCounter

   intCounter = -1

   Set mainWorkBook = ActiveWorkbook

   intRows = mainWorkBook.Sheets("Sheet1").UsedRange.Rows.Count

   For i = 1 To intRows

      intCounter = intCounter + 1

       ReDim Preserve arrDynArray(intCounter)

      arrDynArray(intCounter) = mainWorkBook.Sheets("Sheet1").Range("A" & i).Value

    Next
    MsgBox  “ The Third value in A Column is “ & arrDynArray(3)

End Function

Also Read:

1. VBA Excel - Cells, Ranges and Offset : Range
2. VBA Excel – Refer to Multiple Ranges : Union Method
3. VBA-Excel: Arrays – Two Dimensional, Static Array
4. VBA-Excel: Create Array using Array() Function
5. VBA-Excel: String Functions – strComp()