Excel range to array

[George]

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

[James Lang]

Re: Excel range to array

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" <tek1940@hotmai l.com> wrote in message
news:c1a4a495.0 402161328.34205 2ff@posting.goo gle.com...[color=blue]
> 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[/color]

[George]

Re: Excel range to array - populate control

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(ThisR ow, 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:E 300")

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:E 300")
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.Visua lBasic

ps2.
On this reply, I changed the Subject line, adding "- populate control";
not sure how Google handles this.[color=blue][color=green][color=darkred]
>>>[/color][/color][/color]


"James Lang" <j.lang@blueyon der.co.uk> wrote in message news:<e5uvMsO9D HA.1112@tk2msft ngp13.phx.gbl>. ..[color=blue]
> 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" <tek1940@hotmai l.com> wrote in message
> news:c1a4a495.0 402161328.34205 2ff@posting.goo gle.com...[color=green]
> > 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[/color][/color]