excel/VBA how to assign the variable length of an

Question

In excel/VBA I have this code

Dim colName as Variant
Dim lengthToDispl as Integer
colName = Array("A", "B", "C")
lengthToDispl = colName.length

I get the following error 'Object Required'. It relates to the last line.

How do I assign the length of an array of variable length (containing strings) to a variable (integer)?

Answer 1

The function you want to return the 'size' of the array is the UBound function which returns the Upper Boundary. This is often used with its counterpart, the LBound function which returns the Lower Boundary.

This may or may not be returning the number you are looking for. Some arrays are arranged with a zero-based index and some have a one-based index. It depends on the way that they are declared and assigned.

colName = Array("A", "B", "C")

The above creates an array with a zero-based index and three elements in position colName(0), colName(1) and colName(2). The UBound (e.g. UBound(colName)) will return 2, not 3. To cycle through it, use both LBound and UBound.

for i = LBound(colName) to UBound(colName)
    debug.print colName(i)
next i

When assigning values from a worksheet's cells, you get a one-based, two dimensioned array, even if you are only collecting the values from a single column or row. The other dimension or Rank is simply 1 to 1.

colName = Range("A1:C2").Value2

This creates a two-dimensioned array with a one-based index as in ReDim colName (1 to 2, 1 to 3).

debug.print LBound(colName, 1) & " to " & UBound(colName, 1)
debug.print LBound(colName, 2) & " to " & UBound(colName, 2)

for i = LBound(colName, 1) to UBound(colName, 1)
    for j = LBound(colName, 2) to UBound(colName, 2)
        debug.print colName(i, j)
    next j
next i

I recommend using both LBound and UBound when dealing with the elements of an array.