467,892 Members | 1,875 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,892 developers. It's quick & easy.

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

ADezii
Expert 8TB
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
  • viewed: 3186
Share:
2 Replies
Denburt
Expert 1GB
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 8TB
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.

Similar topics

66 posts views Thread by Darren Dale | last post: by
2 posts views Thread by androtech | last post: by
7 posts views Thread by Shuffs | last post: by
5 posts views Thread by D. Shane Fowlkes | last post: by
16 posts views Thread by Nikolay Petrov | last post: by
reply views Thread by Lee Harr | last post: by
6 posts views Thread by aarklon | last post: by
8 posts views Thread by aleksandar.ristovski | last post: by
10 posts views Thread by Jim | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.