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.

9 thoughts on “Extending Built-in Classes

  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. 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. 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. 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. I summon all my courage to ask…. The code runs into an error at ThisWorkbook.RefreshTables. What am I doing wrong here?

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

  8. 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? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.