Efficiently moving data between worksheets and VBA array variables

  • Thread starter Thread starter Fred
  • Start date Start date
F

Fred

I need to move data to and from a worksheet quickly.

The first code segment efficiently moves data from a VBA array to the
worksheet. However, going the other way, the second code doesn't work
transfering from the sheet to the VBA array.

Why doesn't the second example work? Is there an efficient way to transfer
data without stepping through a loop?


This works:
'Transfer the array to the worksheet starting at cell A2
Dim DATAaRRAY(1 To 100, 1 To 3) As Variant
mySheet.Range("A2").Resize(100, 3).Value = DATAaRRAY

This doesn't work:
'Transfer from the worksheet to the Array doesn't work.
Dim DATAaRRAY(1 To 100, 1 To 3) As Variant
DATAaRRAY = mySheet.Range("A2").Resize(100, 3).Value

Thanks in advance.
Fred.
 
Try declaring DataArray like:
Dim DATAaRRAY As Variant
dataarray = mysheet.range("a2").resize(100,3).value
 
Back
Top