We help IT Professionals succeed at work.

Function to return more than one Value?

eeevbeee
eeevbeee asked
on
Is there any way for a Function to Return more than one value, say I want it to return a True, and a number?
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2012

Commented:
By definition a function returns by name. So you may be better off using a Sub and passing the appropriate paramters.  However, you can pass parameters to a function ByRef (default) and they will be returned.  This is not encouraged, but ...

If you need an example, shout.
Anthony
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Functions by their very nature can only return a single value.  To return more than one value use a SUB as

Public Sub DoSomething (ByRef value1 as integer, ByRef Value2 as Boolean)

   value1 = 25
   Value2 =True

end sub

The BYREF allows you to change the value in the subroutine and have that value available in the calling routine.  If you don't want the value to change then use BYVAL.

In the calling program

sub main
Dim Num as integer
Dim Ok as Boolean

  DoSomething Num , Ok

  Msgbox Num
  Msgbox OK

end sub

You will see 25 and then True in the messageboxes.

Hope this helps

mlmcc
CERTIFIED EXPERT
Top Expert 2012

Commented:
Here is an example first using a Sub and second using a Function.

Private Sub Command1_Click()
Dim lngValue As Long
Dim strHex As String
Dim strOctal As String

' Initialize
lngValue = 12345678
strOctal = vbNullString
strHex = vbNullString

Sub1 lngValue, strOctal, strHex
Debug.Print strOctal, strHex

' Initialize
strOctal = vbNullString
strHex = vbNullString
Debug.Print strOctal, Function1(lngValue, strOctal)

End Sub

Sub Sub1(ByVal lngValue As Long, ByRef strOctal As String, ByRef strHex As String)

strOctal = Oct(lngValue)
strHex = Hex(lngValue)

End Sub

Function Function1(ByVal lngValue As Long, ByRef strOctal As String)

strOctal = Oct(lngValue)
Function1 = Hex(lngValue)

End Function

As you can see, you can use a Function to return "more than one value", although strictly speaking you are using the function to return by name and all arguments that are passed ByRef are also returned.  This is one of the side-effects of using Functions with arguments passed ByRef.  Hence it is not desirable, although it can be done.

Anthony
CERTIFIED EXPERT
Top Expert 2012

Commented:
Here is another example more in line with your question:

Private Sub Command1_Click()
Dim dblValue As Long
Dim dblSqr As Double

' Initialize
dblValue = 12345678
dblSqr = 0
If SquareRootOK(dblValue, dblSqr) Then
   Debug.Print dblSqr
Else
   MsgBox "Invalid"
End If

dblValue = -12345678
dblSqr = 0
If SquareRootOK(dblValue, dblSqr) Then
   Debug.Print dblSqr
Else
   MsgBox "Invalid"
End If

End Sub

Function SquareRootOK(ByVal dblValue As Double, ByRef dblSqr As Double) As Boolean

If dblValue >= 0 Then
   dblSqr = SQR(dblValue)
   SquareRootOK = True
Else
   dblSqr = 0
   SquareRootOK = False
End If

End Function

Again, not a very good idea, but if you insist ...

Anthony
Suat OzgurWeb / Application Developer
CERTIFIED EXPERT

Commented:
Why dont u use user defined type to declare your function?

'All in  module

Public Type MyType
 ret_1 as Boolean
 ret_2 as Integer
End Type

Public Function MyFunc(par_1 as Boolean, par_2 as integer) As MyType
 'You will put your code to set return value here
 MyFunc.ret_1=par_1
 MyFunc.ret_2=par_2
End Function

'In a form command click
Private Sub Command1_Click()
 msgbox MyFunc(True,1).ret_1, MyFunc(True,1).ret_2
End Sub

You have two returns.

Suat
CERTIFIED EXPERT

Commented:
Hi,

You can even return many data(well, an array) and not by pass arguments:

Private Sub Command1_Click()
Dim intArray() As Integer, i As Integer
intArray = ReturnArray(6)
For i = 1 To UBound(intArray)
Print intArray(i)
Next i
End Sub

Private Function ReturnArray(n As Integer) As Integer()
Dim myArray() As Integer, i As Integer
ReDim myArray(n)
For i = 1 To UBound(myArray)
myArray(i) = i
Next
ReturnArray = myArray
End Function

Commented:
You can pass Parameters, or Arguments, one-way [in] or two-way [in, out]

argument ByVal [in]
argument ByRef [in, out]
retval [out]

Ideally, you should use the correct way of passing the parameters.  Since you have two values you want [out] of your function, use one retval and one ByRef argument.

Use excellent acperkins' example:

Function SquareRootOK(ByVal dblValue As Double, ByRef dblSqr As Double) As Boolean


' or this is from ADO documentation (returns both Data and False if call failed)
Public Function GetRowsOK(rstTemp As ADODB.Recordset, _
   ByVal intNumber As Integer, avarData As Variant) As Boolean

   ' Return False only if fewer than the desired
   ' number of rows were returned, ...

End Function

Commented:
Sounds like the user-defined type suggestion is the best... A good one smozgur...
Valliappan ANSenior Tech Consultant
CERTIFIED EXPERT

Commented:
yep, agree with smozgur's one. You need to use UDT.

Type UDTRet
Value1 as Integer
Value2 as Double
End Type

'Note: You can even pass objects, not restricting to variables.

Function MyFunc() As UDTRet
    MyFunc.Value1 = v1
    MyFunc.Value2 = v2
End Function

'Usage:
Dim MyUDT1 As UDTRet

MyUDT1 = MyFunc
Msgbox MyUDT1.Value1
Msgbox MyUDT1.Value2

'Also note that, you dont need to call the function twice to get the 2 values, but just get it into another UDT and access the values from this UDT variable.

Hope this helps.
Cheers.

Commented:
Define UDT to pass one extra parameter?  This is funny!
Valliappan ANSenior Tech Consultant
CERTIFIED EXPERT

Commented:
I think it is more readable to use a UDT, than using a variable by reference, which might be difficult at times, to understand. This is my opinion.
CERTIFIED EXPERT
Top Expert 2012

Commented:
The only catch to the UDT approach as submitted by smozgur, is that in fact this line

msgbox MyFunc(True,1).ret_1, MyFunc(True,1).ret_2

is calling the function twice.  This may or may not be desirable.

All told, all these methods, including mine are hacks and in general should be avoided as they will be a maintainance headache.

And in any case I believe eeevbeee is MIA, so it is probably a moot point.

Anthony
Suat OzgurWeb / Application Developer
CERTIFIED EXPERT

Commented:
You are right Anthony, about calling function twice (also about point:)) Actually i didnot test the code i gave yet but i was in doubt about declaring another UDT to set function return value like valli_an did above. I think it is good way to avoid twice calling. But i still couldnot try it either (unfortunately but i will soon). I am not sure that we can set the UDT type function value into a same UDT type variable.

suat
Suat OzgurWeb / Application Developer
CERTIFIED EXPERT

Commented:
Yes, i tried it and it works as valli_an said above. And it just works one time (no twice) to set the user type variable in receiver procedure.

"'All in a module" is important because you must call this function from a module and make it (also user type) as Public.

Thanks valli_an.
suat
Suat OzgurWeb / Application Developer
CERTIFIED EXPERT

Commented:
sorry,,

"you must put this function in a module ..."

suat
Valliappan ANSenior Tech Consultant
CERTIFIED EXPERT

Commented:
Thanks suat.

Yes, if you want it to be available to other forms/ modules, then need to put it in a module, and declare the UDT as public like:

Public Type UDTRet
  Value1 as Integer
  Value2 as Double
End Type

and put the function which returns the UDT in the module.
Hope it is clear.

Cheers.
Suat OzgurWeb / Application Developer
CERTIFIED EXPERT

Commented:
Hi valli_an,

However it will generate an error if you use a function in a form module which declared as UDT. Is not it?

suat
CERTIFIED EXPERT

Commented:
Just for fun:

If the number need to be returned always > 0, can you return only the number:
If the numger < 0 => -number and faulse
If the number > 0 => number and true
Valliappan ANSenior Tech Consultant
CERTIFIED EXPERT

Commented:
Hi suat,

Hope there is no problem, you can use a function returning UDT in a form.

If you use only in a form, then no need for public for the Type, can declare it as Private is enough.

Hope I understood right.

Senior Tech Consultant
CERTIFIED EXPERT
Commented:
jklmn, why not return a string, with the number followed by a spl.char like chr(13) and then a Boolean in the string. Then split it later. Just for fun again.:)
Suat OzgurWeb / Application Developer
CERTIFIED EXPERT

Commented:
Yes, you are still right valli_an. just Private must be used to declare type and also function.

I ve never used this (2 returns from one function) but i think it would be really nice way which avoids using lots of public variable (to avoid need to remember names).

Thanks for improving it valli_an.

eeevbeee, couldnot you find the best solution within these (mean all ideas) nice comments still? :)

regards
suat

Valliappan ANSenior Tech Consultant
CERTIFIED EXPERT

Commented:
Thanks smozgur and eeevbeee (btw, any naming relevance to your id used in EE).

Cheers :)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.