Be the first user to complete this post
|
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)

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

Also Read:
- VBA-Excel: Create Array using Array() Function
- VBA-Excel: Arrays – Two Dimensional, Static Array
- VBA-Excel : 3D-Ranges - Working with Multiple WorkSheets At The Simultaneously
- VBA Excel - Cells, Ranges and Offset: Refer to Cells by Using Shortcut Notation
- VBA-Excel: Array Functions – Split()