0

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.

6
  • Can repro. For clarity, it might be better to refer to it as a worksheet, not a table. Table generally refers to a ListObject in Excel, which is something entirely different. Commented May 24, 2024 at 12:33
  • 1
    How about Case Sh Is MainTable? Commented May 24, 2024 at 12:38
  • I get Main x 2 when activating the "MainTable" sheet. Commented May 24, 2024 at 16:20
  • @TimWilliams Did you reset (Sometimes you need to click reset multiple times ;)) Commented May 27, 2024 at 5:57
  • @BigBen Didn't know you can use Is without typeOf. Can you explain the difference. Worksheet and MainTable are both classes. But you need typeof when using is with Worksheet. Commented May 27, 2024 at 5:59

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.