return values from function

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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
 
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.
 
I would like to make a public function so that
code isn't copied several places, but I need more than one value back.
Click to expand...

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
 
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!
 
Back
Top