?Quickest way to pass and array from VB to excel?
?Quickest way to pass and array from VB to excel?
(OP)
Hello folks,
I have a visual basic macro running in excel. It performs all of the calculations in about 2 seconds but takes 20 seconds to send the data to excel to update an xy plot.
I pass the info with a loop like this..
for i = 1 to 1000
Worksheets("output").Cells(i+1,1)=the_array(i)
Worksheets("output").Cells(i+1,2)=another_array(i)
Next i
Is there another way to do this that might be faster?
Is the fact that the output is linked to a xy plot what slows it down? If so, is there a way to keep the chart from updating its view until all of the data is passed?
Thanks
I have a visual basic macro running in excel. It performs all of the calculations in about 2 seconds but takes 20 seconds to send the data to excel to update an xy plot.
I pass the info with a loop like this..
for i = 1 to 1000
Worksheets("output").Cells(i+1,1)=the_array(i)
Worksheets("output").Cells(i+1,2)=another_array(i)
Next i
Is there another way to do this that might be faster?
Is the fact that the output is linked to a xy plot what slows it down? If so, is there a way to keep the chart from updating its view until all of the data is passed?
Thanks





RE: ?Quickest way to pass and array from VB to excel?
Can't remember what it's called, but there is a command to disable the spreadsheet updating until you're finished transferring the data.
Also, if you can get each column onto the clipboard, you can paste it into Excel in one fell swoop, although it's not clear that that's any faster.
TTFN
RE: ?Quickest way to pass and array from VB to excel?
Application.ScreenUpdating = False
This line will freeze the screen from refreshing the updates to the workbook that your script performs. In general, it is not necessary to turn the ScreenUpdating back to "True" because it will default back to that state after the end of any Sub.
-Skullmonkey
RE: ?Quickest way to pass and array from VB to excel?
http:
Note limitations.
RE: ?Quickest way to pass and array from VB to excel?
Sub test()
Dim a(1000, 20)
For i = 0 To 999
For j = 0 To 19
a(i, j) = i + j
Next j
Next i
Range(Cells(1, 1), Cells(i, j)) = a
End Sub
RE: ?Quickest way to pass and array from VB to excel?