Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speeding up writes to cells?
Is there anyway to speed up writing to cells? Seems a lot of updating of
the spreadsheet is going on while calling r.Cells(y, 1) = "XYZ". Bruce E. Stemplewski www.stempsoft.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speeding up writes to cells?
Set calculation to manual at the beginning of your macro and back to
automatic at the end. -- Regards, Tom Ogilvy "Bruce E. Stemplewski " wrote in message ... Is there anyway to speed up writing to cells? Seems a lot of updating of the spreadsheet is going on while calling r.Cells(y, 1) = "XYZ". Bruce E. Stemplewski www.stempsoft.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speeding up writes to cells?
Update the worksheet in batches by buffering the data in an array, then
Range("A1").Resize(100,5).Value = MyArray() On Sat, 29 Jan 2005 20:34:13 -0500, "Bruce E. Stemplewski " wrote: Is there anyway to speed up writing to cells? Seems a lot of updating of the spreadsheet is going on while calling r.Cells(y, 1) = "XYZ". Bruce E. Stemplewski www.stempsoft.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speeding up writes to cells?
Tom Ogilvy wrote: Set calculation to manual at the beginning of your macro and back to automatic at the end. Thanks Tom, I tried that and it did not seem to make much of a difference. But just tried it again and I can see that it does make a little difference just not huge. I am writing about 10000 rows X 8 columns so that is a lot of cells. Any other performance ideas? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speeding up writes to cells?
Myrna Larson wrote: Update the worksheet in batches by buffering the data in an array, then Range("A1").Resize(100,5).Value = MyArray() On Sat, 29 Jan 2005 20:34:13 -0500, "Bruce E. Stemplewski " wrote: Thank you Myrna, I really can't do that since all of my elements might not always be in the same row or the same order. My code allows for the end user to specify which columns will be populated by the data. The columns can be in any order, not next to one another or even on different sheets(why someone would want to do that, I do not know, but it can be done). But just for educational purposes, is that an array of variants I assume? Bruce E. Stemplewski GarXface OCX and C++ Class Library for the Garmin GPS www.stempsoft.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speeding up writes to cells?
It can be any type of 2D array. If you have different data types in the
array, then it would probably need to be a variant array. But if the it were all numbers, it could be double. The concept is still sound even if a single array is not appropriate. If you are writing large blocks of data it is faster to populate an array and put it down in one statement than writing each individual cell. -- Regards, Tom Ogilvy "Bruce E. Stemplewski " wrote in message ... Myrna Larson wrote: Update the worksheet in batches by buffering the data in an array, then Range("A1").Resize(100,5).Value = MyArray() On Sat, 29 Jan 2005 20:34:13 -0500, "Bruce E. Stemplewski " wrote: Thank you Myrna, I really can't do that since all of my elements might not always be in the same row or the same order. My code allows for the end user to specify which columns will be populated by the data. The columns can be in any order, not next to one another or even on different sheets(why someone would want to do that, I do not know, but it can be done). But just for educational purposes, is that an array of variants I assume? Bruce E. Stemplewski GarXface OCX and C++ Class Library for the Garmin GPS www.stempsoft.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speeding up writes to cells?
Then, if you can't change the layout, you are probably stuck with poor
performance. Transferring data between the worksheet's data space and VBA's data space is a BIG bottleneck, with writing being about 5-6 times slower than reading. I remember one instance in which a friend had to write lines of 40 elements. He was doing this cell-by-cell, i.e. 40 writes per row. The macro took 2 HOURS to run. When he simply buffered the data to write all 40 cells at once, the time was reduced to 4 MINUTES. On Sun, 30 Jan 2005 06:24:21 -0500, "Bruce E. Stemplewski " wrote: Myrna Larson wrote: Update the worksheet in batches by buffering the data in an array, then Range("A1").Resize(100,5).Value = MyArray() On Sat, 29 Jan 2005 20:34:13 -0500, "Bruce E. Stemplewski " wrote: Thank you Myrna, I really can't do that since all of my elements might not always be in the same row or the same order. My code allows for the end user to specify which columns will be populated by the data. The columns can be in any order, not next to one another or even on different sheets(why someone would want to do that, I do not know, but it can be done). But just for educational purposes, is that an array of variants I assume? Bruce E. Stemplewski GarXface OCX and C++ Class Library for the Garmin GPS www.stempsoft.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speeding up writes to cells?
BTW, you DO have Application.Screenupdating set to False, don't you?
On Sun, 30 Jan 2005 06:13:54 -0500, "Bruce E. Stemplewski " wrote: Tom Ogilvy wrote: Set calculation to manual at the beginning of your macro and back to automatic at the end. Thanks Tom, I tried that and it did not seem to make much of a difference. But just tried it again and I can see that it does make a little difference just not huge. I am writing about 10000 rows X 8 columns so that is a lot of cells. Any other performance ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel only writes 0,0001 instead of 0,1 | Excel Discussion (Misc queries) | |||
Script that automatically writes | Excel Discussion (Misc queries) | |||
Script that automatically writes | Excel Discussion (Misc queries) | |||
Excel writes a formula on it's own - ?? | Excel Discussion (Misc queries) | |||
Linking Cells from other sheets (Speeding up Process) | Excel Discussion (Misc queries) |