1

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)
Improve this question

4 Answers 4

Reset to default
3

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:

Public Property Get FunName() As String

    FunName = Replace(Me.Name, "'", "''")

End Property

Then you call ThisWorkbook.FunName to get your cleaned up name. However, this code has to exist in the workbook at hand. If you want it to work on any workbook, your function is the way to go.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Just do a replace to double up the single quotes

WorksheetName = Replace(WB.Name, "'", "''")
Improve this answer

Comments

1

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.

Improve this answer

3 Comments

Absolutely perfect! This is exactly what I was looking for. Thank you!
OK, nearly perfect. It appears now that wbClass objects only have access to the FunName property. Is there some way to retain the entirety of the original WB object and add the FunName property?
Unfortunately not, VBA doesn't support inheritance, you have to replicate anything you want. (Personally, I would just add a normal Function to do this, rather than extending objects).
1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.