Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filling an Array Quickly | Excel Programming | |||
Filling an array with ranges | Excel Programming | |||
help with filling in an array formula | Excel Discussion (Misc queries) | |||
filling a form with an array | Excel Programming | |||
Filling an array with data | Excel Worksheet Functions |