Part 3 - Using recordset and VBA class

This is part 3 of the article series - 8 ways to return multiple values from a VBA function. In this article, we are going to look at how to return multiple values by using database recordset and VBA class.

Here are the other two parts of this article series.

Part 1 - Return multiple values by By Reference argument, collection, and dictionary object.
Part 2 - Return multiple values by array, CSV, and user-defined type.

Code used in this article is based on a simple Access form as shown below. Each button click event triggers a function that returns multiple values and displays in Immediate Window.

A simple form with 8 buttons for testing use.

7. Return multiple values by using database recordset.

Microsoft ADO recordset is the ADO Recordset object that holds a set of records from a database table. A Recordset object consists of rows and columns (like an Excel range), but the Recordset object can also hold only a single record with only one row and one column. For more information, please refer to Microsoft MSDN resources about ADO recordset here Recordset Object (ADO).

Below is a SQL query that select FirstName and LastName from Employees table in Northwind database. When you need to return multiple values from a function, you may consider to store them in a database table if these data can be grouped or related together in one table or multiple tables. Please read this article series about database design.

' This function returns a recordset which can hold multiple values.
Public Function GetEmployees() As ADODB.Recordset
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    Set GetEmployees = CurrentProject.Connection.Execute("select FirstName, LastName from Employees")
End Function

To test, we call the function from the click event cmdGetRecordset_Click() on Form1 for button [7. Get Recordset]. In this subroutine, function GetEmployees() is called and returns the recordset. Then we display the values in Immediate Window.

' Retrieve first name and last name of all records from Employees table of Northwind database.
Private Sub cmdGetRecordset_Click()
    Dim strFirstName As String
    Dim strLastName As String

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    ' Get the recordset object.
    Set rst = GetEmployees()
    
    ' Use loop to iterate through the recordset row by row to read its content.
    Do While Not rst.EOF
        strFirstName = rst!FirstName
        strLastName = rst!LastName

        ' Display in Immediate window.
        Debug.Print strFirstName & " " & strLastName
        
        rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
End Sub

Immediate Window shows all employee names.

8. Return multiple values by using class members.

According to MSDN, a class is a fundamental building block of object-oriented programming (OOP). In essence, a class defines a new data type which is used to create a class object. This class object can then be used to return multiple values of the class member.

One of the core functionalities of classes is encapsulation, which encapsulates data and methods within the class. When data is contained in a class, they can be retrieved by using a class method, which can be used to return multiple values from a class.

Note that data and method within a class are hidden from the outside world and are accessible only through Public interface of the class, which are the public properties, methods and events. In simple words, the outside world can only deal with the things that are exposed by the class for the outside world to see.

Sometimes, if you store multiple objects of a class in a collection object, it gives you more power to process multiple values. But if things get too complex, you may consider using database to organize the data so that they are stored and retrieved in a structured way.

Here is the class module - clsEmployee.

VBA code for the class module.

Option Explicit

Private p_strName As String
Private p_blnIsManager As Boolean
Private p_datHireDate As Date
Private p_ProbationaryPeriodLength As Integer

' Class constructer - Initialise variables when the class is instantiated.
Private Sub Class_Initialize()
    p_ProbationaryPeriodLength = 3  ' months
End Sub

Public Property Get Name() As String
    Name = p_strName
End Property

Public Property Let Name(Value As String)
    p_strName = Value
End Property

Public Property Get IsManager() As Boolean
    IsManager = p_blnIsManager
End Property

Public Property Let IsManager(Value As Boolean)
    p_blnIsManager = Value
End Property

Public Property Get HireDate() As Date
    HireDate = p_datHireDate
End Property

Public Property Let HireDate(Value As Date)
    p_datHireDate = Value
End Property

Public Function PassedProbationaryPeriod(ByVal dteHireDate As Date) As Boolean
   
    '' Compare with current date for difference in months.
    If DateDiff("m", dteHireDate, Date) > p_ProbationaryPeriodLength Then
        PassedProbationaryPeriod = True
    Else

        PassedProbationaryPeriod = False
    End If
    
End Function

So far, the class has been defined in class module clsEmployee. Next, we coded the button click event subroutine cmdGetClass_Click() on Form1 for button [8. Get Class object]. In this subroutine, we create an object from the class. Then some values were assigned to the class properties and called the PassedProbationaryPeriod method. Finally, we print results in Immediate Window.

Private Sub cmdGetClass_Click()
    Dim blnPassedProbationaryPeriod As Boolean
    Dim objEmp As clsEmployee
    
    ' Create an employee object from the class.
    Set objEmp = New clsEmployee

    ' Assign values to the object attributes.
    objEmp.Name = "John Star"
    objEmp.IsManager = True
    objEmp.HireDate = #6/30/2010#

    blnPassedProbationaryPeriod = objEmp.PassedProbationaryPeriod(objEmp.HireDate)

    Debug.Print "Passed Probationary Period? " & blnPassedProbationaryPeriod

    Debug.Print "Name: " & objEmp.Name
    Debug.Print "Is Manager? " & objEmp.IsManager
    Debug.Print "Hire Date: " & objEmp.HireDate
End Sub

Immediate Window shows four values.

Here comes the end of this article series.

Happy Coding!


Copyright© GeeksEngine.com