Function to return more than one value

  • Thread starter Thread starter mario
  • Start date Start date
M

mario

How to make a function (VBA) return more than one values.
Would appreciate if you can suggest an example
 
mario said:
How to make a function (VBA) return more than one values.
Would appreciate if you can suggest an example
Click to expand...

A function cannot have more than one return value. The closest you can
come is returning a collection, array, or user-defined type, that may
thus contain multiple elements, or a delimited string (like
"one,two,three") from which multiple elements may be parsed. Another
approach is to pass arguments to the function by reference, and have the
function modify its arguments.
 
Examples:

Public Enum Rectangle
Height as Integer
Width as Integer
End Enum


Function CreateRectangle(arguments) as Rectangle
code....
End Function

Call it like:

Dim r as Rectangle
r= CreateRectangle
Debug.Print r.Height
Debug.Print r.Width



Other way:

Function CreateRectangle(ByRef intHeight as Integer, ByRef
intWidth as Integer)
IntHeight=20
intWidth=30
End Function


Call it like

dim Height as Integer
dim Width as Integer
CreateRectangle(height,Width)
Debug.Print Height
Debug.Print Width




All this is air code.


Chris
 
Chris, I like the first one. But is it true that Enum cannot contain string
and stuff like that ?

Thanks
 
Mario,

As Dirk said, you can return an array from a function...
Public Function x() As Variant
Dim y(2) As String

y(0) = "a"
y(1) = "b"
y(2) = "c"

x = y
End Function

....or a collection...
Public Function x() As Collection
Dim y As New Collection

y.Add 111, CStr(111)
y.Add 222, CStr(222)
y.Add 333, CStr(333)

Set x = y
End Function

....or a UDT...
Public Type myType
a As String
b As String
c As Integer
End Type

Public Function x() As myType
Dim y As myType

y.a = "111"
y.b = "222"
y.c = 333

x = y
End Function

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Chris said:
Examples:

Public Enum Rectangle
Height as Integer
Width as Integer
End Enum
Click to expand...

Chris, that doesn't look right to me. Shouldn't that example be

Public Type Rectangle
Height as Integer
Width as Integer
End Type

?
 
Certainly no enum's in A97 VBA.

Do you know if that is true for later versions?

Cheers,
TC
 
TC said:
Certainly no enum's in A97 VBA.

Do you know if that is true for later versions?
Click to expand...

Enums are supported in A2K2 and, I think, A2K. And presumably in A2003,
but I haven't opened that can of worms yet.
 
See, that's why I have the disclaimer that it's Air Code.

It would be a Type, not Enum.... Enums are for possible
values to pass to a function.


And no, A97 Does not support Types (or Enums), so you have
to use the second example.


Chris
 
I wouldn't think they would have dropped them, but I
haven't dove too deep yet. Still looking for a reason.



Chris
 
Chris you can create Private/Public UD Types in A97.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
 
Yes to types, no to Enums?

It's been so long since I used A97, I honestly didn't
remember. Guess I should just preface everthing with
AFAIK!
 
Thank you very much for the prompt and clear reply.
Click to expand...

The one that nobody has mentioned is to use a class module to wrap the
whole lot up much more neatly. You can add as many methods and members as
you like, and then forget how they work.


Dim r As New CMyRectangleClass

r.GetSizeFromRecordset

MsgBox "It's " & r.Width & " by " & r.Height & ", which = " & _
r.Area & " square miles."

r.DrawOnScreen

Set r = Nothing


Just a thought....

Tim F
 
Back
Top