VBA-Excel: Arrays – Two Dimension, Dynamic Array

For creating two dimensional dynamic array in excel, follow the steps below:

Declare the two dimensional Array
Resize the array
Store values in array
Retrieve values from array

Declare the two dimensional Array

Dim arrTwoD()

Resize the array

ReDim Preserve arrTwoD(1 To 2, 1 To 2)

Store values in array

arrTwoD(i, j) = Sheet9.Cells(i, j)

Retrieve values from array

arrTwoD(5, 2)

Two Dimensional Dynamic array -1

Complete Code:

Function FnTwoDimentionDynamic()

   Dim arrTwoD()    

   Dim intRows

   Dim intCols

   intRows = Sheet9.UsedRange.Rows.Count

   intCols = Sheet9.UsedRange.Columns.Count

     ReDim Preserve arrTwoD(1 To intRows, 1 To intCols)

   For i = 1 To UBound(arrTwoD, 1)

      For j = 1 To UBound(arrTwoD, 2)

 arrTwoD(i, j) = Sheet9.Cells(i, j)            

      Next

   Next

   MsgBox "The value is B5 is " & arrTwoD(5, 2)

End Function

Two Dimensional Dynamic array -2

arrays 12