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