Extending Built-in Classes

I’m catching up on Doug Glancy’s blog, yoursumbuddy, and in particular Building a Workbook Table Class. I wondered if it would work better inside the workbook class rather than in its own. Most classes in Excel don’t come with a user interface, but the Workbook class does – the ThisWorkbook module. In that module, you can create your own properties and methods as I’ve done here.

In the ThisWorkbook module

Private mcolTables As Collection

Private Sub Initialize()
    If mcolTables Is Nothing Then
        Set mcolTables = New Collection
    End If
End Sub

Public Sub RefreshTables()
    Dim sh As Worksheet
    Dim lo As ListObject
    For Each sh In Me.Worksheets
        For Each lo In sh.ListObjects
            On Error Resume Next
                mcolTables.Add lo, lo.Name
            On Error GoTo 0
        Next lo
    Next sh
End Sub

Public Property Get Table(vItm As Variant) As ListObject
    Set Table = mcolTables(vItm)
End Property

Public Property Get TableCount() As Long
    TableCount = mcolTables.Count
End Property

Public Property Get Exists(vItm As Variant) As Boolean
    Dim lo As ListObject
    On Error Resume Next
        Set lo = mcolTables(vItm)
    On Error GoTo 0
    Exists = Not lo Is Nothing
End Property

I was trying to replicate what Doug did, but it’s not a perfect match. I made the Item property a Table property. Item obviously doesn’t work in this context because you’d expect an Item to be a Workbook class. But I’ve gotten away from using Item in custom class modules altogether. Here’s what the standard module code looks like

Sub TestTableClass()
    Dim i As Long
    Dim lo As ListObject
    With ThisWorkbook
        Debug.Print "Number of tables in book:", .TableCount
        For i = 1 To .TableCount
            Debug.Print "Table(" & i & ") name:", .Table(i).Name
        Next i
        For i = 1 To .TableCount
            Set lo = .Table(i)
            Debug.Print lo.Name & Space(1) & lo.Parent.Name & "!" & lo.DataBodyRange.Address
        Next i
        Debug.Print "There is a Table1:", .Exists("Table1")
        Debug.Print "There is a Table3:", .Exists("Table3")
    End With
End Sub

And the result

There are some disadvantages to extending an existing class rather than making your own. For one, you can’t define the default value and a NewEnum property. And you wouldn’t want to because the existing class already has those defined. But that means that you can’t For Each loop through the collection. Another drawback is the lack of Intellisense. If you use the ThisWorkbook auto-instantiated variable, you get all the Intellisense goodness you want. If you use some other workbook reference (like ActiveWorkbook), you still get the function, but not the Intellisense.

As I look this over, I should have named the Exits property TableExists. I also should clear out the collection when I refresh like Doug did.


  1. For me, the big advantage of extending the pre-existing Excel classes is that I can declare an external data source WithEvents and not need to delegate incoming events: the event procedures are written in for me, in the workbook class module, and I can implement the object’s properties and methods with the aid of intellisense…

    …And then, if I’m implementing more than half of the props and methods, write stubs for all of them, redeclare the object using agricultural ‘Implements’, and customise the methods as I see fit.

    This is particularly valuable for workbooks using a single ADODB connection: I suspect that it is less so for Excel projects using more complex objects, or multiple data sources. It offers an easy route for asynchronous data retrieval, if that’s available from the OLDEDB driver you’re using.

    I haven’t attempted this with separate worksheet classes implementing different ADO recordsets, but it seeems an obvious extension – and would work very well as a locked-down database retrieval add-in.

  2. Also… You do know that you can write Public Property GET, LET and SET in plain-vanilla VBA modules?

    I *think* that what I’m doing when I declare Public Property Get and Let procedures in a plain VBA module is declaring properties of the VBA project, rather than properties of its ThisWorkbook object. The difference is subtle, and I’d like to hear from your readers – and you! – if there’s anything useful or interesting in that difference.

    I’ve used module properties for encapsulating objects (or global variables) that need to be initialised or validated before use. There’s an overhead with that: but it’s often the easiest course of action, and occasionally the best one.

  3. Dick Kusleika says:

    Nigel: No I didn’t know that. I tried it an it worked

    Private mobjMyGlobalVariable As Class1

    Public Property Get MyGlobalVariable() As Class1
        If mobjMyGlobalVariable Is Nothing Then
            Set mobjMyGlobalVariable = New Class1
        End If
        Set MyGlobalVariable = mobjMyGlobalVariable
    End Property

    Sub test()
    End Sub

    Then I went searching and, wouldn’t you know, Tushar has a page on it.

  4. Doug Glancy says:

    Dick, that’s an interesting… an interesting … hold on a minute…


    … okay, sorry, had to get that out of my system.

    … an interesting approach of which I was unaware and that I will use at some point. My first use of the Tables class has been in an addin with an application events class that watches for certain workbooks – identified by a named range – and allows the users to create new tables, etc. Since I don’t want any code in the target workbooks, this idea wouldn’t apply. Also, I do like having the Intellisense. Beyond that it’s certainly more efficient than having to instantiate a new class in every routine I want to use it in.

    Very educational comment by Nigel and article by Tusha.

  5. Dick Kusleika says:

    Yeah it’s not right for every case. When I do use it though I code everything as ‘thisworkbook’ so I get the intellisense and then go back and change it. Fraught with danger I’m sure you’ll agree.

  6. Dick Kusleika says:

    Oh, and congrats on the new blog. It’s about time. :)

  7. chrisham says:

    I summon all my courage to ask…. The code runs into an error at ThisWorkbook.RefreshTables. What am I doing wrong here?

  8. Dick Kusleika says:

    chrisham: You have to provide more information or I can’t possibly answer that. Let’s start with what error you’re getting.

  9. Thelma Rentfrow says:

    Once I initially commented I clicked the -Notify me when new comments are added- checkbox and now every time a remark is added I get 4 emails with the identical comment. Is there any approach you can take away me from that service? Thanks!

Posting code or formulas in your comment? Use [cc] tags!

  • [cc_vb]Block of code goes here[/cc_vb]
  • [cci_vb]Inline code goes here[/cci_vb]
  • [cc]Formula goes here[/cc]

Leave a Reply