How to Get the Array Length in VBA

1. Get the Difference of the Highest and Lowest Index to Get the Array Length in VBA
2. Use COUNTA Worksheet Function to Get the Array Length in VBA

Arrays are popular tools in computer programming wherein you can save information needed for later use or during code execution. Almost every computer programming language, arrays are included in their library since the need to save and retrieve information is sometimes inevitable.

In VBA, getting the length of an array means counting the number of an element present inside the array. To do this, you have to know the index’s lowest and highest elements. Then, the difference between the highest from the lowest would be the array length.

Also, you could use the COUNTA worksheet function to get the same information.

This tutorial uses the highest/lowest index difference and the COUNTA worksheet function to get the length on a VBA array.

Get the Difference of the Highest and Lowest Index to Get the Array Length in VBA

The logic of this method is that we can get the array’s length by subtracting the lowest index to the highest index then adding 1. We add 1 since it is needed to include the lowest index in the array’s length.

The code block below will demonstrate getting the array length of an array of a fixed-length array. Note that getting only the highest index to get the array length is not always correct as there are cases where the lowest index is not equal to 0 and could start with different indexes.

Sub ArrayLengthDemo()

Dim stringArr(5 To 9) As String

stringArr(5) = "Glen"
stringArr(6) = "Yumi"
stringArr(7) = "Myla"
stringArr(8) = "Katrina"
stringArr(9) = "Jose"

Debug.Print "The array length of stringArr is " UBound(stringArr) - LBound(stringArr) + 1
End Sub

ArrayLengthDemo Output:

The array length of stringArr is  5

The code block below will demonstrate getting the array length of an array of a dynamic array.

Sub ArrayLengthDemo()

Dim StringArr As Variant

StringArr = Array("Glen", "Yumi", "Katrina", "Myla", "Jose")

Debug.Print "The array length of StringArr is "; UBound(StringArr) - LBound(StringArr) + 1

End Sub

ArrayLengthDemo Output:

The array length of stringArr is  5

Implementing functions to get the array length on the below code will be demonstrated. It will be helpful if the need to get the array length is recurring.

Also, the code below has additional logic to detect if the array to be measured is null.

Public Function GetArrayLength(arr As Variant) As Integer
   If IsEmpty(arr) Then
      GetArrayLength = 0
   Else
      GetArrayLength = UBound(arr) - LBound(arr) + 1
   End If
End Function

Sub GetArrayLengthDemo1()


Dim stringArr(5 To 9) As String

stringArr(5) = "Glen"
stringArr(6) = "Yumi"
stringArr(7) = "Myla"
stringArr(8) = "Katrina"
stringArr(9) = "Jose"

Debug.Print "The array length is " & GetArrayLength(stringArr)

End Sub

Sub GetArrayLengthDemo2()

Dim NullArr As Variant

Debug.Print "The array length is " & GetArrayLength(NullArr)

End Sub

GetArrayLengthDemo1 Output:

The array length is 5

GetArrayLengthDemo2 Output:

The array length is 0

Use COUNTA Worksheet Function to Get the Array Length in VBA

An array is a collection of elements structured in a single or multi-dimensional fashion. You can use the COUNTA worksheet function to count these elements in a single line of code.

The code block below demonstrates how to get the array length using the COUNTA worksheet function.

Syntax:

WorksheetFunction.CountA([ arrayName ])

Parameter:

The COUNTA worksheet function is limited only to 30 elements. If dealing with a large array, the first method is suggested.

Sub ArrayLengthDemo()

Dim stringArr(5 To 9) As String

stringArr(5) = "Glen"
stringArr(6) = "Yumi"
stringArr(7) = "Myla"
stringArr(8) = "Katrina"
stringArr(9) = "Jose"

Debug.Print "The array length of stringArr is " & WorksheetFunction.CountA(stringArr)
End Sub

ArrayLengthDemo Output:

The array length of stringArr is 5

Sub ArrayLengthDemo()

Dim StringArr As Variant

StringArr = Array("Glen", "Yumi", "Katrina", "Myla", "Jose")

Debug.Print "The array length of StringArr is " & WorksheetFunction.CountA(stringArr)

End Sub

ArrayLengthDemo Output:

The array length of stringArr is  5