Good file gone bad... Not a virus - So Why?

[SusaninHouston]

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?

 

[diddi]

hi susan. i recently changed screens and found a difference in refresh rates in excel. is your hardware the same across your network? especially graphics cards and full HD monitors. also you may have a fragmentation problem on some of the boxes. i can recommend a defragger (PM) if you need.

 

[ZVI]

...Any ideas?

There can be numerous shapes/objects in the sheet, which are invisible or have small sizes.
See the thread Only one sheet very slow

 

[SusaninHouston]

Diddi - Thanks, but there are a variety of different users on different hardware configurations. It seems to be a worksheet issue because 'sick' files perform poorly on my computer as well as on the client's.

ZVI - The 'numerous small objects' idea is consistent with the behavior, and something I partially checked out, having run into it before. I have shapes at the top left of my screen containing icons that run the macros, and I checked to see if these somehow got 'layered' dragging them to another location to see if there was another copy underneath. I didn't see any.

Would that address that possibility? Or could there still be a bunch of empty invisible ones in there somewhere? And if so, is there an easy way to detect them? (I saw your answer in the other thread about removing them - which is cool - except for the ones for my macros, which I absolutely need...) But in that thread, you mentioned how many there were. Easy to find out how many there are?

Following that thread, I checked the 'Selection & Visibility' under the binoculars, and seem to be some extra, particularly multiple copies of 'Text Box 351' and 'Text Box 97' -- And comparing a 'sick' file to a healthy one, the sick files *do* seem to have many more of these -- so I think you're onto something.


But do you know of a way to keep some of them? The ones I need?

 

[ZVI]

Below is the code to count the shapes in a sheet.
Some properties of the shape(s) can be used to delete shape(s).

Sub Test()
  With ActiveSheet.Shapes
    ' Show shapes count in the sheet
    Debug.Print "Shapes Count = " & .Count
    ' Show some properties of Shape(1)
    ' Properties can be used to delete extra shapes
    With .Item(1)
      Debug.Print .Name, .Type, .TopLeftCell.Address, .Width, .Height
      .TopLeftCell.Activate
      .Select
    End With
  End With
End Sub

Replace ActiveSheet by Sheet1 (it's the code name) to get intellisense for the Shapes(1) properties

 

[SusaninHouston]

You're a genius ZVI!


And thanks so much for the code --

I ran it in a 'semi-healthy' file and came up with 535 shapes. I had tested the data import code numerous times, which appears to have added to the shape count (sometimes doubling) each time. But in the 'sick' file, the code counted 12,950 shapes -- apparently a problem.

I know how the extras got there -- Some of the ranges containing data that needed to be copied over also contained text boxes. And the user doing the bulk of the upgrades imported into the same template over and over, 'growing' the problem each time, which explained why only some properties had it.

Tomorrow's mission is clearing out the duplicate shapes, and hopefully, keeping the good ones.

 

[ZVI]

Glad that you’ve sorted it out!

There are different types of shapes, for example comments are the shapes too.
If it’s known that you deal with rectangles then more correct to count/delete only rectangles:
Debug.Print ActiveSheet.Rectangles.Count
ActiveSheet.Rectangles(1).Delete

 

[SusaninHouston]

That's exactly what I needed to know ZVI. I can probably safely kill my trext boxes, rectangles and comments now (they've largely served their purpose), since my macros are icons on top of rounded rectangles.

I'll post back with the results --

 

[SusaninHouston]

They're cured! Completely cured!

This is precisely what they needed -- Thank you! Thank You!

 

[ZVI]

Good news, congrats!