How to Get the Length of an Array in VBA

The VBA programming language provides a variety of tools for automating tasks in Excel and other Office products. One such tool is the array, a data structure that can hold multiple values. This tutorial will show you how to get the length of an array in VBA. This technique is useful when you need to loop through all the elements of an array or perform some computation based on the number of elements in the array.

Step 1: Declare an Array

Before we get the length of an array, let’s first declare one. VBA uses the Dim keyword to declare an array. The size of the array is provided in parentheses. Here’s an example of how to declare an array with 5 elements in VBA:

Step 2: Assign Values to the Array

Next, let’s assign some values to our array. This can be done using the array index, which starts from zero:

Step 3: Get the Length of the Array

To get the length of the array, we will use the UBound function in VBA. ‘UBound’ stands for ‘Upper Bound’, and it returns the highest index in the array. To get the size, you add 1 to the result (because the array index starts at zero). Here’s the code:

Now variable ‘length’ holds the number of elements in the array.

Full Code

Here’s the full VBA code showing how to declare an array, assign values, and then get the array’s length:


Getting the length of an array in VBA is a simple process once you understand how arrays are indexed in VBA. This method can be useful in several scenarios, such as when iterating through the array elements or when performing computations based on the number of elements in the array.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Posted in vba