return values from function

[Guest]

Is there any way to return several values from a function other than
returning a recordset? I have code that runs about 20 lines that I use all
the time in different forms. I would like to make a public function so that
code isn't copied several places, but I need more than one value back.

 

[Allen Browne]

Hi Sam

The simplest way is to pass in a bunch of variables, so you can set them to
a value. Example:

Function Main
Dim var1, var2, var3, var4
Call ManyHappyReturns(var1, var2, var3, var4)
Debug.Print "var1 was returned as " & var1
Debug.Print "var2 was returned as " & var2
'and so on.
End Function

Function ManyHappyReturns(var1, var2, var3, var4)
var1 = 1
var2 = 10
var3 = 100
var4 = 1000
End Function

The alternative is to create a Type which is in effect a variable with many
components. You can then create a function that returns the Type, and so the
return value has many components (kind of like returning a whole record.)
There is an example of how to do that in this article:
Multiple return values from a Function
at:
http://allenbrowne.com/ser-16.html

 

[Lance McGonigal]

Another way is to use a bit switch method and delimit each place with a "|".

Function MyExample()

MyExample = 1stValue & "|" & 2ndValue & "|" & 3rdValue & "|"

exit function

?myexample()
1stValue|2ndValue|3rdValue

You can then Split() and ref the array.

good luck

 

[John Nurick]

Or return a Variant(Array):

Public Function XXX() As Variant
Dim A As Long
Dim B As Long
Dim S As String

A = 1
B = 2
S = "Hat"

XXX = Array(A, B, S)
End Function

More overhead than a custom Type, but AFAIK it works in more situations.

 

[Tim Ferguson]

I would like to make a public function so that
code isn't copied several places, but I need more than one value back.

or return a user defined type:

public type TFeatures
height as integer
width as integer
smell as string
end type



public function Describe(something as variant) _
As TFeatures

dim featTemp as New TFeatures

with featTemp
.height = 57
.width = sqrt(something)
.smell = "awful"
end with
Set describe = featTemp

end function



or even better make it a Class and hide all the code...

Or you can just use Reference variables

public sub Describe( _
byref Height as integer, _
byref Width as integer, _
byref Smell as String)

Height = 37 ' etc etc
end sub


Hope that helps


Tim F

 

[Bill Johnson]

Allen,

This 'Type' process works really good. I noticed that every time I want a
value and reference it like:

nuRate = ScoreIt(stYr1, nuBBFY).Rate

and so on, it will perform the function. Is it necessary to go out and run
the function for every variable that is part of the Type?

Thanks!