VBA: Add property to class

Question

I would like to do something like add a nice-to-Excel-functions Name property to the WorkBook class. Is there a good way to do this?

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 wb in my code. I then use wb.Name in assigning a formula to a range.

The problem arises when wb.Name has a single-quote in it. Then you wind up with something like this:

=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 FunName property for the WorkBook class that replaces all single-quotes in the Name property with two single-quotes and returns that. Then the above formula would properly wind up looking like

=MONTH('[Ryan''s WB]Sheet1'A1)

Answer 1

Just do a replace to double up the single quotes

WorksheetName = Replace(WB.Name, "'", "''")

Answer 2

What you need is a Class which extends the Workbook object. Insert a class module, then try the 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'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.

Answer 3

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.

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.