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

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

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

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

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

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

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

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

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

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

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