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

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.