JIYIK CN >

Arrays are a popular tool in computer programming where you can save required information for later use or during code execution. Almost every computer programming language includes arrays in their libraries because sometimes the need to save and retrieve information is inevitable.

In VBA, getting the length of an array means counting the number of elements present in the array. To do this, you must know the lowest and highest element indexed. Then, the difference between the highest and the lowest will be the array length.

Additionally, you can use COUNTAworksheet functions to obtain the same information.

This article uses the highest/lowest index difference and COUNTAworksheet functions to get the length of a VBA array.

Taking the difference of highest and lowest index to get the length of an array in VBA

The logic of this method is that we can get the length of the array by subtracting the highest index from the lowest index and then adding 1. We add 1 because we need to include the lowest index in the length of the array.

The following code block will demonstrate how to get the array length of a fixed length array. Note that it is not always correct to just get the highest index to get the array length, because in some cases the lowest index is not equal to 0 and can start from a different index.

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

ArrayLengthDemoOutput:

The array length of stringArr is  5

The following code block will demonstrate how to get the array length 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

ArrayLengthDemoOutput:

The array length of stringArr is  5

I will demonstrate implementing a function to get the length of an array in the following code. This will be helpful if you often need to get the length of an array.

Additionally, the code below has extra logic to detect if the array being measured is empty.

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

GetArrayLengthDemo1Output:

The array length is 5

GetArrayLengthDemo2Output:

The array length is 0

COUNTAUsing Worksheet Functions in VBA to Get Array Length

An array is a collection of elements structured in a single or multidimensional manner. You can use COUNTAworksheet functions to calculate these elements in a single line of code.

The following code block demonstrates how to use COUNTAthe worksheet function to get the length of an array.

grammar:

WorksheetFunction.CountA([ arrayName ])

scope:

COUNTAWorksheet functions are limited to 30 elements. If you are working with large arrays, the first method is recommended.

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

ArrayLengthDemoOutput:

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

ArrayLengthDemoOutput:

The array length of stringArr is  5