Excel Writing speed

[Stef]

Hi,

First thank everybody for your help with my previous
problems. Hope someday I will be able to help you back...

I am trying to improve my apps processing time. I would
like to know the "fastest" way to write in a workbook. I
already know the "screenupdating = false" way. I do not
know if we can transfert another way then one cell value
at a time... I heard that some people use array to
transfert data, unfortunatly my test with this method
failed.

I also have an app that is filling a range (cell by
cell). Do you know a way to fill the range and AFTER
having been filled, it write (like if you
would "disconnect" the range from the sheet so it will
not update at each change)?

Thanks again for your precious help

 

[Paul W Smith]

I do not know if this helps, but if the entry you are making is causing a
slow recalculation, you can turn of calculation so that this does not slow
the process. When all your 'data transfer' has been done turn calculation
back to automatic and then one large recalculation would be done rather than
many slow individual ones.

Application.calculation = XlManual
Application.calculation = XlAutomatic

I do not know why your array test failed, but it is possible to write an
array of data to a similarly size range on a worksheet as one action.

An AArray(1 to 2, 1 to 2), which is a 2 x 2 array can be written to the
range("A1:B2") as one operation.

range("A1:B2") = AArray

Hope the above helps..... whenever I post a question here I try to assist
someone else!

Paul Smith

 

[pfsardella]

Som links that deal with optimizing speed.

http://www.decisionmodels.com/optspeed.htm
http://www.cpearson.com/excel/optimize.htm
http://www.microsoft.com/officedev/articles/Opg/013/013.htm

Two discussions on transferring arrays to ranges and vice versa.

http://www.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&safe=off&th=834328e94960b424&rnum=16
http://www.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&safe=off&th=e8f3b49fdd7c1dae&rnum=15

XL: Limitations of Passing Arrays to Excel Using Automation
http://support.microsoft.com/?id=177991

HOWTO: Pass a Visual Basic Array to an Excel Worksheet
http://support.microsoft.com/default.aspx?scid=kb;EN-US;153090


HTH
Paul