Two - Dimensional Array

VBA Two-Dimensional Array in Excel

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

Syntax of the 2Dimensional Array Function

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

Dim ArrayName(FirstIndexNumber,SecondIndexNumber) As DataType

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

Static 2Dimensional Array VBA Example in Excel

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

'VBA Static 2Dimensional 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.

VBA 2Dimensional Array

Dynamic 2Dimensional Array VBA Example in Excel

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

'VBA Dynamic 2Dimensional 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

Instructions to use Macro

Here are the instructions to use above macro in Visual basic editor.

  • Open Visual Basic Editor(VBE) by clicking Alt +F11
  • Go to code window by clicking F7
  • Copy above specified macro or procedure
  • Paste above copied code in code window
  • Run macro by clicking F5 or Run command
  • You can see output on the screen
  • Find above specified output screenshot.

Other Related VBA Arrays articles

You may also like the related VBA Array articles.

Back to VBA Arrays




Leave a Comment

Your email address will not be published. Required fields are marked *