Arrays are a bit of a puzzle until you understand their structure. Once
you get there then working with them is a breeze! Here's some
pointers...

You can leave a declared array's elements blank (as Claus suggests) and
ReDim it once per usage if it's multi-dimensional, but you can only
progressively ReDim a 1D array.

Loading a range into a Variant results a 2D array consisting of [n]Rows
by [n]Cols. These will always be 1 based since there is no Rows(0) or
Columns(0) on a worksheet.

All other arrays are zero-based unless declared otherwise, *or* you
specify *Option Base 1* in the declarations section of the module
containing the code. I don't ever do this myself, but it seems to
attract interest to many when it shouldn't and so I recommend to avoid
using it.


Zero-based arrays:
These work well with most list control indexes.

Also works great when loading data from a delimited text file that has
been properly prepared with fieldnames in the first line. In this case,
the first record is MyArray(1) and the last record is UBound(MyArray).
Thus RecordCount = UBound(MyArray), and Record # of #Records is
always...

"Record " & MyArray(n) & " of " & UBound(MyArray)

So for example, you can 'dump' fieldnames into a ComboBox list like
this...

ComboBox1.List = Split(MyArray(0), ",")

...where its ListIndex starts at zero but the ListCount is
UBound(Split(MyArray(0), ","))+1. The same thing can be achieved as
follows...

Dim vTmp
vTmp = Split(MyArray(0), ",")
ComboBox1.List = vTmp

...but I don't see the point for using the extra steps.

Now in the case of a 1D array of single values...

ComboBox1.List = MyArray1D


Not surprisingly, you can't 'dump' a range directly into a list control
because the indexing is invalid due to a range array being 1-based-2D.
In this case you need to load the list into another array (or populate
each list item individually)...

With Application
Me.ComboBox1.List = .Transpose(.Index(Range("MyList"), 0, 1))
End With

...where "MyList" is a named range containing the items to display in
the ComboBox1.List.

(This can be confusing since you can 'dump' a range into a DV list
because it's 1-based since there's no Item(0) when enumerating a DV
list)

Also, VB[A] arrays are virtual meaning they only exist in memory.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion