Excel – Add property to existing VBA class

classexcelvba

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)

Best Solution

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.

Related Solutions

C++ – When should you use a class vs a struct in C++

The differences between a class and a struct in C++ is:

struct members and base classes/structs are public by default.
class members and base classes/struts are private by default.

Both classes and structs can have a mixture of public, protected and private members, can use inheritance and can have member functions.

I would recommend you:

use struct for plain-old-data structures without any class-like features;
use class when you make use of features such as private or protected members, non-default constructors and operators, etc.

Python – Are static class variables possible in Python

Variables declared inside the class definition, but not inside a method are class or static variables:

>>> class MyClass:
...     i = 3
...
>>> MyClass.i
3

As @millerdev points out, this creates a class-level i variable, but this is distinct from any instance-level i variable, so you could have

>>> m = MyClass()
>>> m.i = 4
>>> MyClass.i, m.i
>>> (3, 4)

This is different from C++ and Java, but not so different from C#, where a static member can't be accessed using a reference to an instance.

See what the Python tutorial has to say on the subject of classes and class objects.

@Steve Johnson has already answered regarding static methods, also documented under "Built-in Functions" in the Python Library Reference.

class C:
    @staticmethod
    def f(arg1, arg2, ...): ...

@beidy recommends classmethods over staticmethod, as the method then receives the class type as the first argument, but I'm still a little fuzzy on the advantages of this approach over staticmethod. If you are too, then it probably doesn't matter.

Best Solution

Related Solutions

C++ – When should you use a class vs a struct in C++

Python – Are static class variables possible in Python

Related Question