Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

VBA-Excel: Arrays – One Dimension, Dynamic Array

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

Use ReDim, For Resiz­ing the size of the array.

If you just you ReDim to chang­ing the size of an array, the already stored val­ues in array will be deleted, if you want to keep the old val­ues of an array while resiz­ing the array, use Pre­sereve key­word along with ReDim

ReDim Pre­serve arrArrayName(newSize)

Exam­ple: Store all the avail­able val­ues in col­umn A, into an Array.

For cre­at­ing one dimen­sional sta­tic array, fol­low the below steps

  • Declare an Array
  • Resize the array
  • Store val­ues in array
  • Retrieve val­ues from array.


Declare an Array

Dim arr­Dy­nAr­ray()

Resize the array

int­Counter = 2

ReDim Pre­serve arrDynArray(intCounter)

Store val­ues in array

arrDynArray(intCounter) = 4

Retrieve val­ues from array.

Msg­Box arr­Dy­nAr­ray (1)

Exam­ple: Store all the avail­able val­ues in col­umn A, into an Array.

One Dimension, Dynamic Array -1

One Dimen­sion, Dynamic Array –1

Com­plete 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
One Dimension, Dynamic Array -2

One Dimen­sion, Dynamic Array –2

 

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: