Can a function return multiple values?

[VBAhole22]

I have a Sub that accepts a pair of x,y coordinates and takes them into an Excel spreadsheet to project them to another coordinate system and then returns a new x and y and another value.
What I would like to do is turn this into a function so that it only occupoes one line of code in my main module but returns the 3 values to me. Is there a way to have a function that returns 3 values with 3 different data types?

The main reason I want to do this is because I don't like to update my form values from the Sub. I would rather do that from the calling code in the main module. Does that make any sense?

 

[raskew]

Take a look at Allen Browne's excellent approach at http://users.bigpond.net.au/abrowne1/ser-16.html

 

[VBAhole22]

Excellent, Thank You.

You know I read tons of books on Access and I see user-defined data types in every one but never had occassion to try to use one. This seems like the perfect opportunity.

 

[VBAhole22]

Now I have total confused myself up. Following the advice in Browne's tip I cam up with the following declaration of my function.

Public Type Coord
intDegreeX_WGS84 As Integer
intDegreeY_WGS84 As Integer
intMinuteX_WGS84 As Integer
intMinuteY_WGS84 As Integer
dblSecondX_WGS84 As Double
dblSecondY_WGS84 As Double

dblUTM1727_Northing As Double
dblUTM1727_Easting As Double
intUTM_Zone As Integer

dblUTM1827_Northing As Double
dblUTM1827_Easting As Double

txtCoordError As String

End Type


And my calling code sends this line to start the process:

ExcelCoordDumpDD dblDDx, dblDDy

The actual code is here and invloves sending some values to Excel where the math is done and then fetching the results.

Function ExcelCoordDumpDD(dblDDx As Double, dblDDy As Double) As Coord
'Input: Decimal Degree in Lat/Lon Datum WGS 84 or Nad 83
'Action: Dumps Lat/Lon to Excel spreadsheet
'Action: Returns UTM coordinates
'Required: Reference to the Excel Object Library
On Error GoTo HandleError

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

' Create an object from the XLS file
Set xlBook = GetObject(Application.CurrentProject.Path & "\Converter.XLS")
Set xlApp = xlBook.Parent
Set xlSheet = xlBook.Worksheets("LatLong->UTM")

'Values to return
'Dim dblUTMEasting As Double
'Dim dblUTMNorthing As Double
'Dim intUTMZone As Integer

'Throw values into Excel
'Latitude
xlSheet.Range("G9").Value = dblDDx
'Longitude
xlSheet.Range("I9").Value = -dblDDy
'Force recalculation
xlApp.CalculateFull

'Pull UTM Results
ExcelCoordDumpDD.intUTM_Zone = xlSheet.Range("C13")
If ExcelCoordDumpDD.intUTM_Zone = 17 Then
ExcelCoordDumpDD.dblUTM1727_Easting = xlSheet.Range("C11")
ExcelCoordDumpDD.dblUTM1727_Northing = xlSheet.Range("C12")
ElseIf ExcelCoordDumpDD.intUTM_Zone = 18 Then
ExcelCoordDumpDD.dblUTM1827_Easting = xlSheet.Range("C11")
ExcelCoordDumpDD.dblUTM1827_Northing = xlSheet.Range("C12")
Else
ExcelCoordDumpDD.txtCoordError = "Not in UTM Zone 17 or 18"
End If


Exit Function
ProcDone:
On Error Resume Next

' Let's clean up our act
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

ExitHere:
Exit Function
HandleError:
Select Case Err.Number
Case Else
MsgBox Err.Description, vbExclamation, _
"Error " & Err.Number
End Select
Resume ProcDone
End Function



So my trouble arises when I return to the calling code and want to update the form with the results, like so:

Me.txtY_UTM1727.Value = ExcelCoordDumpDD().dblUTM1727_Northing
Me.txtX_UTM1727.Value = ExcelCoordDumpDD().dblUTM1727_Easting
Me.txtUTMZone.Value = ExcelCoordDumpDD().intUTM_Zone


When I do it this way Access thinks I want to call the function again. I don't, I just want to reference the values I just set using the function. I thought something like

Me.txtY_UTM1727.Value = Coord.dblUTM1727_Northing

would work but it doesn't. I really got turned around on this one I think. I don't know which way is up. I do know that I don't need to call the function more than once to fill all the values.
Wouldn't another solution to this problem be to fill global variables with the results from a Sub procedure that does the work and then just reference thos globals?

Any help would be greatly appreciated.

 

[jca]

I didn't try it but my guess would be this:

Instead of updating your form fields like this:

Me.txtY_UTM1727.Value = ExcelCoordDumpDD().dblUTM1727_Northing 
Me.txtX_UTM1727.Value = ExcelCoordDumpDD().dblUTM1727_Easting 
Me.txtUTMZone.Value = ExcelCoordDumpDD().intUTM_Zone

Stock the function value in a Coord variable
Like this:

Dim FunctionCoord as Coord

FunctionCoord = ExcelCoordDumpDD(dblDDx, dblDDy)

Then update your form with your variable so you won't call your fonction multiple times:

Me.txtY_UTM1727.Value = FunctionCoord.dblUTM1727_Northing 
Me.txtX_UTM1727.Value = FunctionCoord.dblUTM1727_Easting 
Me.txtUTMZone.Value = FunctionCoord.intUTM_Zone

Hope this help


JC

 

[jca]

Here some more tips when you're calling a function.
I saw in your code that you are calling your function this way:

ExcelCoordDumpDD dblDDx, dblDDy

When you're calling a function without the () surronding the parameters, it tells VB that you don't want to keep the return value of the function.

But if you call your function with the () like this:

ExcelCoordDumpDD(dblDDx, dblDDy)

VB will return an error cause the return value doesn't have anywhere to go so you have to assign it to a variable with the same type as the return value, the type Coord in this case.

So, that's why, if you want to keep the return value, you must call your function like this:

CoordTypeVariable = ExcelCoordDumpDD(dblDDx, dblDDy)

Then you can look at the value your function as return you in the variable without having you call your function more than once.

 

[Cosmos75]

Take a look at Allen Browne's excellent approach at http://users.bigpond.net.au/abrowne1/ser-16.html

Link no longer works?


I am interested in creating a UDF that returns multiple values as well? What are the limitations? Must the multiple values be of the same data type (All Boolean, All Long, All String)?

 

[ChrisO]

G’day Cosmos75

Yes the link seems to be no longer working.

As far as I know, a user defined data type can mix and match any previously defined data type, both inbuilt and other user defined data types.

Regards,
Chris.

 

[Mile-O]

I am interested in creating a UDT that returns multiple values as well? What are the limitations? Must the multiple values be of the same data type (All Boolean, All Long, All String)?

User Defined Types (UDT), as you know are defined at module level in the Declarations section.

Limitations? Memory.
Must the multiple values be of the same data type? No.

According to Vb & VBA in a Nutshell the Type statement has died a little with the advent of Class modules as they can do the same thing with regards storing values as well as allowing their own functions and methods - proper modelled objects.

Here's a quick type:

Public Type udtEmployee
    Forename As String
    Surname As String
    DateOfBirth As Date
End Type

To declare it:

Dim emp As udtEmployee

With emp
    .Forename = "John"
    .Surname = "Smith"
    .DateOfBirth = #01/01/1970#
End With

And then send it off in a function.

strData = BlaBlaBla(emp)

Function BlaBlaBla(ByRef udt As udtEmployee) As String
    [i]function stuff[/i]
End Function

 

[Rich]

Link no longer works?

http://members.iinet.net.au/~allenbrowne/ser-16.html

 

[raskew]

This also works (just tested it 13-Jan-04)

http://allen@allenbrowne.com/

Bob

 

[Cosmos75]

Wonderful!

Thank you all!!

 

[ChrisO]

As we enter the world of user defined data types
we can see many possibilities, not all of which are
immediately apparent.

A user defined data type can ‘inherit’ properties from
other user defined data types.

This is not restricted to VBA, nor VB, nor to C++, nor to C.
It is inherent in all languages that allow user defined data types.

But things had to be cleaned.
There is nothing in ‘C++’ that can’t be done in ‘C’.
‘C++’ came along and wrapped ‘C’ in a structured manner.
It brought ‘C’ up from the ‘high level assembly language’ that it still is, to a more structured approach.

VBA is in the same boat as ‘C’.
It starts out at a much higher level than ‘C’ but when it gets laborious it can get redefined as an object.

The imperative aim with objects it to define that ‘singular’ object in one place.

Scope of reference is not a problem in VBA, so long as the variable referenced is declared as a local in the current procedure.

Option Explicit
Option Compare Text


Public Type udtPoint
    lngX As Long
    lngY As Long
End Type

Public Type udtRectangle
    TopLeft     As udtPoint
    BottomRight As udtPoint
End Type


Sub Test()
    Dim udtMyRectangle As udtRectangle

    udtMyRectangle.TopLeft.lngX = 1
    udtMyRectangle.TopLeft.lngY = 1
    udtMyRectangle.BottomRight.lngX = 10
    udtMyRectangle.BottomRight.lngY = 10
    
    MsgBox AreaOfRectangle(udtMyRectangle)

End Sub


Public Function AreaOfRectangle(ByRef udtX As udtRectangle) As Long
                        
    AreaOfRectangle = (udtX.BottomRight.lngX - udtX.TopLeft.lngX) * _
                      (udtX.BottomRight.lngY - udtX.TopLeft.lngY)
    
End Function

Hope that helps.

Regards,
Chris.

 

[PhillipsAndrew]

ChrisO

Thanks for the excellent example above... could you explain a bit more about the scope please?

Referring to your example, I’d like to put the AreaOfRectangle function in a standard module so that it can be called from anywhere (declared as Public).
I’ve put the Type declaration in a standard module as well (also declared as public).

Then in any Class Modules, say for example a form, I want to create a variable of type Rectangle (private to that module), assign its values and then call AreaOfRectangle..

Trouble is I get “Compile Error: Type MisMatch” errors at the function call. I’ve tried playing with changing public/private etc.. but this brings further errors..

Any ideas? What I’m hoping to do seems fairly straightforward...

P

 

[ChrisO]

G 'day Andrew

Scope refers to the area from which something may be accessed.

Behind the Form...

Option Explicit
Option Compare Text

Private lngVariable1 As Long
Public lngVariable2 As Long


Private Sub cmdTest_Click()
    Dim udtMyRectangle As udtRectangle

    udtMyRectangle.TopLeft.lngX = 1
    udtMyRectangle.TopLeft.lngY = 1
    udtMyRectangle.BottomRight.lngX = 10
    udtMyRectangle.BottomRight.lngY = 10
    
    MsgBox AreaOfRectangle(udtMyRectangle)

End Sub


Public Sub MyTest()


End Sub

In a Standard/Global/Public Module

Option Explicit
Option Compare Text

Public Type udtPoint
    lngX As Long
    lngY As Long
End Type

Public Type udtRectangle
    TopLeft     As udtPoint
    BottomRight As udtPoint
End Type


Public Function AreaOfRectangle(ByRef udtX As udtRectangle) As Long
                        
    AreaOfRectangle = (udtX.BottomRight.lngX - udtX.TopLeft.lngX) * _
                      (udtX.BottomRight.lngY - udtX.TopLeft.lngY)
    
End Function

In the above Private lngVariable1 can only be accessed from within the Forms Class module.
Public lngVariable2 can be accessed from anywhere, so long as the Form is open.

Private Sub cmdTest_Click() can only be accessed from within the Forms Class module.
Public Sub MyTest() can be accessed from anywhere, so long as the Form is open.

Dim udtMyRectangle As udtRectangle declares udtMyRectangle as private and can only be accessed from within the Sub cmdTest_Click()

Public Type udtPoint, Public Type udtRectangle and Public Function AreaOfRectangle can be accessed from anywhere.

Hope that makes some sense.

Regards,
Chris.

 

[PhillipsAndrew]

ChrisO,

Thanks for the clear explanation - it makes sense.. Similar to the extern and structs used in C..

One more question- if you don't mind...

If I change the Type definition to include different data types (eg strings) then in the function declaration what should the 'As' be?
According to the help file it says it should be the return type i.e. udtRectangle but this produces Type Mismatch errors..
So my question is, if using mixed data types within the User Defined type what should the function declaration look like??

For example, (I know the code is meaningless - but it illustrates the point)

Public Type udtRectangle
TopLeft As udtPoint
BottomRight As udtPoint
RectName as string '****
End Type


Public Function AreaOfRectangle(ByRef udtX As udtRectangle) As Long
udtX.RectName = "First Rectangle"
AreaOfRectangle = (udtX.BottomRight.lngX - udtX.TopLeft.lngX) * _
(udtX.BottomRight.lngY - udtX.TopLeft.lngY)

End Function

It works with the As Long, but complains if I put As udtRectangle.
I'm hoping to use more complex User Types so would like to get this right..

Thanks mate...

 

[ChrisO]

G 'day Andrew

You seem to well understand structures in ‘C’.

In VBA it is not that much different. In the following, MyTestFunction() passes back one and one only variable. It passes back a pointer to a user data type. The pointer can be used to reference individual variables within the structure pointed to by that pointer.

Option Explicit
Option Compare Text

Public Type udtUserDataType

    lngX   As Long
    lngY   As Long
    strOne As String
    strTwo As String
    
End Type


Sub Test()
    Dim lngXPosition       As Long
    Dim lngYPosition       As Long
    Dim strFirstString     As String
    Dim strSecondString    As String
    Dim udtNewUserDataType As udtUserDataType

    lngXPosition = 1234
    lngYPosition = 5678
    strFirstString = "ABC"
    strSecondString = "DEF"
    
    udtNewUserDataType = MyTestFunction(lngXPosition, lngYPosition, strFirstString, strSecondString)
    
    MsgBox lngXPosition & ", " & udtNewUserDataType.lngX & vbTab & _
           "  -:-  " & vbTab & _
           lngYPosition & ", " & udtNewUserDataType.lngY & vbNewLine & _
           strFirstString & ", " & udtNewUserDataType.strOne & vbTab & _
           "  -:-  " & vbTab & _
           strSecondString & ", " & udtNewUserDataType.strTwo

End Sub


Public Function MyTestFunction(ByVal lngMyX As Long, _
                               ByVal lngMyY As Long, _
                               ByVal strFirst As String, _
                               ByVal strSecond As String) As udtUserDataType
                        
    lngMyX = lngMyX * 2
    lngMyY = lngMyY * 2
    
    strFirst = strFirst & CStr(lngMyX)
    strSecond = strSecond & CStr(lngMyY)
    
    MyTestFunction.lngX = lngMyX
    MyTestFunction.lngY = lngMyY
    MyTestFunction.strOne = strFirst
    MyTestFunction.strTwo = strSecond
    
End Function

Hope that helps.

Edit:
Note:
All arguments are passed by value, the return values are returned by reference.


Regards,
Chris.