Why would VBA TypeOf operator fail

Question

I have been fighting with an Excel 2007 problem for several days now. Below is a listing of all facts I can think of that might be relevant:

1. IDetailSheet is a class declared in the VBA project with several methods, and which throws an error in its Class Initializer so that it cannot be instantiated (making it abstract).

2. Option Explicit is set in all modules.

3. Ten worksheets in the VBA project implement IDetailSheet and compile cleanly (as does the entire project).

4. CDetailSheets is a class declared in the VBA project that wraps a Collection object and exposes the Collection object as a Collection of IDetailSheet. It also exposes some additional methods to perform certain methods of IDetailSheet on all collection menmbers.

5. In its Class initializer (called from the Workbook_ Open event handler and assigned to a global variable), CDetailSheet executes the following code to populate the private collection DetailSheets:

   Dim sht as EXCEL.WorkSheet
   For Each sht in ActiveWorkbook.Worksheets
     If TypeOf sht is IDetailSheet Then
       Dim DetailSheet as IDetailSheet
       Set DetailSheet = sht
       DetailSheets.Add DetailSheet, DetailSheet.Name
     End If
   Next sht
   

6. In certain Ribbon call-backs the following code is run:

      If TypeOf ActiveWorkbook.ActiveSheet is IDetailSheet Then
         Dim DetailSheet as IDetailSheet
         Set DetailSheet = ActiveWorkbook.ActiveSheet
         DetailSheet.Refresh  *[correction]*
      End If
   

7. All ActiveX controls have been removed from the Workbook, after having been identified with other stability issues (There were a few dozen originally). A Fluent Interface Ribbon has been created to replace the functionality originally associated with the ActiveX controls.

8. There is a Hyperion add-in from the corporate template, but it is not used in this workbook.

When all is said and done, the following symptom occurs when the workbook is run:

• Any number of instances of IDetailSheet are recognized in the CDetailSheets Initializer by TypeOf Is, from 1 (most common) to occasionally 2 or 3. Never zero, never more than 3, and most certainly never the full 10 available. (Not always the same one, though being near the front of the set seems to increase likelihood of being recognized.)
• Whichever instances of IDetailSheet implementation are discovered in the CDetailSheets initializer (and as near as I can determine, only such instances) are also recognized by TypeOf ... Is in the Ribbon call-back.

Can anyone explain why most of the TypeOf ... Is operations are failing? Or how to fix the issue?

I have resorted to manually creating v-tables (i.e. big ugly Select Case ... End Select statements) to get the functionality working, but I actually find it rather embarrassing to have my name beside such code. Besides which, I can see that being a future maintenance nightmare.

Thinking that it might be a stale p-code issues, I went to the extent of deleting the Project.Bin file from the expanded XLSM zip, and then manually importing all the VBA code back in. No change. I also tried adding the project name to all the usages of IDetailSheet to make them miFab.IDetailSheet, but again to no avail. (miFab is the project name.)

Answer 1

There are a few ways you could cheat using CallByName. You're going to have to work around this bug one way or another.

A quick dirty example

Every sheet that starts with an implementing line should have a public GetType function. I attached the "TestSheet" sub to a button on my ribbon. It puts the returned type name in cell A1 to demonstrate the function.

Module1

'--- Start Module1 ---
Option Explicit

Public Sub TestSheet()
  Dim obj As Object
  Set obj = ActiveSheet
  ActiveSheet.[A1] = GetType(obj)
End Sub

Public Function GetType(obj As Object) As String
  Dim returnValue As String
  returnValue = TypeName(obj)
  On Error Resume Next
  returnValue = CallByName(obj, "GetType", VbMethod)
  Err.Clear
  On Error GoTo 0
  GetType = returnValue
End Function
'--- End Module1 ---

Sheet1

'--- Start Sheet1 ---
Implements Class1
Option Explicit

Public Function Class1_TestFunction()
End Function

Public Function GetType() As String
    GetType = "Class1"
End Function
'--- End Sheet1 ---

Answer 2

I found this question after posting my own similar issue as TypeOf fails to work with Excel workbook's ActiveSheet that implements interface

I don't have a definitive explanation, but I think I do have a workaround.

I suspect it is because [the code] is implementing an interface on Sheet1 or Chart, and is extending Sheet1/Chart1, but Sheet1 is already extending Worksheet (and Chart1 is already extending Chart).

In my testing, I am able to force VBA to return the real value of TypeOf by firstly accessing a property of the sheet. That means, doing something ugly like:

'Explicitly access ThisWorkbook.ActiveSheet.Name before using TypeOf
If TypeOf ThisWorkbook.Sheets(ThisWorkbook.ActiveSheet.Name) Is PublicInterface Then

Answer 3

If you're not trusting TypeOf, plough on and ignore errors:

Dim sht as EXCEL.WorkSheet
For Each sht in ActiveWorkbook.Worksheets
  'If TypeOf sht is IDetailSheet Then
  Dim DetailSheet As IDetailSheet
  On Error Resume Next
  Set DetailSheet = sht
  On Error GoTo 0
  If Not DetailSheet Is Nothing Then
    DetailSheets.Add DetailSheet, DetailSheet.Name
  End If
Next sht

If this doesn't work, the worksheets really aren't IDetailSheet at that time at least.