Function to return more than one value

[mario]

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

 

[Dirk Goldgar]

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

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.

 

[Chris]

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

 

[Phil Hunt]

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

Thanks

 

[Graham R Seach]

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

 

[Dirk Goldgar]

Examples:

Public Enum Rectangle
Height as Integer
Width as Integer
End Enum

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

Public Type Rectangle
Height as Integer
Width as Integer
End Type

?

 

[mario]

Thank you very much for the prompt and clear reply.

 

[TC]

Certainly no enum's in A97 VBA.

Do you know if that is true for later versions?

Cheers,
TC

 

[Dirk Goldgar]

Certainly no enum's in A97 VBA.

Do you know if that is true for later versions?

Enums are supported in A2K2 and, I think, A2K. And presumably in A2003,
but I haven't opened that can of worms yet.

 

[TC]

Thanks Dirk.
TC

Enums are supported in A2K2 and, I think, A2K. And presumably in A2003,
but I haven't opened that can of worms yet.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Chris]

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

 

[Chris]

Yeah, I goofed. It's a Type, not Enum

Public Type Rectangle



Chris

 

[Chris]

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



Chris

 

[Stephen Lebans]

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.

 

[Chris]

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!

 

[Tim Ferguson]

Thank you very much for the prompt and clear reply.

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