Get length of array? Get length of array? vba vba

Get length of array?


excel vba ms-access

Length of an array:

UBound(columns)-LBound(columns)+1

UBound alone is not the best method for getting the length of every array as arrays in VBA can start at different indexes, e.g Dim arr(2 to 10)

UBound will return correct results only if the array is 1-based (starts indexing at 1 e.g. Dim arr(1 to 10). It will return wrong results in any other circumstance e.g. Dim arr(10)

More on the VBA Array in this VBA Array tutorial.

excel vba ms-access

Function

Public Function ArrayLen(arr As Variant) As Integer    ArrayLen = UBound(arr) - LBound(arr) + 1End Function

Usage

Dim arr(1 To 3) As String  ' Array starting at 1 instead of 0: nightmare fuelDebug.Print ArrayLen(arr)  ' Prints 3.  Everything's going to be ok.

excel vba ms-access

If the variant is empty then an error will be thrown. The bullet-proof code is the following:

Public Function GetLength(a As Variant) As Integer   If IsEmpty(a) Then      GetLength = 0   Else      GetLength = UBound(a) - LBound(a) + 1   End IfEnd Function

Recent Posts
How can I color dots in a xy scatterplot according to column value?
How to update a claim in ASP.NET Identity?
What does {0} mean when initializing an object?
Accessing members of items in a JSONArray with Java
How to log SQL statements in Spring Boot?
Powershell Get-WebSite name parameter is ignored
How to detect scroll to bottom of html element
Java synchronized method
How to test controllers with CodeIgniter?
Detect Visual Composer
Matplotlib: Specify format of floats for tick labels
Rails join a list of strings with commas and "and" before the last