Is this the right way to implement your own VBA Control?

[Jim Luedke]

If you want to improve a Control's functionality, how do you do it?

The code below is based on my hunch of how VBA works. Amazingly, it
works. But is it legit?

My UserForm has a TreeView. In Initialize(), I equivalence it to a New
instance of my "ImprovedTreeView" class.

Is that the best, most VBA-native way to do it? Even eliminating one
step would improve things.

My biggest confusion is: In a class module, how do you descend from
another class?

I want CImprovedTreeView to have all the behavior of TreeView, plus my
stuff.

I get around it now by implementing a property of CImprovedTreeView of
the type (TreeView) I want to descend from. But that sure is kluge-a-
delic:

***

‘CLASS MODULE CImprovedTreeView

Option Explicit

Private g_Tree As TreeView

'Sample property...which works fine!

Public Property Get FirstNode() As Node
'Action: Return the topmost node of the tree.
'Note: I was going crazy, because I thought, doesn't .Nodes(1) give
you that? Nein, Dummkopf!

Dim N As Node

'Take any node, ascend to its root, then take the root's first
sibling.
With g_Tree
If .Nodes.Count > 0 Then
Set N = .Nodes(1)
While Not (N.Parent Is Nothing)
Set N = N.Parent
Wend
Set FirstNode = N.FirstSibling
End If
End With
End Property

Public Property Set Tree(TV As TreeView)

Set g_Tree = TV
End Property


‘MODULE UserForm1
‘TreeView1 is on this form.

Option Explicit

Private ImprovedTreeView as CImprovedTreeView

Private Sub UserForm_Initialize()
‘Note: Why do I have to get involved with naming two classes (TreeView
and CTreeView)? Can I dispense with one?

Set ImprovedTreeView = New CImprovedTreeView
Set ImprovedTreeView.Tree = TreeView1
End Sub

Private Sub DoSomething
Dim N as Node

Set N = ImprovedTreeView.FirstNode
‘etc.
End Sub

***

Thanks.

***

 

[Simon Murphy]

Jim
This is the containment, perfectly valid and the approach I tend to use.

You could use interface inheritance (Implements keyword)(this is how you
descend from another class in VBA) but then you have to define each
method and explicitly call the parents implementation or code your own.
I have never found it as useful as the full inheritance provided by
other languages.

Cheers
Simon
Excel development website: www.codematic.net

 

[Chip Pearson]

The code below is based on my hunch of how VBA works. Amazingly, it

works. But is it legit?

Your code is perfectly fine. I've written very similar code over the
years to provide extended functionality for many of MSFORMS controls.

My biggest confusion is: In a class module, how do you descend from
another class?

Sadly, you can't. VBA doesn't support inheritance. A Class an
implement an Interface, which provides the look and feel of the
implemented interface to external callers and allows you to override
(but not overload) members of the implemented interface. Implements
won't help you with the TreeView, however, because TreeView doesn't
expose an interface that you can Implement into a class in VBA.

‘Note: Why do I have to get involved with naming two classes (TreeView
and CTreeView)? Can I dispense with one?

Set ImprovedTreeView = New CImprovedTreeView
Set ImprovedTreeView.Tree = TreeView1

You don't have two classes here. You have one class,
CImprovedTreeView, and one instance variable, ImprovedTreeView. A
class is like a blueprint for a house. It describes every aspect of
the house, but isn't itself a house. You can't live in it. The
instance variable is the house. The same blueprint can be used to
create many (identical) independent houses, but one house has only one
blueprint, no more,no less. One class can be used to create any number
of instances, but each instance has one and only one class definition.

Unfortunately, the Initialize constructor of a class in VBA does not
accept parameters and cannot be overloaded to do so. E.g, the
following code won't work in VBA, as it does in other languages:

Set ImprovedTreeView = New CImprovedTreeView(TreeView1) ' no good

You can extend the functionality of the TreeView by declaring the
g_Tree instance variable using WithEvents. This will allow your
CImprovedTreeView class to receive event triggers and, possibly,
provide extended features:

Private WithEvents g_Tree As MSComctlLib.TreeView

To get the events for g_Tree, click the left dropdown at the top of
the code window and choose g_Tree. The supported events will be listed
in the right dropdown.

If you really need object oriented features such as inheritance and
overloading, you should be using C# or VBNET, not VBA.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Jim Luedke]

Simon, Chip:

Thanks very much for replies.

Of course, not being able to subclass is a kinda serious, non-OOP
limitation.

But for a script language, ya can't complain too much. VBA is still
awfully rich.

Chip, I refer to your site quite a bit.

For everyone else, you should check out Chip's massive VBA reference
material at:

cpearson.com

Thanks again.

***