Updating range properties is so sloooow!!!
 

I have been profiling a Excel solution I have built and have just discovered
that this particular statement ...

rngCell.NumberFormat = xlHAlignCenter ' where Dim rngCell As
Range

.... adds 0.61 secs of run time - when called only 2,000 times!!!!!

This is running on a W2Kmachine with Pentium4 2.8 GHz with 533MHz FSB and 1G
RAM - ie. no swapping!

Is working with Range object properties always this slow? Would using a
With rngCell statement and then performing all of the property updates in
one fell swoop speed things up?

Has any one found another way to whip this into shape?

Cheers,
Frank.

Updating range properties is so sloooow!!!
 

Try to set Range properties once for as large a range of cells as possible
rather than one cell at a time.
And using With or Set will also speed things up.

The basic approach for speed is to minimise the number of . that are
executed


Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

"Frank_Hamersley" wrote in message
...
I have been profiling a Excel solution I have built and have just
discovered
that this particular statement ...

rngCell.NumberFormat = xlHAlignCenter ' where Dim rngCell
As
Range

... adds 0.61 secs of run time - when called only 2,000 times!!!!!

This is running on a W2Kmachine with Pentium4 2.8 GHz with 533MHz FSB and
1G
RAM - ie. no swapping!

Is working with Range object properties always this slow? Would using a
With rngCell statement and then performing all of the property updates in
one fell swoop speed things up?

Has any one found another way to whip this into shape?

Cheers,
Frank.

Updating range properties is so sloooow!!!
 

"Charles Williams" wrote
Try to set Range properties once for as large a range of cells as possible
rather than one cell at a time. And using With or Set will also speed
things up.

Thanks for the hint Charles - I suspected that bulk operations might be the
way to go! Unfortunately the app tries to set the best possible cell format
based on the Value in each cell.

I had thought of doing EntireColumn formats, but there is no guarantee that
the most representative cell value occurs on the first row after the column
titles la la la. This is because some of the data comes from a database
server and may contain NULL's etc ie. hetrogenous values and I try to
present the best format possible.

Anyway I guess I'll have to do something 'cos I don't expect Excel (sic)
will pull its finger out!

Cheers, Frank.

Updating range properties is so sloooow!!!
 

Frank,
It might be better if you use an Autofilter to do blocks of cells at a time. Eg, turn Autofilter on, filter for blank cells only, change format for all cells in the range, and it will only change the filtered cells.

Regards,
Ashley

"Frank_Hamersley" wrote:

"Charles Williams" wrote
Try to set Range properties once for as large a range of cells as possible
rather than one cell at a time. And using With or Set will also speed
things up.

Thanks for the hint Charles - I suspected that bulk operations might be the
way to go! Unfortunately the app tries to set the best possible cell format
based on the Value in each cell.

I had thought of doing EntireColumn formats, but there is no guarantee that
the most representative cell value occurs on the first row after the column
titles la la la. This is because some of the data comes from a database
server and may contain NULL's etc ie. hetrogenous values and I try to
present the best format possible.

Anyway I guess I'll have to do something 'cos I don't expect Excel (sic)
will pull its finger out!

Cheers, Frank.

Updating range properties is so sloooow!!!
 

changing number format doesnt trigger change event nor recalc's..
so the .61 secs looks suspicious.

BUT

in my experience the bottleneck just might be the display of
pagebreaks... (which takes time regardless of screenupdating)

have you (double)checked that display pagebreaks is OFF?
(both auto and manual)...

for new sheets, you must force a preview when the sheet is still
unpopulated and THEN turn if off.

hth


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


?B?QXNobGV5?= wrote:

Frank,
It might be better if you use an Autofilter to do blocks of cells at a
time. Eg, turn Autofilter on, filter for blank cells only, change
format for all cells in the range, and it will only change the
filtered cells.

Regards,
Ashley

"Frank_Hamersley" wrote:

"Charles Williams" wrote
Try to set Range properties once for as large a range of cells as
possible rather than one cell at a time. And using With or Set will
also speed things up.

Thanks for the hint Charles - I suspected that bulk operations might
be the way to go! Unfortunately the app tries to set the best
possible cell format based on the Value in each cell.

I had thought of doing EntireColumn formats, but there is no
guarantee that the most representative cell value occurs on the first
row after the column titles la la la. This is because some of the
data comes from a database server and may contain NULL's etc ie.
hetrogenous values and I try to present the best format possible.

Anyway I guess I'll have to do something 'cos I don't expect Excel
(sic) will pull its finger out!

Cheers, Frank.

Updating range properties is so sloooow!!!
 

"Frank_Hamersley" wrote ...

I had thought of doing EntireColumn formats, but there is no guarantee that
the most representative cell value occurs on the first row after the column
titles la la la. This is because some of the data comes from a database
server and may contain NULL's etc ie. hetrogenous values and I try to
present the best format possible.

For your Excel column, why not use a format based on the corresponding
database column? If you have permissions to access the data, you are
likely to have permissions to access the table schema.

Jamie.

--

Updating range properties is so sloooow!!!
 

"Jamie Collins" wrote ...

For your Excel column, why not use a format based on the corresponding
database column? If you have permissions to access the data, you are
likely to have permissions to access the table schema.

Am going to go down this path after a fashion. The minor complications are
that multiple result sets can get dumped on to a single sheet hence the
optimal choice for the format may change as several points down the page. I
know the extent (in rows) so I will be doing bulkier updates that now.

The other fly in the ointment is that the tool can be wired to read text
files as well as ODBC data sources and so I still need to do best guess
formatting on the data - mainly to improve readability, but also to ensure
date arithmetic, column totalling etc actions work as the user would expect.

Updating range properties is so sloooow!!!
 

"keepITcool" wrote
changing number format doesnt trigger change event nor recalc's..
so the .61 secs looks suspicious.

in my experience the bottleneck just might be the display of
pagebreaks... (which takes time regardless of screenupdating)

have you (double)checked that display pagebreaks is OFF?
(both auto and manual)...

for new sheets, you must force a preview when the sheet is still
unpopulated and THEN turn if off.

It's the alignment change that costs big time - although your points re page
breaks is well made - I will check on Monday.
Screen updating is disabled but it is still doing some serious work to get
ready for when it is enabled again.

Cheers,
Frank.

Updating range properties is so sloooow!!!
 

Hi Frank,

If the app really sets a different format for each cell it is extremely
likely to hit Excel's limit for the number of different formats.

I suspect that in practice large blocks of adjacent cells will have the same
format, so what you could do is determine the format and the size of the
block looking at data values in memory, and then apply the format to the
entire block. If you have a number of formats (font, size, alignment, colour
etc) then try creating a style and applying it to a range that covers the
block.
May also be quicker to apply a style/format to an entire block and then fix
the exceptions individually

regards
Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

"Frank_Hamersley" wrote in message
...
"Charles Williams" wrote
Try to set Range properties once for as large a range of cells as
possible
rather than one cell at a time. And using With or Set will also speed
things up.

Thanks for the hint Charles - I suspected that bulk operations might be
the
way to go! Unfortunately the app tries to set the best possible cell
format
based on the Value in each cell.

I had thought of doing EntireColumn formats, but there is no guarantee
that
the most representative cell value occurs on the first row after the
column
titles la la la. This is because some of the data comes from a database
server and may contain NULL's etc ie. hetrogenous values and I try to
present the best format possible.

Anyway I guess I'll have to do something 'cos I don't expect Excel (sic)
will pull its finger out!

Cheers, Frank.

Updating range properties is so sloooow!!!
 

"Frank_Hamersley" wrote ...

Am going to go down this path after a fashion. The minor complications are
that multiple result sets can get dumped on to a single sheet hence the
optimal choice for the format may change as several points down the page. I
know the extent (in rows) so I will be doing bulkier updates that now.

The other fly in the ointment is that the tool can be wired to read text
files as well as ODBC data sources and so I still need to do best guess
formatting on the data - mainly to improve readability, but also to ensure
date arithmetic, column totalling etc actions work as the user would expect.

Something else to consider would be to query (a closed copy of) your
Excel results i.e. get Jet to determine the majority data type. More
details on Dick Kusleika's site:

http://www.dicks-blog.com/excel/2004...al_data_m.html

Jamie.

--