467,080 Members | 1,081 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

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

Excel range to array

Is there a fast way to transfer an Excel range to an array?

Example:
Excel range is E2:E300
Dim person() as string

Thanks,

George
Nov 20 '05 #1
  • viewed: 23760
Share:
2 Replies
Hi
A range in excel is an array
ie

Dim ary As Range
Set ary = Range("E2:E300")

Debug.Print ary(0, 1)
Debug.Print ary(1, 1)
Debug.Print ary(2, 1)
Debug.Print ary(3, 1)

gets e1 to e4 values
and so on

Regards
James

"George" <te*****@hotmail.com> wrote in message
news:c1**************************@posting.google.c om...
Is there a fast way to transfer an Excel range to an array?

Example:
Excel range is E2:E300
Dim person() as string

Thanks,

George

Nov 20 '05 #2
James,
Thanks for your reply.

I should have stated the purpose of the array - to populate a control.

Current code:
1. Define the array
dim personArray() as string

2. For-next sub to move values from Excel to the array:
personArray(i) = oXL.Cells(ThisRow, 1).value
(this sub does the dim preserve to update the array's index.)

3. Move values from array to control.
ComboBoxPerson.Items.AddRange(personArray)
I tried your suggestion (modifying it a bit):

1. Define the range
Dim xrange As Excel.Range
xrange = oXL.Range("E2:E300")

2. Move values from range to control.
ComboBoxPerson.Items.AddRange(xrange)

I get this vb.net Build error on last line, with xrange highlighted:
Value of type 'Excel.Range' cannot be converted to '1-dimensional array of string'.

Same error and highlight occurs with this:
Dim xrange As Excel.Range
xrange = oXL.Range("E2:E300")
personArray = xrange

A valid way to say this last line is what I'm looking for.

Thanks,
George

ps.
I have vb.net 2003 and Excel 2000.
The modules have:
Imports System
Imports System.io
Imports Microsoft.VisualBasic

ps2.
On this reply, I changed the Subject line, adding "- populate control";
not sure how Google handles this.


"James Lang" <j.****@blueyonder.co.uk> wrote in message news:<e5**************@tk2msftngp13.phx.gbl>... Hi
A range in excel is an array
ie

Dim ary As Range
Set ary = Range("E2:E300")

Debug.Print ary(0, 1)
Debug.Print ary(1, 1)
Debug.Print ary(2, 1)
Debug.Print ary(3, 1)

gets e1 to e4 values
and so on

Regards
James

"George" <te*****@hotmail.com> wrote in message
news:c1**************************@posting.google.c om...
Is there a fast way to transfer an Excel range to an array?

Example:
Excel range is E2:E300
Dim person() as string

Thanks,

George

Nov 20 '05 #3

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
3 posts views Thread by George | last post: by
18 posts views Thread by Frank M. Walter | last post: by
3 posts views Thread by implicate_order | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.