My "final answer", in moving data from Excel range to Listbox items:

Solution 1, needs one array, OK for small range.

1a. Excel to array

Dim Ary1() as string

'one-dimensional array acceptable to Listbox.Items.AddRange

For...Next, transfer values from Excel to Ary1

1b. Array to Listbox

Listbox.Items.AddRange(Ary1)

------------------------------------------

Solution 2, needs two arrays, better for large ranges.

2a. Excel to array

Dim Ary2 as System.Array

Dim xRange as Object

xRange = oXL.Range("A1:A930")

Ary2 = xRange.value 'Ary2 now has rank of 2, base 1

2b. Transfer data from two-dimensional to one-dimensional array.

Could not find a command to do this.

Redim cannot change the array's rank.

The For...Next here goes much faster than the one in step 1a.

Dim Ary3(UBound(Ary2, 1) - 1) as String

'Ary2 uses base 1, Ary3 uses base 0.

'Need to decrement by one for normal array with base zero

Dim n2 as Integer

Dim n3 as Integer

For n2 = 1 to UBound(Ary2,1)

'read items from Ary2 starting at 1

n3 = n2 -1

Ary3(n3) = Ary2(n2, 1)

'transfer to Ary3 starting at zero

Next n2

2c. Array to Listbox

Listbox.Items.AddRange(Ary3)

---------------

I could not find these steps explained in Microsoft help or in other posts here.

George

te*****@hotmail.com (George) wrote in message news:<c1**************************@posting.google. com>...

This seems to be the problem:

When Ary2 = xRange.value, the rank (number of dimensions) of Ary2 is

set to 2.

But "listbox1.AddRange" evidently allows only single-dimensional

arrays.?

If that is true, is there a way to copy a specific dimension of a

multi-dimensional array to a single-dimensional array?

I tried Array.Copy without success.

I'm not getting very much help here; is this a dumb question or what?

Would ArrayList or something else work better?

Thanks,

George

te*****@hotmail.com (George) wrote in message news:<c1**************************@posting.google. com>... No replies yet, so I'll explain this newbie question another way:

How do you transfer data from an "Excel range" array to a listbox?

I've used this code with "normal" arrays:

dim Ary1() as String

[slow transfer of Excel data in "For-Next" code into Ary1]

xValue = Ary1(2) 'xValue = C

me.listbox1.AddRange(Ary1) 'this works

To transfer data (faster) from an Excel range:

dim Ary2 'not normal way to declare an array

dim xRange as Object

xRange = oXL.Range("A1:A9") 'has letters A-H

Ary2 = xRange.value 'this works

xValue = Ary2(3, 1) 'xValue = C

'not normal way to access array values

me.listbox1.AddRange(Ary2) 'error message (see my first post)

It's obvious that Ary2 is a Different kind of object than Ary1.

Have you seen any explanations for this?

Can this kind of array (from Excel range) be used in Listbox.AddRange?

If not, any suggestions to do it other ways?

Thanks for your help on either question,

George

te*****@hotmail.com (George) wrote in message news:<c1**************************@posting.google. com>... Sub ExcelToListBox()

Dim xRange As Object

Dim ary

Dim xValue As String

xRange = oXL.Range("A1:A9") 'has letters A-H

ary = xRange.value

xValue = ary(3, 1) 'xValue = C

Me.ListBoxPerson.Items.AddRange(ary)

This AddRange causes error message:

'An unhandled exception of type

'System.Reflection.AmbiguousMatchException' occurred in

microsoft.visualbasic.dll

'Additional information: No accessible overloaded

'ObjectCollection.AddRange' can be called without a narrowing

conversion.

End Sub

>>

Questions:

Is there a difference between these two:

Dim ary

Dim ary as System.Array

What is the syntax for the AddRange? I've tried various combinations.

Thanks,

George