Add property to existing VBA class
I would like to do something like add a Name
nice-to-Excel -s property to the class WorkBook
. Is there a good way to do this?
More detailed problem: In VBA, you can assign a formula to a range in an Excel sheet. I want to do this, and I want my formula to reference the second book, which is a named object wb
in my code. Then I use wb.Name
when assigning a formula to the range.
The problem arises when it wb.Name
has a single quote in it. Then you end up with something like this:
=MONTH('[Ryan WB]Sheet1'A1)
in a spreadsheet that fails because the single quote in the book title matches the first single quote.
I would like to use a property FunName
on a class WorkBook
that replaces all single quotes in the property Name
with two quotes and returns them. Then the above formula should look like
=MONTH('[Ryan' WB]Sheet1'A1)
source to share
You don't need to create a separate class to extend the workbook class. You can add properties to an existing module of the ThisWorkbook class, for example:
Public Property Get FunName() As String
FunName = Replace(Me.Name, "'", "''")
End Property
Then you call ThisWorkbook.FunName
to get your cleared name. However, this code must exist in the workbook. If you want it to work on any book, your function is the way to go.
source to share
Finally, the final answer is that the WorkBook class can be extended to include a name property that works well for 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 only have the properties that you define them.
So it makes sense to just use a function. This is what I will be using:
Function FormulaWorkName(ByVal aName As String) As String
FormulaWorkName = Replace(aName, "'", "''")
End Function
Which I will apply to worksheet names and book names.
source to share
You need a class that extends the Workbook object. Insert class module then try following code
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 put WithEvents in the Dims WB line at the top of the class. This allows you to capture all the events happening to the WB and I have included the Calculate event as a demo above. If you are in the code of a class and click the dropdown list of objects in the upper left corner of the code window, you will see the WB object, and if you click that, in the right list you will see all the events to choose from.
source to share