PDA

Click to See Complete Forum and Search --> : Speed up VB

Trenchcoat
July 2nd, 2001, 06:43 AM
Hello!

I am developing a COM Add-In for Excel. Now I have the following problem. I write around 2000 lines with 20 columns of data (most are references to another sheet) to an excel worksheet. I cannot write the whole data set at one time. Impossible (too long to explain for now). The problem is that the more data is on the sheet the longer it takes to create new lines. At the end it takes nearly a minute to create the data.

How can I speed this up? Any ideas?

Thank you in advance for your help!

--------------------------------------------------
Visit our new forum: www.maxcode.com
Feel free to contact me via ICQ# 82806695
Clearcode
July 2nd, 2001, 06:52 AM
Do you have derived cells on the sheet? i.e. running totals or averages or whatever - if so, add these after the data set has been populated.

-------------------------------------------------
Ex. Datis: Duncan Jones
Merrion Computing Ltd
http://www.merrioncomputing.com
Trenchcoat
July 2nd, 2001, 06:55 AM
No...

Its the following: I have one sheet with languages (4 columns) one column contains the current language and if another language is selected this columns is filled with one of the other columns contents... follow?

And on the other sheet there are cells which refer to the current language column in the other sheet.

Understand what I am talking about?

--------------------------------------------------
Visit our new forum: www.maxcode.com
Feel free to contact me via ICQ# 82806695
Trenchcoat
July 2nd, 2001, 07:00 AM
I think that is a redraw problem. The excel window is redrawn every time a line is inserted into the sheet. Can I prevent redrawing until my generating process is finished?

--------------------------------------------------
Visit our new forum: www.maxcode.com
Feel free to contact me via ICQ# 82806695
Cakkie
July 2nd, 2001, 07:01 AM
Try setting the EnableCalculation property of the sheet to false, insert all your rows, and set it back to true. Then call the Calculate method of the sheet.

Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook