VBA Array Size Limit in Excel (5 Different Cases)

Get FREE Advanced Excel Exercises with Solutions!

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.

vba array size limit


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

Create an Array with 60 Dimensions


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

Find the Dimension of an Array

Code breakdown

  • This code defines an array called BigArray with 30 dimensions, each ranging from 0 to 1.
  • It then uses a loop to determine the number of dimensions in BigArray by checking the lower bound of each dimension using the LBound
  • The code then shows a MsgBox displaying the number of dimensions in BigArray by subtracting 1 from the loop variable i.

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

Find size of an Array in limit using VBA

Read More: VBA to Get Array Dimensions in Excel


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.

vba array size limit crossed


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.

Find the Number of Elements an 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

Find the Number of Elements an Array

Code Breakdown

salesData = ws.Range("B5:C12").Value
  • This line assigns the values from range B5:C12 to the array called salesData.
 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
  • This part calculates the number of rows in SalesData by using the UBound and LBound functions.
  • Then it displays a MsgBox with the result of multiplying numRows by numColumns.

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

Find the Number of Elements an Array

Read More: Excel VBA: Determine Number of Elements in 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

VBA UBound Function

Code Breakdown

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
  • The code first assigns values of range B4:C12 in array arrData.
  • Then it adds a new worksheet to the workbook.
  • This code then sets the value of the newly added worksheet’s range to the values of the arrData array.

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.

Download Practice Workbook

Download this practice workbook to exercise while reading this article.


Conclusion

Thanks for reading this article. I hope you find this article helpful. In this article, we have demonstrated a few examples related to the 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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Alif Bin Hussain
Alif Bin Hussain

Alif Bin Hussain earned a BSc in Civil Engineering from Bangladesh University of Engineering and Technology. As an engineering graduate, he has a deep passion for research and innovation. He loves to play with Excel. In his role as an Excel & VBA Content Developer at ExcelDemy, he not only solves tricky problems but also shows enthusiasm and expertise in handling tough situations with finesse, emphasizing his dedication to delivering top-notch content. He is interested in C, C++,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo