Modify an existing VBA class

Question

I would like to know if there is some way to add your own methods/properties to an existing VBA class (such Range, Charts, etc).

An example: I would like the currently VBA class Worksheet have a specific method done by myself, something like:

'Worksheet methods
Public Sub LookFor (ByVal Value as String)
    'My code
End Sub

Then I can call from any declared Worksheet class this function.

In class MyClass:

'MyClass members
Private pWS1 as Worksheet
Private pWS2 as Worksheet
Private pWS3 as Worksheet


'MyClass methods
Private Sub Class_Initialization()
    Set pWS1 = Worksheets("WS1")
    Set pWS2 = Worksheets("WS2")
    Set pWS3 = Worksheets("WS3")
End Sub

Public Sub Example()
    pWS1.LookFor("abc")
    pWS2.LookFor("123")
    pWS3.LookFor("def")
End Sub    

Thanks!

Answer 1

There is no direct way to do this in VBA.

Best you can do is create a "wrapper" class which has a private Worksheet member, and expose that via a Sheet property. Add your "extension" methods to the class and have them operate on m_sheet.

Initialize your class by creating an instance of it and assigning a worksheet object to its Sheet property.

You can call your "extension" methods directly on the object, and any existing methods you'd access via the Sheet property.

Class MySheet:

Private m_sht As Worksheet


Public Property Set Sheet(ws As Worksheet)
    Set m_sht = ws
End Property

Public Property Get Sheet() As Worksheet
    Set Sheet = m_sht
End Property

Public Property Get CountHellos() As Long
    CountHellos = Application.CountIf(m_sht.Cells, "Hello")
End Property

Test sub:

Sub Tester()

    Dim sht As MySheet

    Set sht = New MySheet

    Set sht.Sheet = ActiveSheet

    MsgBox sht.CountHellos '<< "extension" method

    MsgBox sht.Sheet.Rows.Count '<< built-in object property

End Sub

Edit: you might be able to make the Sheet property the default for your class by following the steps outlined by Chip here: http://www.cpearson.com/excel/DefaultMember.aspx

May work to allow you to skip the Sheet property when working with instances of your class (but I've not tested this)