将属性添加到现有 VBA 类
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/284985/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Add property to existing VBA class
提问by Ryan Shannon
I would like to do something like add a nice-to-Excel-functions Nameproperty to the WorkBookclass. Is there a good way to do this?
我想做一些事情，比如向类添加一个对 Excel 函数很好的Name属性WorkBook。有没有好的方法可以做到这一点？
More detailed problem: In VBA you can assign a formula to a range in an Excel worksheet. I want to do so, and I want my formula to refer to a second workbook, which is an object called wbin my code. I then use wb.Namein assigning a formula to a range.
更详细的问题：在 VBA 中，您可以为 Excel 工作表中的区域分配一个公式。我想这样做，并且我希望我的公式引用第二个工作簿，它是wb我的代码中调用的对象。然后我用于wb.Name将公式分配给一个范围。
The problem arises when wb.Namehas a single-quote in it. Then you wind up with something like this:
当其中wb.Name包含单引号时会出现问题。然后你会得到这样的结果：
=MONTH('[Ryan's WB]Sheet1'A1)
in the spreadsheet, which fails because the single-quote in the workbook name matches to the first single-quote.
在电子表格中失败，因为工作簿名称中的单引号与第一个单引号匹配。
What I would like is a FunNameproperty for the WorkBookclass that replaces all single-quotes in the Nameproperty with two single-quotes and returns that. Then the above formula would properly wind up looking like
我想要的是类的一个FunName属性，用两个单引号WorkBook替换属性中的所有单Name引号并返回它。那么上面的公式会正确地结束看起来像
=MONTH('[Ryan''s WB]Sheet1'A1)
回答by Dick Kusleika
You don't need to make a separate class to extend the workbook class. You can add properties to the existing ThisWorkbook class module, like this:
您不需要创建单独的类来扩展工作簿类。您可以向现有的 ThisWorkbook 类模块添加属性，如下所示：
Public Property Get FunName() As String
FunName = Replace(Me.Name, "'", "''")
End Property
Then you call ThisWorkbook.FunNameto get your cleaned up name. However, this code has to exist in the workbook at hand. If you want it to work on anyworkbook, your function is the way to go.
然后你打电话ThisWorkbook.FunName给你清理过的名字。但是，此代码必须存在于手头的工作簿中。如果您希望它适用于任何工作簿，那么您的函数就是要走的路。
回答by Ryan Shannon
The final answer appears to be that the WorkBook class can be extended to include a name property that is nice to Excel formulas. This can be done using the method provided by dbb. However, since VBA does not support inheritance, objects of the extended class will have only the properties you define for them.
最终的答案似乎是 WorkBook 类可以扩展为包含一个对 Excel 公式有用的 name 属性。这可以使用 dbb 提供的方法来完成。但是，由于 VBA 不支持继承，扩展类的对象将只有您为它们定义的属性。
Therefore, it really makes more sense to just use a function. Here's what I'm going to use:
因此，仅使用函数确实更有意义。这是我将要使用的：
Function FormulaWorkName(ByVal aName As String) As String
FormulaWorkName = Replace(aName, "'", "''")
End Function
Which I will apply to both worksheet names and workbook names.
我将应用于工作表名称和工作簿名称。
回答by DJ.
Just do a replace to double up the single quotes
只需进行替换即可将单引号加倍
WorksheetName = Replace(WB.Name, "'", "''")
回答by dbb
What you need is a Class which extends the Workbook object. Insert a class module, then try the following code
您需要的是一个扩展 Workbook 对象的类。插入一个类模块，然后试试下面的代码
Dim WithEvents WB As Workbook
Public Sub SetWB(W As Workbook)
Set WB = W
End Sub
Public Property Get FunName() As String
FunName = Replace(WB.Name, "'", "''")
End Property
Private Sub WB_SheetCalculate(ByVal Sh As Object)
'this runs when WB calculates
End Sub
'use it like this
'像这样使用它
Dim WB As New wbClass
WB.SetWB ActiveWorkbook
CleanedName = WB.FunName
Note that as a bonus, I've put WithEvents in the line that Dims WB at the top of the class. This allows you to trap all the events that happen to WB, and I included the Calculate event as a demo above. If you are in the class code and click the objects dropdown at top left of the code pane, you'll see the WB object, and if you click this, the right hand list box will give you all the events to choose from.
请注意，作为奖励，我将 WithEvents 放在 Dims WB 类顶部的行中。这允许您捕获发生在 WB 上的所有事件，我将计算事件作为上面的演示包含在内。如果您在类代码中并单击代码窗格左上角的对象下拉列表，您将看到 WB 对象，如果单击它，右侧的列表框将为您提供所有可供选择的事件。
