为什么VBA TypeOf操作符在EXCEL中失败 [英] Why would VBA TypeOf operator fail in EXCEL

问题描述

我已经与Excel 2007问题争夺了好几天了。以下是我可以想到的所有事实的列表，可能是相关的：

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 是一个声明的类在VBA项目中有几种方法，并在其类初始化程序中引发错误，使其无法实例化（使其成为抽象的）。


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


3. VBA项目中的十个工作表实现 * IDetailSheet *，并且整体编译（和整个项目一样）。


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



5. 在其类初始化器（从工作簿 _ 打开事件处理程序并分配给全局变量），CDetailSheet 执行以下代码以填充专用集合 DetailSheets ：< br>
   

Dim sht as EXCEL.WorkSheet

ActiveWorkbook.Worksheets中的每个sht

如果TypeOf sht是IDetailSheet然后

Dim DetailSheet作为IDetailSheet

设置DetailSheet = sht

DetailSheets.Add DetailSheet，DetailSheet.Name

End If

下一个sht



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


在某些功能区回调中，运行以下代码：


如果TypeOf ActiveWorkbook.ActiveSheet是IDetailSheet然后

Dim DetailSheet作为IDetailSheet

设置DetailSheet = ActiveWorkbook.ActiveSheet

Detai lSheet.Refresh [更正]

如果


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

所有的ActiveX控件已经被删除从工作簿，在被确定与其他稳定性问题（最初有几打）。已创建流畅的界面功能区以替代与ActiveX控件最初关联的功能。

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.

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

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:

• IDetailSheet的任何数量的实例都可以通过 TypeOf Is 从CDetailSheets Initializer中识别，从1（最多共同）偶尔2或3.从不零，从不超过3，最肯定永远不会全部10可用。 （不一定是一样的，虽然在集合的前面似乎增加了被认可的可能性）。


• 在CDetailSheets初始化程序中发现了IDetailSheet实现的任何实例正如我可以确定的那样，只有这样的实例）也被功能区回调中的 TypeOf ... 识别。

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

任何人都可以解释为什么大部分 TypeOf ...是操作失败？还是如何解决问题？

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

我已经诉诸手动创建v表（即大丑陋选择案例...结束选择语句）以使功能正常工作，但实际上我觉得在我的名字旁边放一个这样的代码是很尴尬的。此外，我可以看到，这是一个未来的维护噩梦。

I have resorted to manually creating v-tables (ie 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.

更新：

认为它可能是陈旧的p代码问题，我从扩展的XLSM zip中删除了Project.Bin文件，然后手动导入所有的VBA代码。没有变化。我还尝试将项目名称添加到所有使用IDetailSheet 的用法中，使其成为miFab.IDetailSheet ，但再次无效。 （ miFab 是项目名称。）

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

推荐答案

有几种方法可以使用CallByName 。您将不得不以这种方式解决此问题。

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

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

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.

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

Sheet1

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

Public Function Class1_TestFunction()
End Function

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

这篇关于为什么VBA TypeOf操作符在EXCEL中失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！