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 – Two Dimension, Dynamic Array

For cre­at­ing two dimen­sional dynamic array in excel, fol­low the steps below:

  • Declare the two dimen­sional Array
  • Resize the array
  • Store val­ues in array
  • Retrieve val­ues from array


Declare the two dimen­sional Array

Dim arrT­woD()

Resize the array

ReDim Pre­serve arrTwoD(1 To 2, 1 To 2)

Store val­ues in array

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

Retrieve val­ues from array

arrTwoD(5, 2)

Two Dimensional Dynamic array -1

Two Dimen­sional Dynamic array –1

Com­plete 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 Dimen­sional 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: