Creating a User Defined Class / Object

[Andrew Hall NZ]

I am fairly new to VBA and am working on a project where a simple Object
structure could be useful. Most of my previous scripting has been with php
and javascript, both of which blur the distinction between Classes and
Objects so I am pretty fuzzy on it myself.

With javascript you can define a variable as an Object and simply assign
properties to it:

var myObject = New Object ()
myObject.name = "My Name"

This can be very easy to work with and useful. This is what I have come up
with for a slightly more complex VBA equivalent. If there is a better way to
structure it I would appreciate the feedback.

In a class module called DRec I have:

Option Explicit

Public sf As String
Public tf As String
Public oa As Boolean
Public del As Boolean
Public aso As String
Public codes As String
Public t As String
Public tfa As String
Public ttb As String
Public k As String
Public kfa As String
Public ktb As String
Public m As String
Public mfa As String
Public mtb As String

Sub docc()
MsgBox "DRec Test " & m & " : " & t
End Sub

Then in a standard module I have:

Function docArray(sf As String, Optional tf As String = "", _
Optional oa As Boolean = True, Optional del As Boolean =
True, _
Optional aso As String = "", Optional codes As String =
"", _
Optional t As String = "", Optional tfa As String = "",
Optional ttb As String = "", _
Optional k As String = "", Optional kfa As String = "",
Optional ktb As String = "", _
Optional m As String = "", Optional mfa As String = "",
Optional mtb As String = "" _
) As DRec

Dim ar(1 To 15) As Variant
Dim tRec As DRec
Set tRec = New DRec
tRec.tf = tf
If tf = "" Then tf = sf
tRec.sf = sf
tRec.tf = tf
tRec.oa = oa
tRec.del = del
tRec.aso = aso
tRec.codes = codes
tRec.t = t
tRec.tfa = tfa
tRec.ttb = ttb
tRec.k = k
tRec.kfa = kfa
tRec.ktb = ktb
tRec.m = m
tRec.mfa = mfa
tRec.mtb = mtb
Set docArray = tRec
End Function

Sub testDRec()
Dim v As DRec
Set v = docArray(sf:="Daily Bulletin Folder", codes:="ABC", t:="Daily
Bulletin", m:="emailBody")
v.docc
End Sub

Any pointers to better formulation much appreciated

Andrew

 

[Andrew Hall NZ]

As you can see I am using the function docArray to initialize / set default
values for the DRec class. Maybe it would be better to have it as part of the
Class definition. So this is my second version:

DRec Class Module:


Public sf As String
Public tf As String
Public oa As Boolean
Public del As Boolean
Public aso As String
Public codes As String
Public t As String
Public tfa As String
Public ttb As String
Public k As String
Public kfa As String
Public ktb As String
Public m As String
Public mfa As String
Public mtb As String

Sub docc()
MsgBox m & ":" & t
End Sub

Sub init(csf As String, Optional ctf As String = "", _
Optional coa As Boolean = True, Optional cdel As Boolean =
True, _
Optional caso As String = "", Optional ccodes As String =
"", _
Optional ct As String = "", Optional ctfa As String = "",
Optional cttb As String = "", _
Optional ck As String = "", Optional ckfa As String = "",
Optional cktb As String = "", _
Optional cm As String = "", Optional cmfa As String = "",
Optional cmtb As String = "" _
)

tf = ctf
If ctf = "" Then tf = csf
sf = csf
tf = ctf
oa = coa
del = cdel
aso = caso
codes = ccodes
t = t
tfa = ctfa
ttb = cttb
k = ck
kfa = ckfa
ktb = cktb
m = cm
mfa = cmfa
mtb = cmtb
End Sub

------------- Standard Module--------------------------

Sub testDRec()
Dim tRec As DRec
Set tRec = New DRec
tRec.init csf:="ABN Daily Bulletin", ccodes:="ABN", ct:="Daily Bulletin",
cm:="emailBody"
End Sub

 

[NickHK]

Andrew,
I'll leave it to others to discuss the differences between a class and an
object. For me, any distinction is somewhat irrelevant, as they both provide
a means of encapsulating data/concepts.

I tend to use Property Let/Get instead of Public variable, as you are then
able to validate the input as required, or run other code when a property is
changed or supplied.

Private m_sf As String
Property Let sf(argIn As String)
If Left(argIn, 2) = "OK" Then
m_sf = argIn
Else
m_sf = ""
End If
End Property

Property Get sf() As String
Call SomeOtherRoutineToCheckData
sf = m_sf
End Property

You can also omit the Let to make the property read only, something you
cannot do with public variables. Or indeed make them write-only by omitting
the Get routine.

There's the _Initialize routine that run when the class is created, so you
can set any default values and run any initialization code, similar to what
you are currently doing in your docArray routine.
(You do not have to code the initialization of string to "", as that happens
anyway, but you would have to code the boolean=True, as the default is
False.
Private Sub Class_Initialize()
m_sf = "Initialization code has run."
End Sub

Unless "sf", "oa" etc really mean something to you, I'd use more descriptive
names.

And then use your class somewhere suitable in code:
Private Sub CommandButton1_Click()
Dim NewClassTest As cTest

Set NewClassTest = New cTest
'Initailization is run when this object is created.

With NewClassTest
MsgBox .sf
.oa = True
End With

End Sub

Methods are written as a Sub/Function and you can also Raise events.

NickHK

 

[NickHK]

Andrew,
Make a start with all that and post back if you have further questions.

NickHK