# 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 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`````` 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”. ### 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`````` 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. ## 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))
Range(ActiveCell, ActiveCell.Offset(UBound(arrData, 1) - 1, _
UBound(arrData, 2) - 1)).Value = arrData
End Sub`````` Code Breakdown

``````arrData = Range("B4", Range("B4").End(xlDown).End(xlToRight))
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.

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. Alif Bin Hussain

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Civil Engineering from Bangladesh University of Engineering & Technology (BUET). I am a fresh graduate with a great interest in research and development. I do my best to acquire new information and try to find out the most efficient solutions.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  