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

Question posted by: ADezii (Expert) on June 9th, 2008 03:50 PM

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

Not the answer you were looking for? Post your question . . .
189,890 Experts ready to help you find a solution.
Sign up for a free account, or Login (if you're already a member).