By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,444 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

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

ADezii
Expert 5K+
P: 8,607
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
Share this Question
Share on Google+
2 Replies


Denburt
Expert 100+
P: 1,356
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
Jun 12 '08 #2

ADezii
Expert 5K+
P: 8,607
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

Post your reply

Sign in to post your reply or Sign up for a free account.