?Quickest way to pass and array from VB to excel?

[navyguy]

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&quot.Cells(i+1,1)=the_array(i)
Worksheets("output&quot.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

 

[IRstuff]

As a general rule, if everything is minimized, the program will run faster.

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

 

[Skullmonkey]

Use this before doing anything that will change the graphical structure of anything in the workbook:

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

 

[Bize]

To transfer all of an array to an Excel range at once, see:

http://support.microsoft.com/defaul.../kb/articles/q177/9/91.asp&NoWebContent=1#kb1

Note limitations.

 

[cummings54]

Here's an example of fast array transfer.

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

 

[Mutt]

Could switch calculation off just before the download and then reactivate perhaps?