VBA Array Length / Size
In this Article
This tutorial will teach you how to get the length (size) of an Array in VBA.
Get Array Length
In order to get the length of an Array, you need to know the array’s start and end positions. You can do this with the VBA’s UBound and LBound Functions.
LBound and UBound Functions
This procedure demonstrates how to use the UBound and LBound Functions on a a single dimension array:
Sub UBoundLBound() Dim exArr(1 To 4) As String MsgBox UBound(exArr) MsgBox LBound(exArr) End Sub
Subtracting the two will give you the array length (UBound – LBound +1).
Get Array Length Function
This function will calculate the size (length) of a single-dimensional Array:
Public Function GetArrLength(a As Variant) As Long If IsEmpty(a) Then GetArrLength = 0 Else GetArrLength = UBound(a) - LBound(a) + 1 End If End Function
Get 2D Array Size
This function will calculate the number of positions in a two-dimensional array:
Sub testArrySize() Dim arr2D(1 To 4, 1 To 4) As Long MsgBox GetArrSize_2D(arr2D) End Sub Public Function GetArrSize_2D(a As Variant) As Long Dim x As Long, y As Long If IsEmpty(a) Then GetArrSize_2D = 0 Else x = UBound(a, 1) - LBound(a, 1) + 1 y = UBound(a, 2) - LBound(a, 2) + 1 GetArrSize_2D = x * y End If End Function
VBA Coding Made EasyStop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!