excel/VBA how to assign the variable length of an array to integer variable

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)?

Answers


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.


Need Your Help

How to add only right border to UIImageView

ios uiimageview uiimage border

I want to add a white border to an UIImageView. I tried the following, but it didn't work:

gem2rpm includes all dependencies instead of including only runtime dependencies

ruby rubygems dependencies rpm

Version 0.6.0 of gem2rpm includes all (development and runtime) dependencies for a given Gem.