?Quickest way to pass an array from VB to excel? 3

[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

 

[Zathras]

Try surrounding your code with

   Application.ScreenUpdating = False

and

   Application.ScreenUpdating = True

 

[Nikita6003]

navyguy,

as long as the dimensions of a range are the same as the dimensions of your array, you can dump an array straight into excel by assigning it to that range:

Dim l_rngTest As Range
Dim the_array(1000) As String

'... code filling the array

Set l_rngTest = ThisWorkbook.Sheets(1).Range("A1:A1000")
l_rngTest = the_array

HTH

Cheers
Nikki

 

[xlbo]

Nice tip Nikki - you could also try setting calc to manual

Application.calculation = xlmanual
code
Application.calculation = xlautomatic Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes