Filling an Array Quickly
 

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

Filling an Array Quickly
 

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


--
__________________________________
HTH

Bob

"Bill Martin" wrote in message
...
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

Filling an Array Quickly
 

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
--
Bernard


"Bob Phillips" wrote in message
...
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


--
__________________________________
HTH

Bob

"Bill Martin" wrote in message
...
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

Filling an Array Quickly
 

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")

Filling an Array Quickly
 

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
----------------

Fernando Fernandes wrote:
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")