Excel range to array

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

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
 
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
 
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.
 
Back
Top