Excel range to Array to Listbox

  • Thread starter Thread starter George
  • Start date Start date
G

George

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


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


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
Click to expand...
Click to expand...
 
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



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


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


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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top