Broken worksheet and workbook objects in VBA Object Browser

Question

It seem that with some macro workbooks, when I save, close and then reopen them, the worksheet and workbook objects get duplicated with an additional '1' appended to their names.  The new objects become the active objects.  But, since any code that was in the original objects stays in the original objects and is not duplicated to the new objects, the active sheets do not have access to them.

See an example here:

Just seeing two Workbook objects should already be cause for alarm for any VBA coder.

Now of course, the active workbooks and worksheets normally cannot be removed in the Object Browser, but why can't I remove the broken ones?

Secondly, why is this happening in the first place?

• I don't have any shapes, ActiveX objects or anything else that I added to my sheet.  Just values, text and formatting.  Any conditional formatting too but I make sure that the references are always cleaned up.
• The code in the workbook is only a 'WorkbookChange' routine to respond to a certain keyword entered in in a cell and change it to a number.  There is no infinite loop.
• Only have the standard references loaded:
• 

Answer 1

Show me such a file, I'll take a look.

IMPORTANT: Zip your file!
https://support.microsoft.com/en-us/windows/zip-and-unzip-files-8d28fa72-f2f9-712f-67df-f80cf89fd4e5

Login to https://onedrive.live.com (Use the same Login ID and password as for this forum).
Click Upload in the top and choose your file.
After uploading, right click the file and choose Share.
Click Copy Link in the lower left edge (no need to enter an email).
Copy the link and paste it here.

Andreas.

Answer 2

Thanks Andreas,

Here is a link to such a file: https://drive.google.com/file/d/1j-zaKKMt4sxZT6isTWuRVObGmP-fP1AU/view?usp=sharing

The other current offender is my work's timesheet file. This is an important one to fixe because I have to export the active sheet to a new workbook every time I want to submit my hours.

Answer 3

I can not reproduce the issue. You said this happens every time when you save, close and then reopen them.

Please follow these steps:

Close Excel
Press and hold the CTRL key
Open Excel
Wait for a message to appear and ask for "Safe Mode"
Release the CTRL key
Click Yes
After Excel opens
Click File\Open and select your file
Press and hold the SHIFT key
Click Open
Wait for your file to open
Release the SHIFT key
Save, close and reopen the file

What happens?

Andreas.

Answer 4

Which part could you not reproduce? Is it that you did not see the duplicated objects in the object viewer? Or that you were not able to cause a duplication of what was already there?

I think there is a deeper bug in Excel that everyone else may not be experiencing. I have used a few computers over the years and the bug comes up once in a while. Maybe I am using an old API that is only slightly broken. But it can't be that I'm the only one that this happens to.

Anyway, I created a fresh file by copying the sheets to a new workbook and cut&pasted the code to Notepad and back to make sure I had no crazy characters. Then I followed your steps except that when I 'shift-opened' the file I choose to disable the macros (you didn't specify). Once I reopened the file I had no issues with the objects duplicating and tried opening/saving/closing several times.

It seems to have fixed it for now but do you have some insight on what the procedure was supposed to do? You gave me the band aid but it would be nice to get closer to the fix of the problem.

I will also try it on my company's timesheet file and let you know.

Answer 5

It seems to have fixed it for now but do you have some insight on what the procedure was supposed to do? You gave me the band aid but it would be nice to get closer to the fix of the problem.

I don't really think so. ;-) It would mean you would have to deal with the internal structures and all the details of how Excel works and how the data is stored.

What I can definitely say is that the VBAproject is intact and working, even if it looks strange. In general, you cannot remove this type of module because the VBA interface does not allow this.

When I view the binary structure of the VBAproject I can see that e.g. the module header of ThisWorkbook is okay

Attribute VB_Name = "ThisWorkbook"

Attribute VB_Base = "0{00020819-0000-0000-C000-000000000046}"

Attribute VB_GlobalNameSpace = False

Attribute VB_Creatable = False

Attribute VB_PredeclaredId = True

Attribute VB_Exposed = True

Attribute VB_TemplateDerived = False

Attribute VB_Customizable = False

Option Explicit

And the "unusual" module ThisWorkbook1 has nearly the same settings

Attribute VB_Name = "ThisWorkbook1"

Attribute VB_Base = "0{00020819-0000-0000-C000-000000000046}"

Attribute VB_GlobalNameSpace = False

Attribute VB_Creatable = False

Attribute VB_PredeclaredId = True

Attribute VB_Exposed = True

Attribute VB_TemplateDerived = False

Attribute VB_Customizable = True

Option Explicit

If you want to have look at the details what all that means start here

https://docs.microsoft.com/en-us/openspecs/microsoft_general_purpose_programming_languages/ms-vbal/4599fae2-3f41-4e70-968e-2398741f446b

However, you can do some research and try to understand what this means, but it doesn't really get you closer to the cause of the problem.

Is that a bug in Excel? No.

My procedure opens Excel in safe mode, means you just load Excel, no AddIns, no additional files like PERSONAL.XSLB nor anything else. If that solves the issue means further one of your (i guess COM-) AddIns is causing the issue.

It might also be a problem of your locale PC / installation issue, maybe you mixed up some system DLLs from different Office versions. But that are guessing games.

I would try an Online repair, in most cases this solves such strange issues.

Repair an Office application - Office Support

Andreas.