Excel VBA Project has generated multiple Workbook objects

Question

I have an Excel 2010 spreadsheet with links to external data sources including Bloomberg, 65 worksheets with VBA modules and references to other VBA add-ins.

The VBA project has acquired multiple Workbook Objects.

A number of the worksheets have been turned into Workbook objects, 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, a worksheet called wksInputs has now been turned into a Workbook object, and the original wksInputs is now called wksInputs1.

I can't delete the wksInputs Workbook object.

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.