Filling an Array Quickly

[Bill Martin]

I'm using a large array to pass data to/from a DLL that I call from VBA and all
works well. I'm just wondering if there's a faster way to fill the array.
Basically I'm doing something of the form:

redim Y(1000,10) as single
'Now fill the array from the spreadsheet
for I = 1 to 1000
for J = 1 to 10
Y(I,J) = cells(I,J)
next J
next I
Call DLL_Routine(Y())
'And now put the data back into the spreadsheet.
Range("A1:J1000").value = Y()

Using the For/Next loop to fill the array takes a fair amount of time in my
actual code. Putting the array back into the sheet however is virtually
instantaneous.

So, is there some faster approach I can use than the For/Next loop? I won't
bother to list out all the things I've tried that I now know *don't'* work.

I'm using Excel 2003, FWIW...

Thanks.

Bill

 

[Bob Phillips]

ReDim Y(1 To 1000, 1 To 10)
'Now fill the array from the spreadsheet
Y = Range("A1:J1000")
Call DLL_Routine(Y())
'And now put the data back into the spreadsheet.
Range("A1:J10").Value = Y

 

[Bernard Liengme]

HI Bob,
Can you tell me why this works

Option Base 1
Sub tryme()
ReDim y(4, 3)
y = Range("A1:C4").Value
For myrow = 1 To 4
For mycol = 1 To 3
Debug.Print y(myrow, mycol)
Next mycol
Next myrow
End Sub

But if I use Dim y(4, 3), I get a message "Can't assign to an array"
Thanks

 

[Fernando Fernandes]

to fill an array with contents of cells, you should certainly read it with
one line of code insteat of 10 thousand.

dim y as variant
y = range("a1:j1000")

 

[Bill Martin]

You're ignoring the central premise that this must be a SINGLE array, not a VARIANT.

"Gary's Student" gave me a solution which appears to be what I need.

Thanks.

Bill