Bytes Tip of the Week #53 - How to Return Multiple Values From a Function

[ADezii]

The incentive for this Tip was an Article by the amazing Allen Browne - I considered it noteworthy enough to post as The Tip of the Week in this Access Forum.
Original Article by Allen Browne

Traditionally, one has always thought that Functions can only return a single value, and for the most part that was true. Ever since Access 95, we gained the new functionality, through VBA, to have Functions return an entire Structure of values. A User Defined Type can easily be created to handle the Structure returned by the Function. In this specific case, a picture is worth a thousand words, so I'll simply post the modified code, adequately comment it, and leave the rest up to you. Should you have any questions, please feel free to ask them.

1. Declare a User Defined Type that can handle the Structure returned by the Function. Notice the use of the 'Public' in the type declaration, it gives the User Defined Type/Structure Global Scope, meaning its elements can be accessed from anywhere within the Application. Also, notice the diversified Data Types within the Structure.
   

   Public Type MyPC
     PC_Type As String
     MHz As Integer
     Hard_Drive_Capacity As String
     RAM As String
     On_Board_Video As Boolean
     USB_Ports As Byte
     Date_Purchased As Date
   End Type

2. The return value of the Function is set to the Global User Defined Type/Structure. Each line within the Function assigns values to individual elements of the Structure, thus giving it the capability to return multiple values.
   

   Public Function fReturnPCInfo() As MyPC     'Function returns Structure
   'This is an example of how to return multiple values from a Function,
   'by setting the Return Value of the Function equal to a User Defined
   'Type, then setting the values of its elements
     fReturnPCInfo.PC_Type = "Pentium 1000"
     fReturnPCInfo.MHz = 750
     fReturnPCInfo.Hard_Drive_Capacity = "80 Gb"
     fReturnPCInfo.RAM = "500 Mb"
     fReturnPCInfo.On_Board_Video = True
     fReturnPCInfo.USB_Ports = 5
     fReturnPCInfo.Date_Purchased = #5/15/2008#
   End Function

3. Now, we can retrieve individual elements of the Structure by calling the single Function but with different qualifiers, as in:
   

   Debug.Print "********************************************"
   Debug.Print "PC Type: " & fReturnPCInfo().PC_Type
   Debug.Print "PC MegaHertz: " & fReturnPCInfo().MHz
   Debug.Print "Hard Drive Capacity: " & fReturnPCInfo().Hard_Drive_Capacity
   Debug.Print "RAM: " & fReturnPCInfo().RAM
   Debug.Print "On Board Video: " & IIf(fReturnPCInfo().On_Board_Video, "Yes", "No")
   Debug.Print "USB Ports: " & fReturnPCInfo().USB_Ports
   Debug.Print "Date of Purchase: " & fReturnPCInfo().Date_Purchased
   Debug.Print "********************************************"

4. OUTPUT
   

   ********************************************
   PC Type: Pentium 1000
   PC MegaHertz: 750
   Hard Drive Capacity: 80 Gb
   RAM: 500 Mb
   On Board Video: Yes
   USB Ports: 5
   Date of Purchase: 5/15/2008
   ********************************************

[Denburt]

Very nice and an interesting post, if I may expand on this a bit instead of locking yourself into particular constants this can be very dynamic, take no 2 line 6 (for 1 of many examples) CPU speed can be requested and returned via a function and make this more flexible and dynamic. Therefore Debug.Print "PC MegaHertz: " & fReturnPCInfo().MHz would return any particular users P.C. information: function added GetCPUSPeed...

Public Function fReturnPCInfo() As MyPC     'Function returns Structure
'This is an example of how to return multiple values from a Function,
'by setting the Return Value of the Function equal to a User Defined
'Type, then setting the values of its elements
  fReturnPCInfo.PC_Type = "Pentium 1000"
'For example...
   fReturnPCInfo.MHz = GetCPUSpeed
  fReturnPCInfo.Hard_Drive_Capacity = "80 Gb"
  fReturnPCInfo.RAM = "500 Mb"
 fReturnPCInfo.On_Board_Video = True
  fReturnPCInfo.USB_Ports = 5
  fReturnPCInfo.Date_Purchased = #5/15/2008#
End Function
 
 Public Function GetCPUSpeed()
Dim objWMIService As Object
Dim objItem As Object
Dim colItems As Object
On Error Resume Next
Set objWMIService = GetObject("winmgmts:\\" & "." & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_Processor", , 48)
For Each objItem In colItems
      GetCPUSpeed = "MaxClockSpeed: " & objItem.MaxClockSpeed & " CurrentClockSpeed: " & objItem.CurrentClockSpeed
Next
End Function

[ADezii]

Very nice and an interesting post, if I may expand on this a bit instead of locking yourself into particular constants this can be very dynamic, take no 2 line 6 (for 1 of many examples) CPU speed can be requested and returned via a function and make this more flexible and dynamic. Therefore Debug.Print "PC MegaHertz: " & fReturnPCInfo().MHz would return any particular users P.C. information: function added GetCPUSPeed...

Public Function fReturnPCInfo() As MyPC     'Function returns Structure
'This is an example of how to return multiple values from a Function,
'by setting the Return Value of the Function equal to a User Defined
'Type, then setting the values of its elements
  fReturnPCInfo.PC_Type = "Pentium 1000"
'For example...
   fReturnPCInfo.MHz = GetCPUSpeed
  fReturnPCInfo.Hard_Drive_Capacity = "80 Gb"
  fReturnPCInfo.RAM = "500 Mb"
 fReturnPCInfo.On_Board_Video = True
  fReturnPCInfo.USB_Ports = 5
  fReturnPCInfo.Date_Purchased = #5/15/2008#
End Function
 
 Public Function GetCPUSpeed()
Dim objWMIService As Object
Dim objItem As Object
Dim colItems As Object
On Error Resume Next
Set objWMIService = GetObject("winmgmts:\\" & "." & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_Processor", , 48)
For Each objItem In colItems
      GetCPUSpeed = "MaxClockSpeed: " & objItem.MaxClockSpeed & " CurrentClockSpeed: " & objItem.CurrentClockSpeed
Next
End Function

Very interesting point, Denburt, and a great indication of just how flexible this logic can be. Thanks.