Sheet counts in VBA Project Explorer

I’ve been writing a simple (but for me pretty complicated) Excel 2003 VBA application, and the project generates a lot of charts and sheets as I test it. These get deleted in a cleanup subroutine that I use to start over back at square 1 for more testing.

I’m referring to the VBAProject Project Explorer pane display tree menu- top left on my screen. I can see the sheets’ numbers increasing steadily as I create and delete sheets for testing the code. For example, in my open workbook there is one sheet and it says “Sheet301 (Sheet 1)”. When I add a new sheet, it says Sheet397 (Sheet133).

Since it seems like this number gets incremented automatically, Is Excel still keeping track of all my deleted stuff and is this going to come back and bite me in the ass at a later time?

The deleted stuff is gone forever.

When I tested your scenario with Excel 2003 I found the sheet numbers auto increment UNTIL you save & close the workbook.

So starting from scratch with a fresh workbook:

If you create 2 sheets, they are 1 & 2. Then delete 2. The next one you create will be 3. The number 1 is not recycled.

Save & close the workbook. Now repoen it. When you create a new sheet it’ll be #1. The next would be 2, then 4, since 3 is already taken.

There is some upper limit on that number between opening & closing the workbook. The lowest likely candidate is 32,768, and it might be more like 4.3 billion. Not likely you’ll hit it during a day’s work.

When doing development, a good technique is to save often (like every 5 minutes) & close & reopen at least twice a day, if not hourly. Over the course of a year you’ll save yourself one really awful Friday afternoon with this technique.