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:
|
1 |
Dim arr(5) As Integer |
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:
|
1 2 3 4 5 |
arr(0) = 1 arr(1) = 2 arr(2) = 3 arr(3) = 4 arr(4) = 5 |
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:
|
1 2 |
Dim length As Integer length = UBound(arr) + 1 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sub GetLength() Dim arr(5) As Integer arr(0) = 1 arr(1) = 2 arr(2) = 3 arr(3) = 4 arr(4) = 5 Dim length As Integer length = UBound(arr) + 1 MsgBox length End Sub |
Conclusion
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.