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

TomCon

Active Member
Joined
Mar 31, 2011
Messages
303
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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

Well-known Member
Joined
May 16, 2017
Messages
1,149
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

TomCon

Active Member
Joined
Mar 31, 2011
Messages
303
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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

Active Member
Joined
Mar 31, 2011
Messages
303
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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

Active Member
Joined
Mar 31, 2011
Messages
303
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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.


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

Active Member
Joined
Mar 31, 2011
Messages
303
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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

Well-known Member
Joined
May 16, 2017
Messages
1,149
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
VBA Code:
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

Active Member
Joined
Mar 31, 2011
Messages
303
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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.
VBA Code:
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

Well-known Member
Joined
May 16, 2017
Messages
1,149
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,147,812
Messages
5,743,352
Members
423,789
Latest member
k10riley

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top