VBA - Array dimensions
This module examines several aspects of VBA array dimensions
- The VBA array 60 dimensions limit
- Exceeding the 60 dimension limit
- The effect of increasing the element count of a large array
- Determining the number of dimensions of an array
VBA arrays
A VBA array can have a maximum of 60 dimensions.
1. An array with 60 dimensions
An example of a 60 dimension array declaration is shown in code 1, with the associated Locals Windows view in figure 1. Each dimension is a single element.
Code 1a: Sub
BigArrayDeclare60
declare an array with 60 dimensions
Private Sub BigArrayDeclare60() Dim BigArray(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, _ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, _ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, _ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, _ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, _ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0) As Integer End Sub

2. Exceeding the dimension limit
Adding one more dimension in code 1 line 8 (to a total of 61 dimensions);
- Line 7: 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, _
- Line 8: 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0) As Integer
- returns a Compile error: Too many dimensions
In other words, the 60 dimension limit has been exceeded.
3. Increasing the element count of a large array
In Code 1b, the Module Declaration includes Option Base 0 (line 1), and the upper index of each dimension is set to 1. This is equivalent to Dim BigArray(0 to 1, 0 to 1, 0 to 1, ..., 0 to 1)
This declaration returns error 7 Out of Memory (on Windows 10 64-bit with 64Gb of RAM), because the Excel and VBA resources have been exceeded. There are 2^60 elements. Compare this to the 2^34 cells in a worksheet.
Code 1b: Sub
BigArrayDeclare60b
declare an array with 60 dimensions and 2 elements of type integer per dimension
Option Base 0 Private Sub BigArrayDeclare60b() Dim BigArray(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) As Integer End Sub
4. Determining the dimension of an array
There is no VBA in-built function to return the dimension count for an array. In code 2, the BigArrayDimensions macro counts the number of dimensions by testing the existence of a lower bound index number for the dimension, using the LBound function.
BigArrayDimensions macro
Code 2 by line number (selected statements):
- Line 1: Procedure declaration:
Sub
BigArrayDimensions() - Lines 3 to 8: Declare a static array named BigArray with 60 dimensions, as type
Integer
. Procedure scope. Default value: 0 in the element of the upper dimension. This is one declaration statement with line breaks added to improve readability (space, underscore, enter at the end of lines 3 to 7). Written as a one-liner, we need to scroll to the right to read the code in this alternate version - Dim BigArray(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0) As Integer
- Line 12: Suppress the error message raised by the LBound function (line 14) in cases where the array dimension limit has been exceeded, then pass control to the statement immediately following the statement causing the error
- Lines 13 to 19: A For...Next Loop with 62 steps (60 plus an arbitrary margin)
- Lines 14: Return the lower bound index number the dimension i, and assign the value to the ErrChk variable. In this example, all values are zero for a valid dimension. If the dimension does not exist, then a Subscript out or range error, is returned (error number 9)
Code 2: Sub
BigArrayDimensions
counts the number of dimension by testing the existence of a lower bound index number for the dimension
Private Sub BigArrayDimensions() Dim BigArray(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, _ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, _ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, _ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, _ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, _ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0) As Integer Dim ErrChk As Long Dim i As Integer On Error Resume Next For i = 1 To 62 ErrChk = LBound(BigArray, i) If Err.Number = 9 Then MsgBox "The array has " & i - 1 & " dimensions" Exit For End If Next i End Sub
- Lines 15: The Subscript out of range error (error number 9) is not assigned to the ErrChk variable, but is the number property of the Err object. Use the Watch Window in Break mode to examine details of the Err.Number expression (figure 2)
- Lines 16 to 17: Display the number of dimensions in a message box, then Exit the For...Next loop

GetArrayD UDF
- Line 4: Declare variables
i
andx
as Type Integer using the Type Declaration Character (TDC) of %
Code 3: Function
GetArrayDimensions
returns the number of dimensions in an array
Private Function GetArrayD(InArray As Variant) As Integer Dim ErrChk As Long Dim i%, x% On Error Resume Next For i = 1 To 62 ErrChk = LBound(InArray, i) If Err.Number = 9 Then x = i - 1 Exit For End If Next i GetArrayD = x End Function
Test the UDF
- Line 2: Declare an array named TestArray with 3 dimensions. Dimension 1 has 5 elements indexed 1 to 5. Dimension 2 has seven elements indexed -8 to -2 (index bounds must be in ascending order, From...To). Dimension 3 has 10 elements indexed 1 to 10. TestArray has a total of 350 elements (5 x 7 x 10)
- Lines 5 to 11: Is a three dimensional nested For...Next loop that populates the array elements with values based on the sum of the element's index numbers (i,j,k)
Code 4: Sub
RunGetArrayD
returns the number of dimensions in a test array
Private Sub RunGetArrayD() Dim TestArray(1 To 5, -8 To -2, 1 To 10) Dim i%, j%, k% For i = LBound(TestArray, 1) To UBound(TestArray, 1) For j = LBound(TestArray, 2) To UBound(TestArray, 2) For k = LBound(TestArray, 3) To UBound(TestArray, 3) TestArray(i, j, k) = i + j + k Next k Next j Next i Debug.Print vbNewLine & "===============================" Debug.Print "Time: " & Format(Time, "hh:mm:ss am/pm") Debug.Print "TestArray has " & GetArrayD(TestArray) & " dimensions" End Sub
Selected VBA terms used in this module
VBA terminology | Description |
---|---|
Debug.Print (Debug object, Print property) | Prints information to the Immediate Window |
Err.Number (Err object, Number property) | Returns or sets a value specifying an error number Error numbers 7 Out of memory 9 Subscript out of range |
LBOUND(arrayname[, dimension]) (Function) | Returns the lowest subscript value for the indicated dimension of an array. The lower bound is 0 or 1 depending on the value of the Option Base setting, or the lower number in the "to" setting |
On Error{Resume Next} (Statement) | Unstructured error handling. The Resume Next part specifies that control goes to the statement immediately following the statement causing the error and execution continues from there |
vbNewLine (Constant NewLine field) | Sends a newline character for print and display functions |
UBOUND(arrayname[, dimension]) (Function) | Returns the highest subscript value for the specified dimension of an array. The dimension default is 1 |
- This example was developed in Excel 2016 64 bit with VBA 7.1.
- Published: 3rd May 2016
- Revised: Friday 24th of February 2023 - 10:37 PM, Pacific Time (PT)