Menu
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 
779
length is a property of arrays in JavaScript that returns or sets the number of elements in a given array. The length property of an array can be returned like so. let desserts = ["Cake", "Pie", "Brownies"]; console. log(desserts.
We can find the size of an array using the sizeof() operator as shown: // Finds size of arr[] and stores in 'size' int size = sizeof(arr)/sizeof(arr[0]);
The JavaScript array length property states the number of items in an array. To find the length of an array, reference the object array_name. length. The length property returns an integer.
Unlike the String and ArrayList, Java arrays do not have a size() or length() method, only a length property.
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.
75
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. If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
