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
Initialize
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
.RefreshTables
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.
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.
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.
Nigel: No I didn’t know that. I tried it an it worked
Private mobjMyGlobalVariable As Class1Public Property Get MyGlobalVariable() As Class1
If mobjMyGlobalVariable Is Nothing Then
Set mobjMyGlobalVariable = New Class1
End If
Set MyGlobalVariable = mobjMyGlobalVariable
End Property
Sub test()
MyGlobalVariable.DoStuff
End Sub
Then I went searching and, wouldn't you know, Tushar has a page on it.
http://www.tushar-mehta.com/publish_train/xl_vba_cases/vba-property_procs_in_std_module.htm
Dick, that’s an interesting… an interesting … hold on a minute…
I GOT PINGBACKED BY DDOE! WOOHOO! YEEEHAW!
… 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.
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.
Oh, and congrats on the new blog. It’s about time. :)
I summon all my courage to ask…. The code runs into an error at ThisWorkbook.RefreshTables. What am I doing wrong here?
chrisham: You have to provide more information or I can’t possibly answer that. Let’s start with what error you’re getting.
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!