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. - 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
- 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
- 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 "********************************************"
- 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
-
********************************************
2 3581
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 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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Darren Dale |
last post by:
Hello,
def test(data):
i = ? This is the line I have trouble with
if i==1: return data
else: return data
a,b,c,d = test()
|
by: androtech |
last post by:
Hello,
I'm looking for a function that returns a date range for a specified week
number of the year.
I'm not able to find functions like this anywhere. Any pointers/help would
be much...
|
by: Shuffs |
last post by:
Could someone, anyone please tell me what I need to amend, to get this
function to take Sunday as the first day of the week?
I amended the Weekday parts to vbSunday (in my code, not the code...
|
by: D. Shane Fowlkes |
last post by:
This may be a very basic question but it's something I've never done before.
I've looked at a couple of my favorite sites and books and can't find an
answer either. I can write a Function to...
|
by: Nikolay Petrov |
last post by:
How can I return multiple values from a custom function?
TIA
|
by: Lee Harr |
last post by:
I wrote a function to return the first date of a given week
(and a few related functions) :
-- return the first date in the given week
CREATE or REPLACE FUNCTION week_start(integer, integer)...
|
by: aarklon |
last post by:
Hi folks,
I found an algorithm for calculating the day of the week here:-
http://www.faqs.org/faqs/calendars/faq/part1/index.html
in the section titled 2.5 what day of the week was 2 august...
|
by: aleksandar.ristovski |
last post by:
Hello all,
I have been thinking about a possible extension to C/C++ syntax.
The current syntax allows declaring a function that returns a value:
int foo();
however, if I were to return...
|
by: Jim |
last post by:
I'm sure this has been asked before but I can't find any postings.
I have a table that has weekly inspections for multiple buildings. What
I need to do is break these down by the week of the...
|
by: erikbower65 |
last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps:
1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal.
2. Connect to...
|
by: kcodez |
last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: Taofi |
last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same
This are my field names
ID, Budgeted, Actual, Status and Differences
...
|
by: DJRhino1175 |
last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this -
If...
|
by: Rina0 |
last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
|
by: DJRhino |
last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer)
If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _
310030356 Or 310030359 Or 310030362 Or...
|
by: Mushico |
last post by:
How to calculate date of retirement from date of birth
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
| |