Category Archives: 13050

If One Is Not Enough, …



It is generally accepted that it is a good programming practice to not use monolithic code, but to break functional units of code into separate subs or functions and call these from a control procedure. Functions are very useful for passing values back to the calling procedure, but functions return a single value so what if you want to get more than one value returned to you calling procedure?


I have often seen this topic raised in the Excel forums, a statement that a function cannot return multiple values, or a question on how to return multiple values. There are a number of ways to return multiple values, here I will show 3 such ways, with 2 variations on the last one.


The ways that I will cover are:


  • Classes
  • ByRefArguments
  • UDTs
    • Returning a UDT
    • UDT as an Argument



Some of the codeI includes basic error handling to illustrate the point made earlier.


 


Classes


The first example here is using classes. Classes have properties that can be read, write, or read-write, they can have as many as you need, and they have methods which are essentially functions within the class. So it should be clear that by using a class, our control procedure can call a procedure that returns multiple values; any of the properties or functions could set any number of other properties; a call to a class can then read those properties to get multiple return values. An example class and calling procedure is shown in Figure 1.


'---Control procedure
Public Sub MultiplesClass()
Dim cls As clsPerson

    On Error GoTo errhnd
    Set cls = New clsPerson
    cls.Name = "Jack"
    cls.DoB = DateSerial(1976, 12, 3)
    MsgBox cls.Name & " aged " & cls.Age & " years"

teardown:
    Set cls = Nothing
    Exit Sub
    
errhnd:
    MsgBox Err.Number & "," & Err.Description
    Resume teardown
End Sub

'---Class code
Private mAge As Long
Private mDoB As Date

Public Name As String

Public Property Let DoB(Value As Date)
    mDoB = Value
    mAge = Year(Date) - Year(mDoB)
    If DateSerial(Year(Date), Month(mDoB), Day(mDoB)) >= Date Then
    
        mAge = mAge - 1
    End If
End Property

Public Property Get Age() As Long
    Age = mAge
End Property

Figure 1 – Using a Class to Return Multiple Values


Byref Arguments


Perhaps the easiest way to return multiple values is to use arguments of the function to return amended or changed values. This method can use the return value to pass the function success status with the arguments returning the business variables. The one thing to note is that arguments that will be used to return values must be passed ByRef.


 


Figure 2 shows an example of returning multiple values using function arguments, also demonstrating the error handling.


 


Public Function MultiplesArgsCaller()
Dim myName As String
Dim myAge As Long
Dim isBlokeWoman As String

    On Error GoTo errhandler:
    
    myName = "John"
    If Not MultiplesArgsCalled(myName, isBlokeWoman, myAge) Then
    
        Err.Raise 19999
    End If
    
    MsgBox myName & " is a " & " is a " & myAge & " year old " & isBlokeWoman
    
    myName = "Jane"
    If Not MultiplesArgsCalled(myName, isBlokeWoman, myAge) Then
    
        Err.Raise 19999
    End If
    
    MsgBox myName & " is a " & " is a " & myAge & " year old " & isBlokeWoman
    
    myName = "Damien"
    If Not MultiplesArgsCalled(myName, isBlokeWoman, myAge) Then
    
        Err.Raise 19999
    End If
    
    MsgBox myName & " is a " & " is a " & myAge & " year old " & isBlokeWoman

teardown:
    Exit Function

errhandler:
    If Err.Number = 19999 Then Err.Description = myName & "is an unknown  name"
    MsgBox Err.Number & ", " & Err.Description
    Resume teardown
End Function

Private Function MultiplesArgsCalled( _
    ByVal Name As String, _
    ByRef GenderType As String, _
    ByRef Age As Long) As Boolean

    MultiplesArgsCalled = True
    Select Case Name
        Case "John"
            GenderType = "bloke"
            Age = 45
        Case "Jane"
            GenderType = "woman"
            Age = 36
        Case Else
            MultiplesArgsCalled = False
    End Select
End Function

Figure 2 – Using a Function Arguments to Return Multiple Values


Returning a UDT


The third and fourth methods use user defined types (UDTs).


UDTs can be seen as mini, very basic, classes. They are a set of data elements within an encompassing object, any instance of a UDT will inherit all of those data elements. The elements within a type can be strongly type with the uual data types, or default to Variant. Interestingly, an elament within a UDT can be defined as a previously defined UDT, types within types, but that is another topic.


As with function arguments, the elements within a UDT can be used as read, write or read-write data elements, the values that are passed to the function can be separated from the values returned, or a value could be passed to the function, amended ther, and the changed value returned in the same UDT element.


The first of these two methods uses a UDT for the function type. Figure 3 shows an example of returning multiple values using a UDT as a function type.


Public Type Person
    Gender As String
    Age As Long
    Success As Boolean
End Type

Public Function MultiplesUDTCaller()
Dim myName As String
Dim thisPerson As Person
Dim isBlokeWoman As String

    On Error GoTo errhandler:
    
    myName = "John"
    thisPerson = MultiplesUDTCalled(myName)
    If Not thisPerson.Success Then
    
        Err.Raise 19999
    End If
    
    MsgBox myName & " is a " & " is a " & thisPerson.Age & " year old " & thisPerson.Gender
    
    myName = "Jane"
    thisPerson = MultiplesUDTCalled(myName)
    If Not thisPerson.Success Then
    
        Err.Raise 19999
    End If
    
    MsgBox myName & " is a " & " is a " & thisPerson.Age & " year old " & thisPerson.Gender

    myName = "Damien"
    thisPerson = MultiplesUDTCalled(myName)
    If Not thisPerson.Success Then
    
        Err.Raise 19999
    End If
    
    MsgBox myName & " is a " & " is a " & thisPerson.Age & " year old " & thisPerson.Gender

teardown:
    Exit Function

errhandler:
    If Err.Number = 19999 Then Err.Description = myName & "is an unknown  name"
    MsgBox Err.Number & ", " & Err.Description
    Resume teardown
End Function

Private Function MultiplesUDTCalled( _
    ByVal Name As String) As Person

    MultiplesUDTCalled.Success = True
    Select Case Name
        Case "John"
            MultiplesUDTCalled.Gender = "bloke"
            MultiplesUDTCalled.Age = 45
        Case "Jane"
            MultiplesUDTCalled.Gender = "woman"
            MultiplesUDTCalled.Age = 36
        Case Else
            MultiplesUDTCalled.Success = False
    End Select
End Function

Figure 3 – Using a UDT as a Function Type to Return Multiple Values


Passing UDT as an Argument


The fourth and final method uses a UDT as a function argument. The same considerations apply as when using a UDT as a function type. Figure 4 shows an example of returning multiple values using a UDT as a function argument.


 


Public Type Person
    Name As String
    Gender As String
    Age As Long
    Success As Boolean
End Type

Public Function MultiplesUDTCaller()
Dim myName As String
Dim thisPerson As Person
Dim isBlokeWoman As String

    On Error GoTo errhandler:
    
    thisPerson.Name = "John"
    If Not MultiplesUDTCalled(thisPerson) Then
    
        Err.Raise 19999
    End If
    
    MsgBox thisPerson.Name & " is a " & " is a " & thisPerson.Age & " year old " & thisPerson.Gender
    
    thisPerson.Name = "Jane"
    If Not MultiplesUDTCalled(thisPerson) Then
    
        Err.Raise 19999
    End If
    
    MsgBox thisPerson.Name & " is a " & " is a " & thisPerson.Age & " year old " & thisPerson.Gender

    thisPerson.Name = "Damien"
    If Not MultiplesUDTCalled(thisPerson) Then
    
        Err.Raise 19999
    End If
    
    MsgBox thisPerson.Name & " is a " & " is a " & thisPerson.Age & " year old " & thisPerson.Gender

teardown:
    Exit Function

errhandler:
    If Err.Number = 19999 Then Err.Description = thisPerson.Name & "is an unknown  name"
    MsgBox Err.Number & ", " & Err.Description
    Resume teardown
End Function

Private Function MultiplesUDTCalled( _
    ByRef Them As Person) As Boolean

    MultiplesUDTCalled = True
    Select Case Them.Name
        Case "John"
            Them.Gender = "bloke"
            Them.Age = 45
        Case "Jane"
            Them.Gender = "woman"
            Them.Age = 36
        Case Else
            MultiplesUDTCalled = False
    End Select
End Function

Figure 4 – Using a UDT as a Function Argument to Return Multiple Values


Summary


so there we have it, 4 ways to get multiple values from a procedure. ALl work, so it is a matter of preference or suitability as o which to use. Personally, I don’t use UDTs, my preference is for multiple arguments within a function. Of curse I use classes, but not for this being able to return multiple values, but in situations where a class suits my application design.


As an aside, I don’t use subs, I always use functions. All procedures should always return at least one value, a success or failure value to pass back to the error handler. Using the function return value to pass back a succes indicator does mean that where you do need that function to return some value determined within the function, just think of any type of calculator, the function return value has been used up, so you will necessarily need to pass back multiple values.

What’s My Type?


I am a big advocate of using the Enum construct in VBA, I feel that it is a simple way of adding a set of values, and provides a grouping, abstracting advantage that is not given by simple variables. Plus they can add to intellisense. As such, I should also be a fan of another specialised construct, the Type statement, but it is quite the opposite, I naturally avoid it. This is not because I think the Type statement is a bad concept, in fact I think the basic idea is great, but more because of its implementation; whenever I try to use it in anger, it breaks on me. As such, I tend to avoid it and create classes.


Earlier this week, it occurred to me that there was a case in which I could implement a Type, an elegant solution, with no chance of it breaking on me.


We all know that when your masterpiece sets application variables, such as calculation mode, in its processing, they should be reset at the end to whatever state the user originally had them. The normal way to do this is to save them in a bunch of variables at the start of the process, and restore them at the end.


My solution is very similar, but the benefits of Type add an elegance that I don’t feel the variables give it.


Here is that solution.


 


Public Type ApplicationValues


    ScreenUpdating As Boolean


    Calculation As XlCalculation


End Type


 


Private AppValues As ApplicationValues


 


Public Function ObfuscateData()


 


    With Application


   


        AppValues.ScreenUpdating = .ScreenUpdating


        AppValues.Calculation = .Calculation


       


        .ScreenUpdating = False


        .Calculation = xlCalculationManual


    End With


   


    On Error GoTo func_error


 


… main code


 


func_exit:


    With Application


       


        .ScreenUpdating = AppValues.ScreenUpdating


        .Calculation = AppValues.Calculation


    End With


 


func_error:


    ‘your error handler


    Resume func_exit


End Function


 


I think this is a neat little approach. Furthermore, it is simple and extendible, what more can we ask?


This may be obvious to you, it may be the sort of thing that you do all of the time, but it made me smile after I had implemented it. So much so that it is now a code snippet in my MZ-Tools, implementable at the drop (click) of a button.