SusaninHouston
Active Member
- Joined
- Jan 13, 2005
- Messages
- 295
Here's the whole story:
(For those who don't want to wade through, I think it's something with the screen refresh)
I have a large, complicated workbook in use by approximately 50 different people. Each person has his/her own copy of the workbook that has the same format and formulas, but data specific to that entity. I also have a 'master copy'.
The workbook is large, well-protected and contains macros but no links. I am a memory-conscious developer, so there's not a lot of 'fluff' in the workbook. I avoid things like formatting entire columns or rows, referring to entire rows/columns/sheets in lookups, and most other common and well-publicized design flaws. I always test for 'last cell' issues and blown range names, and always run my models through 'cleaner' programs, and they almost never shrink much -- I'm going to ask you to trust me on this.
So what I'm running into now is this: I issued a revised version that had some minor VB Code changes, and the users transferred their data from the Old version to the New using a macro contained in the New file. That macro used a combination of Copy-Paste-Value and Copy-Paste-All commands.
For about 35 of the users, that upgrade went without a hitch, and their new version files are working fine. But for about 15 of them, their new files now run *painfully* slowly. I'll refer to these files as 'healthy' and 'sick'. I had one of the users send in a sick file, and compared it to my master (healthy) and to another user's healthy file. Other than the performance speed, I found no differences.
Things I checked:
- Last cells (same, no extra)
- Links (none)
- Range Names (no errors)
- Conditional formatting (same for all, some duplication, but eliminating didn't cure)
- Turned off auto-calc -- no speed difference
- Picture/Icon duplication (no difference, no duplications)
- Excel versions (all using 2007)
- Virus scan (none found)
- No extra worksheets
I also tried importing from a sick file into a copy of my healthy file -- which then became sick. I also tried importing from a healthy file into a sick file -- which stayed sick.
This is where things get REALLY WEIRD:
-- The slowness is *not* so pronounced in calculation speed, but is most evident in *screen refresh* activities. Things like cursor movement or PageDown, my 'GoTo' macro that hops from named range to named rage, or the refresh-rate after moving between worksheets.
-- Also, it's really only *one worksheet* (there are about 20 in the model) where this slowness is evident, and in that worksheet, it's really, really bad. (PageDown, PageDown, PageUp, PageUp takes about 2-3 seconds to complete with incomplete screen refreshes) Of course, the worksheet in question is the most important one...
Any ideas?
(For those who don't want to wade through, I think it's something with the screen refresh)
I have a large, complicated workbook in use by approximately 50 different people. Each person has his/her own copy of the workbook that has the same format and formulas, but data specific to that entity. I also have a 'master copy'.
The workbook is large, well-protected and contains macros but no links. I am a memory-conscious developer, so there's not a lot of 'fluff' in the workbook. I avoid things like formatting entire columns or rows, referring to entire rows/columns/sheets in lookups, and most other common and well-publicized design flaws. I always test for 'last cell' issues and blown range names, and always run my models through 'cleaner' programs, and they almost never shrink much -- I'm going to ask you to trust me on this.
So what I'm running into now is this: I issued a revised version that had some minor VB Code changes, and the users transferred their data from the Old version to the New using a macro contained in the New file. That macro used a combination of Copy-Paste-Value and Copy-Paste-All commands.
For about 35 of the users, that upgrade went without a hitch, and their new version files are working fine. But for about 15 of them, their new files now run *painfully* slowly. I'll refer to these files as 'healthy' and 'sick'. I had one of the users send in a sick file, and compared it to my master (healthy) and to another user's healthy file. Other than the performance speed, I found no differences.
Things I checked:
- Last cells (same, no extra)
- Links (none)
- Range Names (no errors)
- Conditional formatting (same for all, some duplication, but eliminating didn't cure)
- Turned off auto-calc -- no speed difference
- Picture/Icon duplication (no difference, no duplications)
- Excel versions (all using 2007)
- Virus scan (none found)
- No extra worksheets
I also tried importing from a sick file into a copy of my healthy file -- which then became sick. I also tried importing from a healthy file into a sick file -- which stayed sick.
This is where things get REALLY WEIRD:
-- The slowness is *not* so pronounced in calculation speed, but is most evident in *screen refresh* activities. Things like cursor movement or PageDown, my 'GoTo' macro that hops from named range to named rage, or the refresh-rate after moving between worksheets.
-- Also, it's really only *one worksheet* (there are about 20 in the model) where this slowness is evident, and in that worksheet, it's really, really bad. (PageDown, PageDown, PageUp, PageUp takes about 2-3 seconds to complete with incomplete screen refreshes) Of course, the worksheet in question is the most important one...
Any ideas?
