[Excel] Range of cells -> VBA Array

[Ecniv]

is it possible?

I know the reverse is (from and arry to a range of cells) but I just tried the reverse and it doesn't like it.

[Webtest]

You can definitely do it cell by cell:

Sub Macro1()
Dim junk As Variant
Dim aRange As Range
Dim aCell As Range

junk = Array(0, 0, 0, 0, 0, 0, 0, 0)
Debug.Print junk(0), junk(1), junk(2), junk(3), junk(4), junk(5), junk(6), junk(7)

Set aRange = Range("D9:K9")
i = 0
For Each aCell In aRange
    junk(i) = aCell.Value
    i = i + 1
Next
Debug.Print junk(0), junk(1), junk(2), junk(3), junk(4), junk(5), junk(6), junk(7)

End Sub

[Ecniv]

No point in putting it in the array then

The idea is to transfer spreadsheets into Access from Excel.
- Docmd.TransferSpreadsheet
- open Excel, loop through cells
- open excel and grab a range as an array

I thought the last one would be faster than cell by cell, as it is when you put a VBA array into an excel range.

[dannymking]

Yes it is possible....

Dim arrMyArray()
  arrMyArray = Excl.Range("A1").CurrentRegion

[Ecniv]

Hmmm thanks for the reply. But not quite what I wanted (from reading the help files). The CurrentRegion method seems to give the whole range that is surrounded by empty cells. I want the selected range in an array (assuming the selected range is one row of data) without having to loop through all the cells.

I can loop through them, but it will be slower than if they are 'magically' put into an array. I don't know if it is possible though as I suppose a range may be of different sized cells/groupings of cells.

Thanks for the suggestion tho.

[Webtest]

"ActiveSheet.Selection" is the Range of selected cells on the currently displayed sheet.

[dannymking]

Vince..

You can specify the range.. it was only an example..

Dim arrMyArray()
  arrMyArray = Excl.Range("A1:G1")
  Dim i As Integer
  For i = LBound(arrMyArray, 2) To UBound(arrMyArray, 2)
    MsgBox arrMyArray(1, i)
  Next i

Although even as this is one row it still populates a two dimensional array