VBA Array Length / Size

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on September 17, 2021

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
      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
      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 Easy

Stop 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! vba save as

Learn More!

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples