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

Set calculation to manual at the beginning of your macro and back to
automatic at the end.
--
Regards,
Tom Ogilvy


"Bruce E. Stemplewski " <[email protected]> wrote in message
news:[email protected]...
> 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

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 "
<[email protected]> 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

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?

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 "
> <[email protected]> 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

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 " <[email protected]> wrote in message
news:[email protected]...
>
>
> 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 "
> > <[email protected]> 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
>
>

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 "
<[email protected]> 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 "
>> <[email protected]> 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
>

BTW, you DO have Application.Screenupdating set to False, don't you?


On Sun, 30 Jan 2005 06:13:54 -0500, "Bruce E. Stemplewski "
<[email protected]> 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?
>