[Excel] Range of cells -> VBA Array

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.

You can definitely do it cell by cell:

Code:

---

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

---

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.

Yes it is possible....

VB Code:

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

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.

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

Vince..

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

VB Code:

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