VBA Three-Dimensional Array in Excel

VBA Three Dimensional Array in Excel. The 3Dimensional Array has three dimensions and uses three indexes(Subscriptss). The three subscripts(indexes) are separated with comma symbol. For example one index represents the rows, second one represnts the columns and third one represnts the charts. We refer Excel worksheet or cudbe for 3Dimensional arrays.

Syntax of the 3Dimensional Array Function

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

Dim ArrayName(FirstIndexNumber,SecondIndexNumber,ThirdIndexNumber) As DataType

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

Example for VBA 3Dimensional Array in Excel

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

'VBA Dynamic 3Dimensional Array
Sub VBA_Dynamic_Three_Dimensional_Array()

'Declare Variable
Dim aType()
Dim iRow As Integer, iCol As Integer, iSht As Integer

'Initialize an array size
ReDim aType(2, 2, 2)

'Loop through rows
For iRow = 0 To UBound(aType, 1)
'Loop through columns
For iCol = 0 To UBound(aType, 2)
'Loop through sheets
For iSht = 1 To UBound(aType, 3)
aType(iRow, iCol, iSht) = ThisWorkbook.Sheets("Sheet" & iSht).Cells(iRow + 1, iCol + 1)
'Check output in immediate window
Debug.Print aType(iRow, iCol, iSht)

End Sub

You can view output in the immediate window.

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