Excel VBA Project has generated multiple Workbook objects

Question

I am responsible for a very large Excel 2010 spreadsheet with links to all sorts of external data sources including Bloomberg, 65 worksheets with vba modules and references to other vba add-ins.

I have noticed that the VBA project has acquired multiple Workbook Objects.

There's the standard ThisWorkbook. However, a number of the worksheets have also been turned into Workbook objects by Excel, leaving the original worksheet as a copy of the previous one, minus the code.

This doesn't appear to be a result of anyone's actions. Indeed, I didn't think it possible to have more than one Workbook object!

For example, I had one worksheet called wksInputs which has now been turned into a Workbook object, and the original wksInputs is now called wksInputs1.

I can't delete the wksInputs Workbook object.

Please could someone help explain what's going on here, and how I might be able to resolve the problem...?

Many thanks.

Answer 1

This problem occurred in my code when I passed a worksheet to a Sub as a parameter, like this:

Call BuildCodeStrings(Sheet2, sAccount)
Sub BuildCodeStrings(wsSource As Worksheet, s As String)

To fix the problem, I created a new workbook, copied all the data from all the legitimate sheets in my original into identically named sheets in my new workbook. Then copied all the code from the original to the new workbook.

Then I changed the subroutine call to

Call BuildCodeStrings("IC Accounts", sAccounts)
Sub BuildCodeStrings(sSource As String, s As String)

and added one line of code to my subroutine BuildCodeString:

Set wsSource = ThisWorkbook.Sheets(sSource)

I don't know what causes this problem, but this workaround worked for me.

Answer 2

Here is my solution, it works consistently and you don't need to manually copy the sheets and code across to a blank workbook. I've tested this method on several corrupted workbooks that were giving me the "Automation error - Catastrophic failure" error upon launch.

NOTE: Original corrupted file was saved as .xlsm

1. Open a blank Excel workbook
2. Developer tab > Macro security > Disable all macros without notification
3. Close Excel
4. Double-click corrupted file, for example, MyFile.xlsm
5. File > Save as... > MyFile.xlsb (not .xlsm), choosing the .xlsb format is what does the trick
6. Developer tab > Macro security > Enable all macros (or whatever level of security you prefer)
7. Close Excel
8. Double-click MyFile.xlsb

The file is now fixed! You can re-save the MyFile.xlsb file as .xlsm if required. In my experience .xlsm files become corrupted quite easily, so I'm going to get into the habit of always using the .xlsb format.

Hope somebody finds this helpful :)

Answer 3

I had the same issue with a file that had multiple workbook objects and was generating the "Automation error - Catastrophic failure" error when opened.

I saved the *.xlsm file as an *.xlsb. When I re-opened the *.xlsb file all the workbook objects were still in the file. I reasonably assumed that the errors in the file were going to cause problems eventually and resigned myself to copy everything to a new file.

However when I closed the *.xlsb file and re-opened the original *.xlsm all the objects had disappeared and the file wasn't generating the "Automation error - Catastrophic failure" error.

Weird I admit but the problem still persisted in the *.xlsb file but the original *.xlsm (which is the one I was trying to save) was fine.

It may be a one off but it might be worth a try...

Answer 4

You can introduce this behavior by doing the "convert to .zip and unzip trick", and then replacing the vbaProject.bin file (the macro code file) with a vbaProject.bin from a different project that has a different sheet structure. Don't know if that's what happened to OP, but it's what happened to me.

Answer 5

I had the same issue in PowerPoint (2007), where "Slide1" was empty and couldn't be removed. The answer by @Scoox pointed me to a solution:

1. Export all VBA Modules into text (.bas) files
2. Save the .pptm (or .xlsm) file as a .pptx (or .xlsx)
3. Close PowerPoint (or Excel)
4. Open this .pptx/.xlsx and save it back as a .pptm/.xlsm
5. Import the original VBA text (.bas) files
6. Manually re-associate all buttons to original macro functions
7. Add any external Reference you had in original file
8. Save and test if everything's fine

This worked for me, I believe it would work with Excel, too.

Answer 6

Had the same issue with Office365, found a mistake where I had the same name for a public constant and a parameter for a function. After changing the parameter name in the function it did not happen again.

Answer 7

My error was caused when I passed a worksheet to a function and then used that variable name later on in a for loop as the loop variable (because I'm a bad and lazy programmer.) I had Option Explicit in the module so I don't think it knew what to do with the references.

As an alternative to completely reworking the workbook:

1. Copy all the worksheets to a new workbook
2. Copy all the modules, forms and classes to that as well
3. Save that as an .xlsm
4. Change both of the file names (new and old) to .zip
5. Copy the file "xl/vbaProject.bin" from the new one to the old one
6. Change the file name back to .xlsm

This sounds like you just copied everything anyway but this means that you don't have to check links, named ranges, conditional formatting or data validation to ensure everything copied over correctly.