为什么VBA TypeOf运算符会失败 [英] Why would VBA TypeOf operator fail

问题描述

几天来我一直在解决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项目中声明的类，它具有多种方法，并且在其Class Initializer中引发错误，因此无法实例化(使其抽象化).

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

Option Explicit .

VBA项目实现 IDetailSheet 中的十个工作表，并进行干净地编译(整个项目也是如此).

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

CDetailSheets 是VBA项目中声明的类，该类包装Collection对象，并将Collection对象公开为 IDetailSheet 的Collection.它还公开了一些其他方法，可以在所有集合成员上执行 IDetailSheet 的某些方法.

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.

在其类初始化程序(从 Workbook _ Open 事件处理程序调用并分配给全局变量)中，执行 CDetailSheet 以下代码填充私有集合 DetailSheets :

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

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

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控件都已从工作簿中删除(最初有几十个).已经创建了Fluent界面功能区，以取代最初与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:

• 在CDetailSheets初始化程序中， TypeOf是可以识别IDetailSheet的任何数量的实例，从1(最常见)到偶发2或3.从不为零，从不超过3，并且最肯定地为从不完整的10个可用. (虽然并不总是同一个人，尽管靠近该组合的前面似乎会增加被识别的可能性.)
• 在CDetailSheets初始化程序中发现了IDetailSheet实现的所有实例(据我所知，只有这样的实例)也被Ribbon回调中的 TypeOf ... Is 识别.

• 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表(例如，大而丑陋的 Select Case ... End Select 语句)以使功能正常工作，但实际上让我的名字旁边很尴尬这样的代码.除此之外，我可以看到这是未来的维护梦night.

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.

考虑到这可能是过时的p代码问题，我决定从扩展的XLSM zip中删除Project.Bin文件，然后手动将所有VBA代码重新导入.没有任何变化.我还尝试将项目名称添加到 IDetailSheet 的所有用法中，使它们成为 miFab.IDetailSheet ，但同样无济于事. ( miFab 是项目名称.)

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

'--- 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运算符会失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！