为什么 VBA TypeOf 运算符会失败

Why would VBA TypeOf operator fail

提问by Pieter Geerkens

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:

几天来，我一直在与 Excel 2007 问题作斗争。以下是我能想到的所有可能相关的事实清单：

1. IDetailSheetis 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 Explicitis set in all modules.

3. Ten worksheets in the VBA project implementIDetailSheetand compile cleanly (as does the entire project).

4. CDetailSheetsis 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 IDetailSheeton all collection menmbers.

5. In its Class initializer (called from the Workbook_ Openevent handler and assigned to a global variable), CDetailSheetexecutes 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.

1. IDetailSheet是在 VBA 项目中声明的具有多种方法的类，它在其类初始值设定项中引发错误，因此无法实例化（使其抽象）。

2. 在所有模块中都设置了显式选项。

3. VBA 项目中的 10 个工作表实现了IDetailSheet并进行了干净的编译（整个项目也是如此）。

4. CDetailSheets是在 VBA 项目中声明的一个类，它包装了一个 Collection 对象，并将 Collection 对象公开为IDetailSheet 的一个集合。它还公开了一些额外的方法来对所有集合成员执行IDetailSheet 的某些方法。

5. 在其 Class 初始值设定项（从Workbook_ Open事件处理程序调用并分配给全局变量）中，CDetailSheet执行以下代码以填充私有集合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. 在某些功能区回调中，运行以下代码：

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

7. 在发现存在其他稳定性问题（最初有几十个）之后，所有 ActiveX 控件都已从工作簿中删除。已创建 Fluent Interface Ribbon 以替换最初与 ActiveX 控件关联的功能。

8. 公司模板中有一个 Hyperion 加载项，但未在本工作簿中使用。

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 ... Isin the Ribbon call-back.

• TypeOf Is在 CDetailSheets Initializer 中识别任意数量的 IDetailSheet 实例，从 1（最常见）到偶尔的 2 或 3。永远不会为零，永远不会超过 3，当然也不会是完整的 10 个可用的。（并不总是相同的，尽管靠近集合的前面似乎会增加被识别的可能性。）
• 在 CDetailSheets 初始值设定项中发现的 IDetailSheet 实现的任何实例（并且尽我所能确定，只有这样的实例）也被TypeOf识别......在功能区回调中。

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

谁能解释为什么大多数TypeOf ...操作失败了？或者如何解决这个问题？

I have resorted to manually creating v-tables (i.e. big ugly Select Case ... End Selectstatements) 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.

我已经使用手动创建 v 表（即大丑陋的Select Case ... End Select语句）来使功能正常工作，但实际上我发现将我的名字放在这样的代码旁边很尴尬。除此之外，我可以看到这是未来的维护噩梦。

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 IDetailSheetto make them miFab.IDetailSheet, but again to no avail. (miFabis the project name.)

考虑到这可能是一个陈旧的 p 代码问题，我从扩展的 XLSM zip 中删除了 Project.Bin 文件，然后手动将所有 VBA 代码重新导入。没有变化。我还尝试将项目名称添加到IDetailSheet 的所有用法中，使它们miFab.IDetailSheet，但再次无济于事。（miFab是项目名称。）

采纳答案by AndASM

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

有几种方法可以使用 CallByName 作弊。你将不得不以一种或另一种方式解决这个错误。

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.

每个以实现行开头的工作表都应该有一个公共的 GetType 函数。我将“TestSheet”子附加到功能区上的按钮。它将返回的类型名称放在单元格 A1 中以演示该函数。

Module1

模块1

'--- 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

表 1

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

Public Function Class1_TestFunction()
End Function

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

回答by ThunderFrame

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

我在发布我自己的类似问题后发现了这个问题，因为TypeOf 无法与实现接口的 Excel 工作簿的 ActiveSheet 一起使用

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).

我怀疑这是因为 [代码] 在 Sheet1 或 Chart 上实现了一个接口，并且正在扩展 Sheet1/Chart1，但是 Sheet1 已经在扩展工作表（而 Chart1 已经在扩展图表）。

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

在我的测试中，我能够TypeOf通过首先访问工作表的属性来强制 VBA 返回 的实际值。这意味着，做一些丑陋的事情，例如：

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

回答by Mark Hurd

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

如果您不信任TypeOf，请继续并忽略错误：

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'twork, the worksheets really aren't IDetailSheetat that time at least.

如果这不起作用，那么工作表IDetailSheet至少在那个时候真的不是。