×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

?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

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

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

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

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

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

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

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources