Setup

Given the following code (inside “ThisWorkbook”) and the excel object “MainTable”, which is the auto generated class module of my first Table just renamed, I get unexpected behavior using TypeOf.

Behavior

I expect that “Main” will be print twice, when activating the first table.
If I close and open the workbook this is true. But when I reset the VBProject (using the stop button inside the VBA-editor) then CastAndPrintSheetName prints Main and PrintSheetName prints 1
Afterwards it works again as expected. But only because I accessed the Property Index and I guess initialized something?!

Additional info

What I also observed using the watch window:

• The type of Sh is Object/MainTable when everything works as expected
• The type of Sh is Object when it doesn’t work as expected

Code:

Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   
    CastAndPrintSheetName Sh
    PrintSheetName Sh
    
End Sub

Private Sub CastAndPrintSheetName(ByVal Sh As Worksheet)
    PrintSheetName Sh
End Sub

Private Sub PrintSheetName(ByVal Sh As Object)
        
    Select Case True
        
        Case TypeOf Sh Is MainTable
            Debug.Print "Main"
            
        Case TypeOf Sh Is Worksheet
            Debug.Print MainTable.Index
            
        Case Else
            Debug.Print "Unknown"
            
    End Select
    
End Sub

Does anyone know why this happens and/or if this is intended behavior? I don’t think so.