Saving file problem

[Andy Swain]

Hi

Here's a very odd one - I have a macro-enabled file of about 9Mb that currently takes 10-15 secs to save.

It is set to manual calculation and calculate-before-save is turned off.

I was asked by a colleague to amend a report to filter out some unwanted data - this meant making a change to three different array-based countifs and to 3 Index-Small formulas.

This change worked well and removed the unwanted data from the reports that the formula are used to create.

It now takes a couple of minutes to save the file.

I've tried going back to the unedited version and re-editing as above - same result every time.

Has anyone had anything similar happen or have an idea about what's happening

[Zer0Cool]

Its likely bloat/excess in the file. After the changes and the several min long save did it increase in size past the 9MB?

Also, the file being 9MB, is that a reasonable size for the amount of content in the workbook? This is a bit subjective but as extreme examples: If the file has 1 sheet 10x rows by 10x columns, 9MB would be a pretty good indicator of excess unseen content in the file. Alternatively 100 sheets with 250,000 rows by 50 columns each...if you pulled off 9MB with that your not making nearly enough money.

There are all types of excess content. Excess cells, excess format, excess styles, excess named ranges, excess objects, etc. Without seeing the workbook myself I cant say which are the culprits for you.Also is the file on a local drive or is it on a network drive. Working on larger Excel files on a network drive is like trying to get a watermelon through a garden house. If its on a network drive copy it local and see if the issue persist. If not, the network is the problem...its just slow and there isnt likely much you can do about it. If the file is local or copying local doesnt solve anything, then the file itself and its contents are the problem.

If you can post a sample file sanitized of course I may be able to investigate the excess content. If not we could discuss the signs of the excess content and what to look for. It would take longer back and forth to guide you through it then checking myself, but I understand if you cant share a copy.

[Andy Swain]

Hi - thanks for that - the file size didn't increase noticeably

As I say, it was a change to six cells that are only recalculated when a macro runs - would that make the sort of difference that changes a file from taking 10-15 second to save to taking 2 minutes?

I'm sure that my VBA could be a lot more efficient but surely that only affects the individual run time for each macro.

Does VBA code increase the file size significantly or is it large amounts of data in the worksheets?

Thanks for looking at this - will be happy to upload the file once I've had chance to clear it

Andy

[Zer0Cool]

VBA in and of itself doesnt take up a ton of space. Its generally stored as a bin file (binary) within the underlying XML/archive structure of the xlsm file.

What typically happens with excess content in a file is what I call the "snowball effect". To simplify an explanation lets say the only issue is excess cells in a single sheet. It could have started of by duplicating what Excel thinks is the used range on that sheet by 1 row, and then next time by 2 rows, and then by 4...8...16 etc. At first its negligible but as time goes on it gets worse and worse. Some types of content even have limits, like styles (~65,000).

So when I evaluate the problem you describe I ask myself if the behavior (long load/save times) matches the content and file size. If the answer is no, I then star to inspect the file for the excess's I have mentioned.

There are some obvious things to look for and some not so obvious things to check. Excess styles is an easy check (but not an easy fix). You just click the styles drop down on the Home tab to view the list of styles. By default there are 47 built in. A dozen or so extra isnt a big deal. If that pop-out window has many duplicated names ("normal", "normal 2", "normal 2 2", etc.) and/or you need to scroll to look at all the styles you have excess styles (very few people use them and even fewer are going to take them time to manually create more than a handful. The fix requires a 3rd party program or VBA (and I am the only person I know who has VBA for fully correcting the styles..to toot my own horn) as you cannot delete multiple styles via the interface only 1 at a time. Corrupt styles wont even delete from the interface and most code cant remove them either.

Once you get a sample up ill check it out. If thats not enough ill guide you through what to check.

[Andy Swain]

Many thanks - your thoughts above have reminded me about a conditional format problem that I had a while back - this may be similar