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]

Oh yeah, and here's one last thing that falls into the "I don't know why this would ever work, but sometimes it does" category for fixing wonky workbooks:

1. With the .xlsm file, do a Save-As to an .xlsb file.
2. With the .xlsb file, do a Save-As back to an .xlsm file.

 

[TomCon]

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.

The reason i have the smaller workbook as part of the project is that I open it on my phone for data entry. The large one does not work well on the phone. every number you enter can take many seconds before Excel can recalc.

 

[TomCon]

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.

Could you post such a macro. I am not exactly sure how to do this. Not exactly sure what #3 means. Thank you!

 

[TomCon]

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

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'.

Thanks much! This seems to be worth trying. It makes sense to use this as an occasion to do some cleaning first and get rid of old macros i no longer use. Good!

 

[TomCon]

Oh yeah, and here's one last thing that falls into the "I don't know why this would ever work, but sometimes it does" category for fixing wonky workbooks:

1. With the .xlsm file, do a Save-As to an .xlsb file.
2. With the .xlsb file, do a Save-As back to an .xlsm file.

Well, this is another thing that would be "simple to do", so i think i will give it a try as well before under taking a much more complex process of re-creating the workbook step by step. Worth a try.

 

[rlv01]

Could you post such a macro. I am not exactly sure how to do this. Not exactly sure what #3 means. Thank you!

This example assumes that you have a format condition applied to column A that you want to "refresh". It could be more than one format condition, but I'm just showing one here. The term "default" in this case just means what you want the format condition to be for this range of cells.

Sub RefreshFormatCondition()
    Dim MyRange As Range
    Dim FC As FormatCondition
    Dim WS As Worksheet

    Set WS = ActiveSheet

    Set MyRange = WS.Range("A2:A" & WS.Range("A" & WS.Rows.Count).End(xlUp).Row)    'define the range containing the conditional formatting (CF)

    With MyRange
        .FormatConditions.Delete                      ' clear existing format conditions
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="Green"    'restore "default" format condition as you like
        Set FC = .FormatConditions(1)
        With FC.Interior
            .Color = 12379352                         'set formatting (background color, font, etc)
            .TintAndShade = 0
        End With
        With FC.Font
            .Bold = True                              'set formatting (background color, font, etc)
            .Italic = False
            .TintAndShade = 0
        End With
    End With
End Sub

 

[TomCon]

This example assumes that you have a format condition applied to column A that you want to "refresh". It could be more than one format condition, but I'm just showing one here. The term "default" in this case just means what you want the format condition to be for this range of cells.

Sub RefreshFormatCondition()
    Dim MyRange As Range
    Dim FC As FormatCondition
    Dim WS As Worksheet

    Set WS = ActiveSheet

    Set MyRange = WS.Range("A2:A" & WS.Range("A" & WS.Rows.Count).End(xlUp).Row)    'define the range containing the conditional formatting (CF)

    With MyRange
        .FormatConditions.Delete                      ' clear existing format conditions
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="Green"    'restore "default" format condition as you like
        Set FC = .FormatConditions(1)
        With FC.Interior
            .Color = 12379352                         'set formatting (background color, font, etc)
            .TintAndShade = 0
        End With
        With FC.Font
            .Bold = True                              'set formatting (background color, font, etc)
            .Italic = False
            .TintAndShade = 0
        End With
    End With
End Sub

Thanks much! Appreciate it.!

I wonder if this could be "further automated" so that you could read the condition in one part of the loop, then delete it, then restore it. In this example you put the condition that you want to restore in the code. In my case i have cells with quite a few conditions (5 or more sometimes) and often use the choice "use a formula to determine which cells to format". And, my CF covers alot of cells. So, it would be a 'pretty big' task to encode all of those CF in code. But, if code could read them itself and then re-write that would be great. Any thoughts? Thanks!

By the way my status is that i tried the "easy" things suggested here, save as xlsb and restore, and clean the code modules. It is not doing it, so need to move to more drastic measures.

Thank you.

 

[rlv01]

It does not really matter what format conditions are used or how many cells they apply to. Applying FCs to 1 cell or 10,000 cells requires approximately the same amount of coding effort. The same technique applies and I use the method on some large worksheets with multiple and complex FC's. The type of FC is not a big factor either. Anything you can manually set up while running the macro recorder to get the specific syntax for that type can be plugged into my example.