VBA - Array dimensions


This module examines several aspects of VBA array dimensions

  1. The VBA array 60 dimensions limit
  2. Exceeding the 60 dimension limit
  3. The effect of increasing the element count of a large array
  4. 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

declare-big-array
Fig 1: Locals Window view. BigArray expanded to the 6 dimension level

2. Exceeding the dimension limit


Adding one more dimension in code 1 line 8 (to a total of 61 dimensions);

  1. Line 7: 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, _
  2. Line 8: 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0) As Integer
  3. 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):

  1. Line 1: Procedure declaration: Sub BigArrayDimensions()
  2. 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 -
  3.         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
    
  4. 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
  5. Lines 13 to 19: A For...Next Loop with 62 steps (60 plus an arbitrary margin)
  6. 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					
  1. 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)
  2. Lines 16 to 17: Display the number of dimensions in a message box, then Exit the For...Next loop

big array dimensions
Fig 2: Watch Window view with the code in Break mode, showing details of Err.Number and i

GetArrayD UDF

  1. Line 4: Declare variables i and x 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

  1. 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)
  2. 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