multiple return values from a function

[CPerdue]

Hi all,

I'm trying to return multiple values from a function.

I just got this working... is there a better way?

Type FunctionReturn
    results(0, 1) As Variant
End Type
 
Public Sub callingFunction()
   Dim ThisResult As FunctionReturn
   Dim A, B
 
   Argument_1 = 7
   Argument_2 = 5
 
   ThisResult = MyFunction(Argument_1, Argument_2)
 
   A = ThisResult.results(0, 0)
   B = ThisResult.results(0, 1)
 
   Debug.Print A
   Debug.Print B
End Sub
 
 
Function MyFunction(ByVal X, ByVal Y) As FunctionReturn
   A = X + Y
   B = X - Y
 
   MyFunction.results(0, 0) = A
   MyFunction.results(0, 1) = B
End Function

 

[RoryA]

It depends what you want really. You could also simply return an array from the function rather than a UDT.

 

[CPerdue]

Thanks.

 

[RoryA]

You can also pass arguments ByRef to the function and it can change them as required.

 

[CPerdue]

Is passing ByRef a better practice? I did note that keeping the meaning of each return element straight could be a bother. The UDT was an attempt to address that, but leads to 'more moving parts'. I guess the trade is a more complex function call for less overhead in unpacking the result.

Any other best practices?

Thanks again,

C.

 

[RoryA]

I'm not sure there is one best practice. Some will recommend that all functions should return a simple success or failure value as the result and manipulate arguments passed ByRef to return actual data; others will say that the return value should be the data, whether it be one value, an array or a UDT/object type. I don't think it really matters as long as you try to be consistent with whichever you choose.
Obviously for Excel UDFs, you have to use the second option.