VBA 2Dimensional Array in Excel

VBA 2Dimensional Array in Excel. Two Dimensional Array has two dimensions and uses 2 Indexes. The two indexes are separated with comma symbol. For example one index represents the rows and other represents the columns. The 2 Dimensional array also called as rectangular array. We refer Excel worksheet or table for 2 Dimensional arrays.

Table of Contents:

Objective
Syntax of VBA Two-Dimensional Array in Excel
Static 2Dimensional Array VBA Example in Excel
Dynamic 2Dimensional Array VBA Example in Excel
Instructions to Run VBA Macro Code
Other Useful Resources

Syntax of the 2 Dimensional Array Function

Here is the Syntax of the 2 Dimensional Array Function in Excel VBA.

Dim ArrayName(FirstIndexNumber,SecondIndexNumber) As DataType

where FirstIndexNumber and SecondIndexNumber: These are mandatory arguments. It represents the index or subscript value.
and DataType: It represents the type of an array variables.

Static 2Dimensional Array VBA Example in Excel

Let us see the example VBA macro code on static 2 Dimensional Array in Excel.

'VBA Static 2 Dimensional Array
Sub VBA_Static_Two_Dimensional_Array()

    'Declare Variable
    Dim aType(1, 1)
    Dim iRow As Integer, iCol As Integer
    
    'Loop through rows
    For iRow = 0 To UBound(aType, 1)
        'Loop through columns
        For iCol = 0 To UBound(aType, 2)
            aType(iRow, iCol) = ThisWorkbook.Sheets("Sheet4").Cells(iRow + 1, iCol + 1)
            'Check output in immediate window
            Debug.Print aType(iRow, iCol)
        Next
    Next
    
End Sub

Here is the output screenshot of above and below macro.

Dynamic 2 Dimensional Array VBA Example in Excel

Let us see the example VBA macro code on dynamic 2 Dimensional Array in Excel.

'VBA Dynamic 2 Dimensional Array
Sub VBA_Dynamic_Two_Dimensional_Array()

    'Declare Variable
    Dim aType()
    Dim iRow As Integer, iCol As Integer
    
    'Initialize an array size
    ReDim aType(1, 1)
    
    'Loop through rows
    For iRow = 0 To UBound(aType, 1)
        'Loop through columns
        For iCol = 0 To UBound(aType, 2)
            aType(iRow, iCol) = ThisWorkbook.Sheets("Sheet4").Cells(iRow + 1, iCol + 1)
            'Check output in immediate window
            Debug.Print aType(iRow, iCol)
        Next
    Next
    
End Sub