Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Performance problems w/o calculations in XL2003 (Excel 2003)

    Has anyone experience performance problems in Excel 2003 where no calculations are involved?

    My problem was brought to my attention as unsatisfactory performance in an Excel VBA procedure, but I can reproduce the timings when testing manually.

    The procedure is designed to run down a range of 300 rows, testing the contents of a single column, the value of which can be 1 or null. If the value is null, the row should be hidden.

    If I set a loop to test and hide each row, it takes approximately 280 seconds to run. A slightly less efficiently-written version of this procedure, run on the same set of data, takes 1 second to run (on an older, less efficient PC) in Excel 97 SR-2 (and about 290 seconds to run on the newer PC in Excel 2003).

    If I set a loop to store the addresses in a string, then use the string to identify the range to hide, it takes approximately 45 seconds to run. While this is a clear improvement, it's still a long time to wait. By starting the timer at different points in the procedure, I can identify that the time required for the non-hide section of the procedure is negligible (less than 0.5 second).

    If I manually select the same non-contiguous range and hide the rows, it takes approximately the same time (no less).

    There are 254 rows to be hidden, broken into 40 ranges of varying sizes.

    Now, I might be about to answer my own question. Automatic calculation was switched on in both Excel 97 and Excel 2003 when I ran the aforementioned tests. When I turn off automatic calculation at the start of the procedure in Excel 2003, and turn it back on at the end, the processing time is reduced to 2 seconds (as opposed to 0 seconds if I don't turn it back on again).

    When I searched the forum for performance issues prior to preparing this post, I noticed that they generally seem to boil down to the new calculation procedures in Excel 2002/3, and that reference was made to empty cells being calculated. Is this what is happening here? If so, the burning question must be "WHY is it recalculating when I'm only hiding rows?"!!

    In writing VBA procedures, I now know to turn off the calculation feature at the start, and turn it back on at the end, and nobody need be any the wiser. Not quite so straightforward if automation isn't being used, since the user will need to know the workaround if they're going to survive to the (hopefully improved) next version of Excel with their hair intact! So if anyone knows any settings, patches, tips or tricks that can be implemented on a one-off basis, I'd love to know about them (but I won't hold my breath!).

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Performance problems w/o calculations in XL2003 (Excel 2003)

    Hiding and unhiding rows forces a recalculation. This is new in Excel 2003. I guess that even if there are no calculations, it takes time to check everything. Turning off automatic recalculations temporarily is the generally recommended workaround.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Re: Performance problems w/o calculations in XL2003 (Excel 2003)

    Thanks Hans - This confirms my suspicions, and leaves me wondering whether people with larger Excel applications might start backtracking to earlier versions in droves!

    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •