1

I am trying to get a count of worksheets in the active workbook. When I run Application.Sheets.Count the value I get is one off from what I expected I should be getting 76 and I get 75. In the project explorer I found a strange sheet labeled ThisWorkbook (See Sheet 52 in the image below). I would really like to understand this behavior as it has an impact on my later work. Any insight would be greatly appreciated.

Private Sub UserForm_Initialize()
    Dim wsCnt As Long 
    wsCnt = Application.Sheets.Count
    MsgBox wsCnt
End Sub

enter image description here

Edit: In light of some of the contributions below, I offer the following to further clarify what I'm seeing and to provide greater insight into why it is important within this context.

The above image is now a more complete picture of what I'm seeing. Note that ThisWorkbook is at the bottom of the Project Explorer window. For some reason however, sheet 52 is also called ThisWorkbook.

This is contextually important because this value is later used to increment through all created sheets, the number of which will change over time. Since Sheet52 increments the sheet count but not the number of sheets, I'm coming up short by one. I could conceivably introduce a workaround by just adding one, then checking to ensure that the sheet value is not equal to 52 on each iteration of the loop but in addition to being an inelegant approach this may also cause issues in the future if the anomaly crops up again. I would really like to understand this behavior. I haven't found anything in the documentation to indicate that this sheet needs to be created and I haven't seen a ThisWorkbook sheet being created before like the case of Sheet52 nor can I fathom any reason why it would need to be created. I defer to the collective wisdom of the community.

Edit 2: Concerning the duplicate question

The suggestion that this question is a duplicate emanates from my curiosity as to what was happening to generate Sheet52. The question however, pertains to getting the wrong sheet count, which is not addressed in that post. This information was instructive in some regards and did ultimately get me closer to the answer.

Bar-Tzur
  • 85
  • 1
  • 10
  • 1
    See the [`ThisWorkbook`](https://learn.microsoft.com/en-us/office/vba/api/excel.application.thisworkbook) documentation. It doesn't represent a `Sheet`, but a `Workbook`. – BigBen May 15 '19 at 21:17
  • 1
    Now that you have shown us the full picture, it looks like your file is corrupt. I suggest you rebuild it. – chris neilsen May 16 '19 at 20:43
  • @chris Thanks for pointing me to this resource. This may be the reason for the sheet52 and I'm grateful to have a clearer understanding of the problem however, this does not resolve the issue I encountered. I tried both rebuilding the file and saving it as a binary worksheet but the sheet count is still off, now missing sheet 54. Both of these result in the same problem; I need to retrieve the number of worksheets but am still getting the sheet count-1. How to do that is the question that I need answered. – Bar-Tzur May 17 '19 at 15:43
  • @baz the point is that once you have fixed the corruption, `Worksheets.Count` _will_ give you the correct value. – chris neilsen May 17 '19 at 22:56
  • @chris That was part of the issue but I was still having a problem with the sheet codenames. Once I transferred all sheets to a new workbook, and saved it as a binary workbook just to be safe, I still had to use `ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "NewCodeName"` with a `for` loop to rename each sheet between 54 and the last sheet in the workbook . – Bar-Tzur May 20 '19 at 12:50

1 Answers1

0

As @BigBen mentions, look further into the documentation on ThisWorkbook.

Modified code should work out:

Private Sub UserForm_Initialize() 

   Dim wsCnt As Long 
   wsCnt = ActiveWorkbook.Worksheets.Count

   MsgBox wsCnt

End sub
Havard Kleven
  • 422
  • 6
  • 19