Be the first user to complete this post

  • 0
Add to List

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
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
Two Dimensional Dynamic array -2



Also Read:

  1. VBA Excel - Cells, Ranges and Offset: Refer to Cells by Using Shortcut Notation
  2. VBA-Excel : Strings Functions – InstrRev
  3. VBA-Excel: Date-Time Functions – Day()
  4. VBA-Excel: Date-Time Functions – Date(), Now() and Time()
  5. VBA Excel – Refer to Multiple Ranges : Union Method