VBA Three Dimensional Array in Excel

Three - Dimensional Array

VBA Three Dimensional Array in Excel. The dimensional Array has three dimensions and uses three indexes(Subscripts). The three subscripts(indexes) are separated with comma symbol. For example one index represents the rows, second one represents the columns and third one represents the charts. We refer Excel worksheet or cube for 3 Dimensional 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 Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog

Leave a Comment