How to use “Object” in VBA

1. The class ancestor– “User Defined Type” in VBA
A Type is made up of other basic variable types which similar with structure in vb.net. The type needs to create in a module otherwise your code won’t be able to reflect it in typing intelligence. The following is and example which I used in one project (the reason i used type because recordcount doesn’t work somehow, it should work after setting recordset.CursorType =adOpenStatic or rs.CursorLocation = adUseClient, but somehow it works only when you using conn.Execute(strSQL) but not run stored procedure by adocommand ):

Public Type EmployeeInfo
    lastname As String
    office As String
    flag As String
End Type

This defines a single type named EmployeeInfo which has three elements. You can then create variables of the Employee type and give values to the elements. For example,

    Dim membersFor() As EmployeeInfo
    Dim conn As ADODB.Connection
    Dim strConn As String
    Dim strSQL As String
    Dim rs As ADODB.Recordset
    Dim totalcount As Integer

    strConn = "Provider=SQLOLEDB.1; Data Source=server\DBInstance;Initial Catalog=db; Integrated Security=SSPI;"
    Set conn = New ADODB.Connection
    conn.Open strConn

    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandText = "SpGetLastNameByID"
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Refresh
    cmd.Parameters(1).Value = CInt(id)  ' id is an globe variable
    cmd.Parameters(2).Value = 1
    Set rs = cmd.Execute()

    totalcount = 0
    Do While Not rs.EOF
        ReDim Preserve membersFor(totalcount) As EmployeeInfo
        membersFor(totalcount).lastname = RemoveQCInLastName(rs("lastName"))
        membersFor(totalcount).office = RemoveQCInLastName(rs("con"))
        membersFor(totalcount).flag = RemoveQCInLastName(rs("flag"))
        rs.MoveNext
        totalcount = totalcount + 1
    Loop

    Set cmd = Nothing
    conn.Close

Then you can pass the membersFor array as the parameter to the function or procedure, it is simple then using class if you just want to use a light way of object.
However types have a few shortcomings. First, you can’t declare new instances of a Type. You must declare all the variables you’ll need at design time or you need a dynamic array that is resized with Redim Preserve. The second is that you have no control over what values are assigned to the elements of a Type. For example, there is nothing to prevent the assignment of a negative value to the Salary element. Finally, a Type can’t “do” anything, no method allowed, it is simply a static data structure.

2. Class in VBA

First, insert a class module into the VBA project (from the Insert menu in the VBA editor). Name the class as CEmployee (it is common practice to use a ‘C’ as the first letter of a class). There are three properties to create: LastName, Office, and flag. These values will be stored in private variables within the class. Since they are declared Private, they cannot be accessed outside the class module.

Private pLastName As String
Private pOffice As String
Private pFlag As String

Next, declare Property procedures to allow these variables to be read from and written to. It is done with Property Get and Property Set functions (or Property Set for object type variables).

Public Property Get LastName() As String
LastName = pLastName
End Property
Public Property Set LastName(Value As String)
pLastName = Value
End Property

Public Property Get Office() As String
Office = pOffice
End Property
Public Property Set Office(Value As String)
pOffice = Value
End Property

Public Property Get Flag() As Boolean
Flag = pFlag
End Property
Public Property Set Flag(Value As Double)
pSalary = Value
End Property

Because Property procedures can contain any code you like, for example if there is a salary property the set Salary procedure can be written to exclude non-positive values.

Public Property Set Salary(Value As Double)
If Value > 0 Then
pSalary = Value
Else
' appropriate error code here
End If
End Property

A property can be made read-only simply by omitting the Set procedure also can be a calculated one as well.

Property Get WithholdingTax() As Double
WithholdingTax = calculated value
End Property

Finally, the class can contain methods, such as getLastNameByID procedure.

Public Sub getLastNameByID()
' actual code
End Sub

Then it can be used like the following way.

Dim Emp As CEmployee
Set Emp = New CEmployee
Emp.LastName = "TestLastName1"
Emp.Office = "Calgary"
Emp.Flag = true
Emp.Salary = 40000
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s