Custom Search

How to return multiple values from a VBA function (Part 3)

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

    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!


Other Recent Articles from the MS Access category:

1.Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)
2.How to easily get a list of field names in MS Access
3.How to count distinct records in MS Access
4.How to do transaction based processing in MS Access
5.How to open a document (local/network file or web page) from MS Access
6.How to use ADOX to create unique composite index - the VBA approach
7.How to do cross-table update queries in MS Access - the right way
8.Three efficient ways to get the number of records by using VBA
9.How to create a composite unique index (not as a primary key) in MS Access
10.Use VBA to get the correct number of records in a Recordset object
11.Disable Access Prompt when a record is changed, table deleted, or action queries run
12.How to hide and unhide a MS Access object
13.How to return multiple values from a VBA function (Part 2)
14.How to return multiple values from a VBA function (Part 1)
15.Three ways to programmatically duplicate a table in MS Access by VBA
16.Create a DLL by CSharp or VB.Net for VBA
17.How to correctly reference and call a DLL
18.How to register a C# or VB.Net DLL
19.Email address validation by Regular Expressions using VBA
20.Fix MS Access error: Query must have at least one destination field
21.How to unselect radio buttons in MS Access after it has been selected
22.How to Change Query Timeout Value for MS Access SQL Queries
23.What is Northwind Traders database

Copyright © 2017 All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

Home | Feedback | Terms of Use | Privacy Policy