467,161 Members | 964 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,161 developers. It's quick & easy.

Excel range to Array to Listbox

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
Nov 20 '05 #1
  • viewed: 8492
Share:
3 Replies
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

Nov 20 '05 #2
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

Nov 20 '05 #3
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

Nov 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Greg | last post: by
5 posts views Thread by Lee | last post: by
2 posts views Thread by George | last post: by
2 posts views Thread by Doug Glancy | last post: by
18 posts views Thread by Frank M. Walter | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.