How to create a VBA class
VBA Class Properties
Option Explicit Private pKey As Long Public Property Get Key() As Long Key = pKey End Property Public Property Let Key(p As Long) pKey = p End Property
VBA Class Instantiation
Option Explicit Sub test() Dim mc As cMyClass Set mc = New cMyClass ' do things Set mc = Nothing End Sub
Since we have already created our class of which mc is now an instance, the IDE will automatically will bring up the available public properties, just like it does with inbuilt Excel classes. In this case, only Key.
'this wil use property let to pass the value 100 mc.Key = 100 MsgBox ("Property get returns " & CStr(mc.Key))
VBA Class methods
Option Explicit Private pKey As Long Private pName As String Private pChildren As Collection Public Property Get Key() As Long Key = pKey End Property Public Property Get Name() As String Name = pName End Property Public Property Get Children() As Collection Set Children = pChildren End Property Public Property Let Key(p As Long) pKey = p End Property Public Function Init(k As Long, sName As String) As cMyClass pKey = k pName = sName Set pChildren = New Collection Set Init = Me End Function
An an example using it:
set mRoot = new cMyClass With mRoot .init 100, "John" end with
set mRoot = new cMyClass With mRoot .init 100, "John" set mc = new cMyClass mc.init 200, "janet" .Children.add mc set mc = new cMyClass mc.init 201, "john" .Children.add mc end with
Since the .init() method actually returns ‘Me’ – the instance of the class, we can short circuit all that by calling init and passing the returned ‘Me’, to the .add() method of the collection. Below is the whole thing.
Sub testChildren() Dim mRoot As cMyClass, mc As cMyClass Set mRoot = New cMyClass With mRoot .Init 100, "john" Set mc = New cMyClass .Children.add mc.Init(200, "janet") Set mc = New cMyClass .Children.add mc.Init(201, "john") MsgBox (.Name & " has " & CStr(.Children.Count) & " children named " & _ .Children(1).Name & " and " & .Children(2).Name) End With End Sub
Of course, the children could have children and so on. This is where classes become so important if you are considering using recursion. See Getting started with recursion for this example taken a little further.