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

 

[joel]

Y = Range("A1:J1000").value

 

[Bernard Liengme]

No, Joel, you cannot assign a range to an array.
Have a look in VBA Help to verify this statement
best wishes

 

[Bernard Liengme]

Oops, I am wrong. Sorry!
It works with REDIM Y(....) but not with DIM Y(....)
Odd

 

[joel]

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.

 

[Bill Martin]

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]

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]

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

 

[Bill Martin]

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]

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.

 

[Gary''s Student]

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.

 

[Bill Martin]

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

 

[Bill Martin]

Thanks. That is indeed faster. I'll use it.

Bill

 

[EagleOne]

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