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
ShisObject/MainTablewhen everything works as expected - The type of
ShisObjectwhen 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.
ListObjectin Excel, which is something entirely different.Case Sh Is MainTable?