Any suggestions on how to "purify" a possibly corrupted workbook (recreate without corruption)?

[TomCon]

I have a large workbook that utilizes many Excel features. It is an ongoing data project for over 12 years. it is increasingly behaving "as if corrupted" but so far it has not fallen over the edge. But, I am worried. I wonder if anybody has suggestion on "how to purify"?

The workbook:

• 10.5MB
• 38 sheets
• worksheets and chart sheets
• Pivot tables
• Conditional formatting
• tables
• Images
• Many defined names
• Largest sheet 1758 rows by 100 columns currently
• Macros
• UDFs (from an add-in, the BERT add-in, a bridge between Excel and the R-Language)
• Array formulas, and some were changed to have @ sign when Excel version upgraded.

The symptoms

• At times opens up with #REF all over the place. So far it has always fixed itself when opened with "Repair". Happening increasingly often.
• Sometimes all the chart sheets suddenly lose their names when i open it and get named "Chart1", "Chart2", etc. Sometimes fixed with "repair" and sometimes i've had to fix them manually.
• A few days ago macro that i run all the time mysteriously gave an error for this line: ActiveSheet.PivotTables("PivotTable1").Refresh . (Object does not support this method). When it stopped, in the immediate window i typed ?ActiveSheet.PivotTables("PivotTable1").Name and it echoed back "PivotTable1". So hard to tell what that was. Since then no further stop at that line.

So, basically i am worried.

Does anybody have a suggestion as to how to recover all that this workbook is, but remove any possible corruption?

Is it practical to think of a macro that converts every formula to text and then recreates the workbook, and recreates charts series by series, and PivotTables, etc? What about formats? If you just copy formats from one workbook to another cell by cell (with macro) is it likely to end up pasting the corruption anyway? Would you have to interpret the format in one workbook and re-create it in the other?

Or, any suggestions at all for this case?

Thanks!

 

[rlv01]

I hope you are saving lots of backup copies.

1. What version of Excel was this workbook first created with?
2. What version of Excel is being used now?
3. 32 bit or 64bit Excel?
4. What is the file format (.xls, .xlsb, .xlsm, etc)?
5. How many VBA macros (approx)?
6. What version of the BERT add-in, 32bit or 64bit?

 

[bobsan42]

I would not necessarily think of corruption first. Rather complexity building up, computer getting generally slower ... things like this. Especially piling up a lot of conditional formatting and/or over large areas can cause efficiency issues.
You can try running the file on another machine and see how it behaves.

Of course you can rebuild the workbook. 38 sheets are not too much to bother to automate it. Normally a simple copy/paste should do it: Create a new workbook with 38 sheets named exactly as the old ones> go back to a sheet on you old workbook> select all cells on the sheet> Copy > go to new sheet > Paste formats >Paste Formulas and you're done. You have to be careful about formula references not leading back to your old workbook.
The only tedious part will probably be recreating the Pivot tables. And of course you have to transfer all code - use copy /paste for sheets and workbook modules, forms and other modules can be transfered with drag and drop in the VBE I believe.

 

[DRSteele]

I can sympathise. I have some suggestions.

Update your software to modern Excel. Excel 365 Insider would be best. The reason your formulas have @ is because of implicit intersections and the new dynamic arrays functions.

Aside from just creating a new file and pasting the raw data as values into it (this being the best way to fix all this), you can try to repair the monstrosity.

Save as a new file.
Remove all the CF, which is a notorious memory hog. Every time you add a column or row to ranges that have CF, Excel seems to create a new rule. I bet your CF rules list is lengthy.
See if everything works better. If so, Save.
Remove the Pivot Tables, which are easier to re-create than one would expect. Getting back to raw data before analysis is wise. So anything in in the Data Model can be dispensed with.
Eliminate the macros and save the file as XLSX. You can reverse this later and add back your macros after you've cleaned out the flotsam and jetsom.

If you want to copy a sheet from one file to another, copy and paste will just paste the formula references into the new shape, producing errors. One way around this is to use the Search and Replace functionality. Select the entire sheet; Search for = and replace it with something like #=. Copy the entire sheet and paste values into a new place. Then do a S&R for #= and replace with =. You must do the #= with all the sheets that are referenced and inter-referenced between sheets and you therefore must rename all the sheets identically, or the new sheet will have #REF errors.

Let us know. Starting anew is hard work, but it might be the best solution. You might end up going round and round, like trying repair a faulty Windows installation.

 

[Fluff]

Update your software to modern Excel. Excel 365 Insider would be best. The reason your formulas have @ is because of implicit intersections and the new dynamic arrays functions.

The fact that the OP has the @ sign appearing, means that they (or someone using the workbook) is on 365 already.

 

[TomCon]

Thanks for the responses! Whatever you call it (corruption or "complexity buildup"), when i see the sea of #REF it always causes we a great startle/worry/bummed-ness. And then the fact that it is fixed by 'repair'...well, I'm relieved when it is fixed; but...i wonder, "what if some day repair fails to fix it"?

I do keep lots of backups! But they may exhibit the same internal stuff, as this "occasional" need to open with 'repair' seems to be for maybe ~2 years or so now. And, it seems to be happening increasingly often.

I don't know what version of Excel this workbook started as. Its root is a very long time ago. I think it was an xls before xlsx got started. Now it is xlsm. I now have Office 365 so i have the most current Excel version. Its 32 bit. I have been using BERT with it for maybe 1-2 years i'd say. I have maybe ~10 VBA macros that I actually use now and probably another 20-30 that could be weeded out and discarded.

I was worried that "sheet by sheet" copy might also copy corruption (or "complexity buildup"). If people think that might work, though, I suppose i could at least give it a try as a start and see if it fails to open less often. It would surely be easier than trying something at a smaller granularity like cell by cell. Since it is not an everyday occurrence it will be hard to 'verify' if the behavior has gone away. Seems to be happening once a month or so now. And by the way, also a subjective impression, but i believe i'm getting alot more Excel crashes than i used to. Most of the crashes occur when saving or closing the workbook, but occasionally just randomly.

My project is actually 2 workbooks and they contain alot of external references to each other (both directions), so it remains to be seen how well copying those will end up pointing to the right place. the second workbook is quite small, and it is the large one that is the problem.

I have also experienced complexity in where defined names point when copying between workbooks. Any thoughts on that? Of course i would always want the new workbook to contain all of its own names and not point back to the old one for a name reference.

If anybody has any further thoughts, please post in addition! Thanks for the help!

 

[TomCon]

(Note a few more replies appeared while i typed the above). Thanks all. Lots of good stuff to work with and get started. Yes, repairing "mysterious, intermittently faulty stuff" is not so easy a task. But this project is important to me and i need to feel that it is safe and not about to die. I do have backups to at least recover the raw data, of course; but the work invested in the workbook itself (formatting, charts, etc) is alot in addition to tracking the raw data. I've tweaked it over 12 years now...and its like "a work of art" to me! Many of the chart series have individual point formatting, for instance. Just one example of the detail involved.

I have indeed (for some unknown reason) suspected CF over time. Sounds like a good idea to remove it and then possibly bit by bit replace it later. Is there a way to copy the format of a cell, and not capture the conditional format, if it has one?

Thanks again!

 

[DRSteele]

Incorporate the external workbook into the new one, if it's small. External references seem to be plagued with problems in some of my projects, and it worsens with every new file I put in the cloud.

You might end up just chasing the problem. Probably the wisest course is: get back to raw data, then build the analysis and formatting methodically and carefully, adding macros and automation only at the end.

 

[rlv01]

Good news if your Excel version is still 32 bit. It means you can download and install Rob Bovey's excellent and free Code Cleaner.

Excel VBA Code Cleaner Add-in

www.appspro.com

Which you should immediately run on the workbook's VBA project. If you workbook is an old and hoary as you say and has never before been 'cleaned' this might be the magic bullet. I maintain several very old and large workbooks and this tool has been a lifesaver in the past.

If you want to spend some $$$, I also use MZTools which has a couple of features that make it easy to analyze which macros you don't anymore and can be weeded out (though I'd recommend commenting out anything first and then waiting awhile to see if anything complains before deleting them). Also a nice tool to identify 'dead code'.

 

[rlv01]

I have indeed (for some unknown reason) suspected CF over time. Sounds like a good idea to remove it and then possibly bit by bit replace it later. Is there a way to copy the format of a cell, and not capture the conditional format, if it has one?

Thanks again!

CF can build up a lot of junk as rules multiply; but I also find CF to be very useful. My strategy has been to include a CF 'refresh' macro that I can use as part of, say, the WB open macro to

1. Visit the CF range
2. Delete all CF rules from the range
3. Restore the CF for the range to the default value

This keeps the CF rules from multiplying.