VBA Array Size Limit in Excel (5 Different Cases)

Array is an important feature that allows you to store and manipulate data. However, VBA has limitations on the size and dimension of an array. Exceeding these limitations will result in an error. In this article, we will explore various examples related to Excel VBA Array Size Limit.

Download Practice Workbook

Download this practice workbook to exercise while reading this article.

VBA Array Size Limit in Excel: 4 Different Things You Should Know

In this article, we will see some examples related to the Excel VBA array size limit. We will walk you through four of these examples in the following section.

1. Create an Array with 60 Dimensions

In this example, we will create an array with 60 dimensions, the maximum dimension for a VBA array. In the following code, we have declared a 60-dimensional array.

Private Sub Array60Dim()
Dim BigArray(0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, _
0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, _
0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, _
0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, _
0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, _
0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, _
0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, _
0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9, 0 To 9) As Integer
End Sub

2. How to Find the Dimension of an Array

Now we will find the dimension of an array using VBA code. In this example, a large array is declared. We will use the following VBA code to determine the dimension of the array.

Private Sub FindArrayDim()
    Dim BigArray(0 To 1, 0 To 1, 0 To 1, 0 To 1, 0 To 1, _
                0 To 1, 0 To 1, 0 To 1, 0 To 1, 0 To 1, _
                0 To 1, 0 To 1, 0 To 1, 0 To 1, 0 To 1, _
                0 To 1, 0 To 1, 0 To 1, 0 To 1, 0 To 1, _
                0 To 1, 0 To 1, 0 To 1, 0 To 1, 0 To 1, _
                0 To 1, 0 To 1, 0 To 1, 0 To 1, 0 To 1) As Integer
    Dim NumDimensions As Long
    Dim i As Integer
    On Error Resume Next
    For i = 1 To 32
        NumDimensions = LBound(BigArray, i)
        If Err.Number = 9 Then
            MsgBox "The array has " & i - 1 & " dimensions"
            Exit For
        End If
    Next i
End Sub

Press F5 to run the code. As a result, a MsgBox will appear that says “The array has 30 dimensions”.

3. What If We Cross the Dimension Limit?

The VBA array is limited to a maximum of 60 dimensions. Adding 61 or more dimensions will show Compile error: “Too many dimensions” like the following image.

4. How to Find the Number of Elements in an Array

In this example, we will create an array using the following dataset and find out the total number of elements of that array.

For this purpose, we will use the following VBA code.

Sub ArrayElements()
    Dim salesData() As Variant
    Dim numRows As Long, numCols As Long
    Dim ws As Worksheet    
    Set ws = ActiveSheet  
    salesData = ws.Range("B5:C12").Value 
    If Not IsEmpty(salesData) Then
        numRows = UBound(salesData, 1) - LBound(salesData, 1) + 1
        numCols = UBound(salesData, 2) - LBound(salesData, 2) + 1     
        MsgBox numRows * numCols
    Else
        MsgBox "No data found in the specified range."
    End If
End Sub

salesData = ws.Range("B5:C12").Value

 If Not IsEmpty(salesData) Then
        numRows = UBound(salesData, 1) - LBound(salesData, 1) + 1
        numCols = UBound(salesData, 2) - LBound(salesData, 2) + 1       
        MsgBox numRows * numCols
    Else

Run the code and it will show a MsgBox with the number of elements of the array.

How to Use VBA UBound Function in Excel

In this example, we will use the UBound function to copy a range of data to another worksheet. We have Sales rep names in column B and Products in column C. We will copy these data using the code given below.

Sub UseUbound()
Dim arrData() As Variant
Sheets("Elements").Activate
arrData = Range("B4", Range("B4").End(xlDown).End(xlToRight))
Worksheets.Add
Range(ActiveCell, ActiveCell.Offset(UBound(arrData, 1) - 1, _
UBound(arrData, 2) - 1)).Value = arrData    
End Sub

arrData = Range("B4", Range("B4").End(xlDown).End(xlToRight))
Worksheets.Add
Range(ActiveCell, ActiveCell.Offset(UBound(arrData, 1) - 1, _
UBound(arrData, 2) - 1)).Value = arrData

Run the code and get your desired results.

Frequently Asked Questions

1. How do I increase the size of an array in VBA?

The ReDim statement can be used to resize a dynamic array. This statement can change both the number of dimensions and elements of an array.

2. What is the limit size of an array in Excel?

The maximum size of an array depends on many factors such as the version of Excel, available memory of the computer, amount of data stored in the worksheet etc.

3. How can I determine the size of an array in VBA?

The size of an array can be determined by the VBA built-in UBound function. To determine the size of an array named “myarray”, you can write: ArraySize=UBound(myarray)+1 in your VBA code.

Things to Remember

• VBA array number of elements depends on the data type of the array and the amount of memory available in the computer.
• If you exceed the available memory on your system, you may encounter performance issues or crashes while running a VBA code with big arrays.

Conclusion

Thanks for reading this article. I hope you find this article helpful. In this article, we have demonstrated a few examples related to Excel VBA array size limit. We created an array with maximum dimensions and determined the array dimension. We also explored what happens when the array limit is exceeded. Moreover, we have also covered calculating the number of elements an array is storing. If you have any queries or recommendations regarding this article, feel free to let us know in the comment section below.