Get length of array

Question

I m trying to get the length of an array  yet I keep getting this error      Object required   Am I doing something wrong   Dim columns As Variant columns   Array     A    ID      D    Name   Debug Print columns Length    Error  Object required

User · Accepted Answer

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

User · Answer

Compilating answers here and there  here s a complete set of arr tools to get the work done   Function getArraySize arr As Variant    returns array size for a n dimention array   usage result k    size of the k-th dimension  Dim ndims As Long Dim arrsize   As Variant ndims   getDimensions arr  ReDim arrsize ndims - 1  For i   1 To ndims     arrsize i - 1    getDimSize arr  i  Next i getArraySize   arrsize End Function  Function getDimSize arr As Variant  dimension As Integer    returns size for the given dimension number     getDimSize   UBound arr  dimension  - LBound arr  dimension    1 End Function  Function getDimensions arr As Variant  As Long   returns number of dimension in an array  ex  sheet range   2 dimensions      On Error GoTo Err     Dim i As Long     Dim tmp As Long     i   0     Do While True         i   i   1         tmp   UBound arr  i      Loop Err      getDimensions   i - 1 End Function

User · Answer

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 If End Function

User · Answer

Function  Public Function ArrayLen arr As Variant  As Integer     ArrayLen   UBound arr  - LBound arr    1 End Function   Usage  Dim arr 1 To 3  As String    Array starting at 1 instead of 0  nightmare fuel Debug Print ArrayLen arr     Prints 3   Everything s going to be ok

User · Answer

Try CountA   Dim myArray 1 to 10  as String Dim arrayCount as String arrayCount   Application CountA myArray  Debug Print arrayCount

[excel] Get length of array?

Examples related to excel

Examples related to vba

Examples related to ms-access