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.

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);

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.

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)

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

GetArrayD UDF

1. Line 4: Declare variables i and x as Type Integer using the Type Declaration Character (TDC) of %

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)

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

• 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)

Copyright © 2011 – 2023 ♦ Dr Ian O'Connor, CPA.   |   Privacy policy

Comment & Reply policy