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.
   
   Expand|Select|Wrap|Line Numbers

   1. Public Type MyPC
   2.   PC_Type As String
   3.   MHz As Integer
   4.   Hard_Drive_Capacity As String
   5.   RAM As String
   6.   On_Board_Video As Boolean
   7.   USB_Ports As Byte
   8.   Date_Purchased As Date
   9. 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.
   
   Expand|Select|Wrap|Line Numbers

   1. Public Function fReturnPCInfo() As MyPC     'Function returns Structure
   2. 'This is an example of how to return multiple values from a Function,
   3. 'by setting the Return Value of the Function equal to a User Defined
   4. 'Type, then setting the values of its elements
   5.   fReturnPCInfo.PC_Type = "Pentium 1000"
   6.   fReturnPCInfo.MHz = 750
   7.   fReturnPCInfo.Hard_Drive_Capacity = "80 Gb"
   8.   fReturnPCInfo.RAM = "500 Mb"
   9.   fReturnPCInfo.On_Board_Video = True
   10.   fReturnPCInfo.USB_Ports = 5
   11.   fReturnPCInfo.Date_Purchased = #5/15/2008#
   12. End Function
3. Now, we can retrieve individual elements of the Structure by calling the single Function but with different qualifiers, as in:
   
   Expand|Select|Wrap|Line Numbers

   1. Debug.Print "********************************************"
   2. Debug.Print "PC Type: " & fReturnPCInfo().PC_Type
   3. Debug.Print "PC MegaHertz: " & fReturnPCInfo().MHz
   4. Debug.Print "Hard Drive Capacity: " & fReturnPCInfo().Hard_Drive_Capacity
   5. Debug.Print "RAM: " & fReturnPCInfo().RAM
   6. Debug.Print "On Board Video: " & IIf(fReturnPCInfo().On_Board_Video, "Yes", "No")
   7. Debug.Print "USB Ports: " & fReturnPCInfo().USB_Ports
   8. Debug.Print "Date of Purchase: " & fReturnPCInfo().Date_Purchased
   9. Debug.Print "********************************************"
4. OUTPUT
   
   Expand|Select|Wrap|Line Numbers

   1. ********************************************
   2. PC Type: Pentium 1000
   3. PC MegaHertz: 750
   4. Hard Drive Capacity: 80 Gb
   5. RAM: 500 Mb
   6. On Board Video: Yes
   7. USB Ports: 5
   8. Date of Purchase: 5/15/2008
   9. ********************************************

Jun 9 '08 #1

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...

Jun 12 '08 #2

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...

Expand|Select|Wrap|Line Numbers

1. Public Function fReturnPCInfo() As MyPC     'Function returns Structure
2. 'This is an example of how to return multiple values from a Function,
3. 'by setting the Return Value of the Function equal to a User Defined
4. 'Type, then setting the values of its elements
5.   fReturnPCInfo.PC_Type = "Pentium 1000"
6. 'For example...
7.   fReturnPCInfo.MHz = GetCPUSpeed
8.   fReturnPCInfo.Hard_Drive_Capacity = "80 Gb"
9.   fReturnPCInfo.RAM = "500 Mb"
10.  fReturnPCInfo.On_Board_Video = True
11.   fReturnPCInfo.USB_Ports = 5
12.   fReturnPCInfo.Date_Purchased = #5/15/2008#
13. End Function
14.  
15.  Public Function GetCPUSpeed()
16. Dim objWMIService As Object
17. Dim objItem As Object
18. Dim colItems As Object
19. On Error Resume Next
20. Set objWMIService = GetObject("winmgmts:\\" & "." & "\root\cimv2")
21. Set colItems = objWMIService.ExecQuery("Select * from Win32_Processor", , 48)
22. For Each objItem In colItems
23.       GetCPUSpeed = "MaxClockSpeed: " & objItem.MaxClockSpeed & " CurrentClockSpeed: " & objItem.CurrentClockSpeed
24. Next
25. End Function

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

Jun 13 '08 #3