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(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
Y = Range("A1:J1000").value
"Bill Martin" wrote: 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(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
No, Joel, you cannot assign a range to an array.
Have a look in VBA Help to verify this statement best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "joel" wrote in message ... Y = Range("A1:J1000").value "Bill Martin" wrote: 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(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
Oops, I am wrong. Sorry!
It works with REDIM Y(....) but not with DIM Y(....) Odd -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Bernard Liengme" wrote in message ... No, Joel, you cannot assign a range to an array. Have a look in VBA Help to verify this statement best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "joel" wrote in message ... Y = Range("A1:J1000").value "Bill Martin" wrote: 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(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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling an Array Quickly
Did you try my code? As long as the variable is a variant and you use VALUE
it works. It just doesn't load the array the same way as using the for loops. "Bernard Liengme" wrote: No, Joel, you cannot assign a range to an array. Have a look in VBA Help to verify this statement best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "joel" wrote in message ... Y = Range("A1:J1000").value "Bill Martin" wrote: 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(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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling an Array Quickly
My thanks to Joel and Bob both for quick response on this. However I tried what
you're suggesting before posting and it doesn't work for me. When I try to run the following code... Sub ArrayTest() ReDim y(1000, 10) As Single y = Range("A1:J1000").Value 'Program halts here, type mismatch Range("L1:U1000").Value = y End Sub ....it immediately halts on the "y = " line with "Run-time error '13' Type mismatch". The For/Next loop is quite happy to run though. Thanks. Bill ------------------ joel wrote: Y = Range("A1:J1000").value "Bill Martin" wrote: 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(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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling an Array Quickly
As I said to Bernard the variable Y has to be a variant. meaning you can't
define the variable Y as an array. Sub ArrayTest() Dim Y as variant 'this is equivalnet to just Dim Y y = Range("A1:J1000").Value 'Program halts here, type mismatch Range("L1:U1000").Value = y End Sub "Bill Martin" wrote: My thanks to Joel and Bob both for quick response on this. However I tried what you're suggesting before posting and it doesn't work for me. When I try to run the following code... Sub ArrayTest() ReDim y(1000, 10) As Single y = Range("A1:J1000").Value 'Program halts here, type mismatch Range("L1:U1000").Value = y End Sub ....it immediately halts on the "y = " line with "Run-time error '13' Type mismatch". The For/Next loop is quite happy to run though. Thanks. Bill ------------------ joel wrote: Y = Range("A1:J1000").value "Bill Martin" wrote: 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(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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling an Array Quickly
Ok, that's interesting I suppose for some future use. My present problem
however, is how does one quickly fill a SINGLE array, not a VARIANT. Do you know any way to do that short of using the For/Next loop? Bill ----------------------- joel wrote: As I said to Bernard the variable Y has to be a variant. meaning you can't define the variable Y as an array. Sub ArrayTest() Dim Y as variant 'this is equivalnet to just Dim Y y = Range("A1:J1000").Value 'Program halts here, type mismatch Range("L1:U1000").Value = y End Sub "Bill Martin" wrote: My thanks to Joel and Bob both for quick response on this. However I tried what you're suggesting before posting and it doesn't work for me. When I try to run the following code... Sub ArrayTest() ReDim y(1000, 10) As Single y = Range("A1:J1000").Value 'Program halts here, type mismatch Range("L1:U1000").Value = y End Sub ....it immediately halts on the "y = " line with "Run-time error '13' Type mismatch". The For/Next loop is quite happy to run though. Thanks. Bill ------------------ joel wrote: Y = Range("A1:J1000").value "Bill Martin" wrote: 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(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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling an Array Quickly
Ok, thanks. I guess that means the For/Next is the only way to go given that
I'm dealing with Single, not Variant. Bill ---------------- joel wrote: As I said to Bernard the variable Y has to be a variant. meaning you can't define the variable Y as an array. Sub ArrayTest() Dim Y as variant 'this is equivalnet to just Dim Y y = Range("A1:J1000").Value 'Program halts here, type mismatch Range("L1:U1000").Value = y End Sub "Bill Martin" wrote: My thanks to Joel and Bob both for quick response on this. However I tried what you're suggesting before posting and it doesn't work for me. When I try to run the following code... Sub ArrayTest() ReDim y(1000, 10) As Single y = Range("A1:J1000").Value 'Program halts here, type mismatch Range("L1:U1000").Value = y End Sub ....it immediately halts on the "y = " line with "Run-time error '13' Type mismatch". The For/Next loop is quite happy to run though. Thanks. Bill ------------------ joel wrote: Y = Range("A1:J1000").value "Bill Martin" wrote: 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(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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling an Array Quickly
I'm sorry to confuse you. A variant means any type including arrays
The will work Dim Y as variant Y = Array(1,2,3,4,5) This will not work Dim Y() Y = Array(1,2,3,4,5) You will get two different variables. The array Y containing the numbers 1,2,3,4 and an empty array y(). Y does not equal Y() in visual basic. "Bill Martin" wrote: Ok, thanks. I guess that means the For/Next is the only way to go given that I'm dealing with Single, not Variant. Bill ---------------- joel wrote: As I said to Bernard the variable Y has to be a variant. meaning you can't define the variable Y as an array. Sub ArrayTest() Dim Y as variant 'this is equivalnet to just Dim Y y = Range("A1:J1000").Value 'Program halts here, type mismatch Range("L1:U1000").Value = y End Sub "Bill Martin" wrote: My thanks to Joel and Bob both for quick response on this. However I tried what you're suggesting before posting and it doesn't work for me. When I try to run the following code... Sub ArrayTest() ReDim y(1000, 10) As Single y = Range("A1:J1000").Value 'Program halts here, type mismatch Range("L1:U1000").Value = y End Sub ....it immediately halts on the "y = " line with "Run-time error '13' Type mismatch". The For/Next loop is quite happy to run though. Thanks. Bill ------------------ joel wrote: Y = Range("A1:J1000").value "Bill Martin" wrote: 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(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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling an Array Quickly
You don't need loops to transfer data between the range and the arrays:
Option Base 1 Sub TransferTest() ReDim y(1000, 10) As Variant ReDim ynot(1000, 10) As Single Dim r As Range Set r = Range("A1:J1000") y = r For i = 1 To 10 For j = 1 To 1000 ynot(j, i) = y(j, i) Next Next r = ynot End Sub This is nearly instantaneous, even though ynot is filled in two step rather than directly. -- Gary''s Student - gsnu200849 "Bill Martin" wrote: Ok, thanks. I guess that means the For/Next is the only way to go given that I'm dealing with Single, not Variant. Bill ---------------- joel wrote: As I said to Bernard the variable Y has to be a variant. meaning you can't define the variable Y as an array. Sub ArrayTest() Dim Y as variant 'this is equivalnet to just Dim Y y = Range("A1:J1000").Value 'Program halts here, type mismatch Range("L1:U1000").Value = y End Sub "Bill Martin" wrote: My thanks to Joel and Bob both for quick response on this. However I tried what you're suggesting before posting and it doesn't work for me. When I try to run the following code... Sub ArrayTest() ReDim y(1000, 10) As Single y = Range("A1:J1000").Value 'Program halts here, type mismatch Range("L1:U1000").Value = y End Sub ....it immediately halts on the "y = " line with "Run-time error '13' Type mismatch". The For/Next loop is quite happy to run though. Thanks. Bill ------------------ joel wrote: Y = Range("A1:J1000").value "Bill Martin" wrote: 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(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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling an Array Quickly
No, when I'm linking to a DLL written in another language, a VARIANT is a
totally different animal from SINGLE. Not in any way interchangeable. Must have SINGLE. Bill ---------------- joel wrote: I'm sorry to confuse you. A variant means any type including arrays The will work Dim Y as variant Y = Array(1,2,3,4,5) This will not work Dim Y() Y = Array(1,2,3,4,5) You will get two different variables. The array Y containing the numbers 1,2,3,4 and an empty array y(). Y does not equal Y() in visual basic. "Bill Martin" wrote: Ok, thanks. I guess that means the For/Next is the only way to go given that I'm dealing with Single, not Variant. Bill ---------------- joel wrote: As I said to Bernard the variable Y has to be a variant. meaning you can't define the variable Y as an array. Sub ArrayTest() Dim Y as variant 'this is equivalnet to just Dim Y y = Range("A1:J1000").Value 'Program halts here, type mismatch Range("L1:U1000").Value = y End Sub "Bill Martin" wrote: My thanks to Joel and Bob both for quick response on this. However I tried what you're suggesting before posting and it doesn't work for me. When I try to run the following code... Sub ArrayTest() ReDim y(1000, 10) As Single y = Range("A1:J1000").Value 'Program halts here, type mismatch Range("L1:U1000").Value = y End Sub ....it immediately halts on the "y = " line with "Run-time error '13' Type mismatch". The For/Next loop is quite happy to run though. Thanks. Bill ------------------ joel wrote: Y = Range("A1:J1000").value "Bill Martin" wrote: 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(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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling an Array Quickly
Thanks. That is indeed faster. I'll use it.
Bill ------------------------------ Gary''s Student wrote: You don't need loops to transfer data between the range and the arrays: Option Base 1 Sub TransferTest() ReDim y(1000, 10) As Variant ReDim ynot(1000, 10) As Single Dim r As Range Set r = Range("A1:J1000") y = r For i = 1 To 10 For j = 1 To 1000 ynot(j, i) = y(j, i) Next Next r = ynot End Sub This is nearly instantaneous, even though ynot is filled in two step rather than directly. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling an Array Quickly
Gary, I really appreciate your work
I am attempting to wrap my mind around arrays. What works for me, is understanding what all the pieces/parts do and watch the input/output. That said, I am attempting to grasp when/where to Dim as Variant vs Single and which can be a subset of the other and which cannot. Looking at your code I have these questions: Like it appears no accident that you used Y and YNot. Just cute labels? Or is Y < Not Y; which evolved into Ynot or am I really off-base? Option Base 1 Sub TransferTest() ReDim y(1000, 10) As Variant 'Why reDim vs an initial Dim then resize? ReDim ynot(1000, 10) As Single 'Why is "y" Dimmed as Variant and ynot as Single? Dim r As Range Set r = Range("A1:J1000") y = r 'Seems we are cloning r "Range" into y as" Variant" For i = 1 To 10 For j = 1 To 1000 ynot(j, i) = y(j, i) 'What is the logic as to this part of the process? Next Next r = ynot 'What is the logic as to this part of the process? End Sub Bill Martin wrote: Thanks. That is indeed faster. I'll use it. Bill ------------------------------ Gary''s Student wrote: You don't need loops to transfer data between the range and the arrays: Option Base 1 Sub TransferTest() ReDim y(1000, 10) As Variant ReDim ynot(1000, 10) As Single Dim r As Range Set r = Range("A1:J1000") y = r For i = 1 To 10 For j = 1 To 1000 ynot(j, i) = y(j, i) Next Next r = ynot End Sub This is nearly instantaneous, even though ynot is filled in two step rather than directly. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filling an array with ranges | Excel Programming | |||
help with filling in an array formula | Excel Discussion (Misc queries) | |||
Filling an array from one sheet to another | Excel Programming | |||
filling a form with an array | Excel Programming | |||
Filling an array with data | Excel Worksheet Functions |