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
|