Getting more than one result from a function.?

[Gerry Abbott]

Hi All,
If completed a script which parses a string into fragments (fields), then
assigns these substrings into an array.

I wish to turn this into a function to which i can pass the string. However
i don't know how to get the contents of the array out?
I am used to passing parameters to a function, and getting a single result
back. Is there a way to get multiple results from a function?
Is it something to do with passing byVal or byRef?
Thanks in advance,

Gerry Abbott

[rkc]

Gerry Abbott wrote:

Hi All,
If completed a script which parses a string into fragments (fields), then
assigns these substrings into an array.

I wish to turn this into a function to which i can pass the string. However
i don't know how to get the contents of the array out?
I am used to passing parameters to a function, and getting a single result
back. Is there a way to get multiple results from a function?
Is it something to do with passing byVal or byRef?

You can do it by passing in a string array or a variant byref
and having the method put values in the array.

Sub getByRef(ByRef s() As String)
Dim a(5) As String
a(0) = "yellow"
a(1) = "orange"
a(2) = "blue"
a(3) = "lemon"
a(4) = "tangerine"
a(5) = "pink"

s = a
End Sub

Sub testGetByRef()
Dim s() As String
Dim i As Integer
Call getByRef(s)

For i = 0 To UBound(s)
Debug.Print s(i)
Next
End Sub

You can also just return an array as the result of the function.

Function getStringArray() As String()
Dim a(5) As String
a(0) = "yellow"
a(1) = "orange"
a(2) = "blue"
a(3) = "lemon"
a(4) = "tangerine"
a(5) = "pink"

getStringArray = a
End Function

Sub testGetStringArray()
Dim s() As String
Dim i As Integer
s = getStringArray

For i = 0 To UBound(s)
Debug.Print s(i)
Next
End Sub

[Justin Hoffman]

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:VB***************@news.indigo.ie...

Hi All,
If completed a script which parses a string into fragments (fields), then
assigns these substrings into an array.

I wish to turn this into a function to which i can pass the string.
However i don't know how to get the contents of the array out?
I am used to passing parameters to a function, and getting a single result
back. Is there a way to get multiple results from a function?
Is it something to do with passing byVal or byRef?
Thanks in advance,

Gerry Abbott

There was a recent discussion on ByVal/ByRef concerning how function
arguments are normally passed ByRef (unless you specify ByVal). This means
that the function can change those actual values and you could use this as a
way to get multiple values from a function - that is, you would get a single
result to say whether all the values had been loaded into the parameters you
passed:

Public Function GetMyValues(strFirstName As String, strLastName As String,
dteDOB As Date) As Boolean

' Do complicated processing to work out values
strFirstName="Steve"
strLastName="Smith"
dteDOB=DateSerial(1965,01,01)
' Return True to say we have set the values
GetMyValues=True

End Function

But this is not a method I have seen used very often. You could get you
function to return a string like:
"FirstName=Steve~LastName=Smith~DOB=1965-01-01" and split it up later. Or
you could get the function to return an array.

Or how about a user-defined data type, eg

Type Person
FirstName As String
LastName As String
DOB As Date
End Type

Public Function LoadPerson(psn As Person) As Boolean

psn.FirstName = "Steve"
psn.LastName = "Smith"
psn.DOB = DateSerial(1965, 1, 1)

LoadPerson = True

End Function

Public Sub TestPerson()

Dim psn As Person

If LoadPerson(psn) Then
MsgBox "hello I'm " & psn.FirstName & " " & psn.LastName
End If

End Sub

[Gerry Abbott]

Thanks guys for the sharp response,
I had worked out the array passing, and my function is working properly this
way.

The solution you suggest using the user defined type looks like the most
elequent one for apps where the array is not appripriate. I'll give this one
a try for future reference. Need to work out which is quicker, array or
type. Also like how you suggest to set the function as a bolean type,
setting its value at the end of the function, then calling the function with
an expression. Neat.
Gerry

"Justin Hoffman" <[e-mail]> wrote in message
news:da**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:VB***************@news.indigo.ie...

Hi All,
If completed a script which parses a string into fragments (fields), then
assigns these substrings into an array.

I wish to turn this into a function to which i can pass the string.
However i don't know how to get the contents of the array out?
I am used to passing parameters to a function, and getting a single
result back. Is there a way to get multiple results from a function?
Is it something to do with passing byVal or byRef?
Thanks in advance,

Gerry Abbott

There was a recent discussion on ByVal/ByRef concerning how function
arguments are normally passed ByRef (unless you specify ByVal). This
means that the function can change those actual values and you could use
this as a way to get multiple values from a function - that is, you would
get a single result to say whether all the values had been loaded into the
parameters you passed:

Public Function GetMyValues(strFirstName As String, strLastName As String,
dteDOB As Date) As Boolean

' Do complicated processing to work out values
strFirstName="Steve"
strLastName="Smith"
dteDOB=DateSerial(1965,01,01)
' Return True to say we have set the values
GetMyValues=True

End Function

But this is not a method I have seen used very often. You could get you
function to return a string like:
"FirstName=Steve~LastName=Smith~DOB=1965-01-01" and split it up later. Or
you could get the function to return an array.

Or how about a user-defined data type, eg

Type Person
FirstName As String
LastName As String
DOB As Date
End Type

Public Function LoadPerson(psn As Person) As Boolean

psn.FirstName = "Steve"
psn.LastName = "Smith"
psn.DOB = DateSerial(1965, 1, 1)

LoadPerson = True

End Function

Public Sub TestPerson()

Dim psn As Person

If LoadPerson(psn) Then
MsgBox "hello I'm " & psn.FirstName & " " & psn.LastName
End If

End Sub

[Chuck Grimsby]

On Thu, 07 Jul 2005 10:59:21 GMT, rkc
<rk*@rochester.yabba.dabba.do.rr.bomb> wrote:

Gerry Abbott wrote:

Hi All,
If completed a script which parses a string into fragments (fields), then
assigns these substrings into an array.

I wish to turn this into a function to which i can pass the string. However
i don't know how to get the contents of the array out?
I am used to passing parameters to a function, and getting a single result
back. Is there a way to get multiple results from a function?
Is it something to do with passing byVal or byRef?

You can do it by passing in a string array or a variant byref
and having the method put values in the array.

Sub getByRef(ByRef s() As String)
Dim a(5) As String
a(0) = "yellow"
a(1) = "orange"
a(2) = "blue"
a(3) = "lemon"
a(4) = "tangerine"
a(5) = "pink"

s = a
End Sub

Sub testGetByRef()
Dim s() As String
Dim i As Integer
Call getByRef(s)

For i = 0 To UBound(s)
Debug.Print s(i)
Next
End Sub

You can also just return an array as the result of the function.

Function getStringArray() As String()
Dim a(5) As String
a(0) = "yellow"
a(1) = "orange"
a(2) = "blue"
a(3) = "lemon"
a(4) = "tangerine"
a(5) = "pink"

getStringArray = a
End Function

Sub testGetStringArray()
Dim s() As String
Dim i As Integer
s = getStringArray

For i = 0 To UBound(s)
Debug.Print s(i)
Next
End Sub

You can also pass a bunch of variables to a function as a ParamArray:

(This example was actually written by Joe Foster, of "Nuke Me Zemu"
legend)

Function RowAvg(ParamArray Stuff() As Variant) As Variant
Dim c As Integer: c = 0
Dim s As Variant
Dim i As Integer
For i = LBound(Stuff) To UBound(Stuff)
If IsMissing(Stuff(i)) Then
' skip it
ElseIf IsNull(Stuff(i)) Then
' skip it too
Else
s = s + Stuff(i)
c = c + 1
End If
Next
If c > 0 Then RowAvg = s / c Else RowAvg = Null
End Function
--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

[rkc]

Chuck Grimsby wrote:

You can also pass a bunch of variables to a function as a ParamArray:

(This example was actually written by Joe Foster, of "Nuke Me Zemu"
legend)

Function RowAvg(ParamArray Stuff() As Variant) As Variant
Dim c As Integer: c = 0
Dim s As Variant
Dim i As Integer
For i = LBound(Stuff) To UBound(Stuff)
If IsMissing(Stuff(i)) Then
' skip it
ElseIf IsNull(Stuff(i)) Then
' skip it too
Else
s = s + Stuff(i)
c = c + 1
End If
Next
If c > 0 Then RowAvg = s / c Else RowAvg = Null
End Function

That's nice, but using an array of a defined type would
clear up what the function actually expected to be passed
in. It's tough to guarantee a result when your only argument
will except any number of any data type, including objects.

[Chuck Grimsby]

On Fri, 08 Jul 2005 02:45:12 GMT, rkc
<rk*@rochester.yabba.dabba.do.rr.bomb> wrote:

Chuck Grimsby wrote:

You can also pass a bunch of variables to a function as a ParamArray:
(This example was actually written by Joe Foster, of "Nuke Me Zemu"
legend)
Function RowAvg(ParamArray Stuff() As Variant) As Variant

That's nice, but using an array of a defined type would
clear up what the function actually expected to be passed
in. It's tough to guarantee a result when your only argument
will except any number of any data type, including objects.

Personally, I quite agree! Indeed, I tend to write code in exactly
that fashion myself, but I also think it's important to know _all_ the
options, not just those I happen to agree with.
--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

[rkc]

Chuck Grimsby wrote:

On Fri, 08 Jul 2005 02:45:12 GMT, rkc
<rk*@rochester.yabba.dabba.do.rr.bomb> wrote:

Chuck Grimsby wrote:

You can also pass a bunch of variables to a function as a ParamArray:
(This example was actually written by Joe Foster, of "Nuke Me Zemu"
legend)
Function RowAvg(ParamArray Stuff() As Variant) As Variant

That's nice, but using an array of a defined type would
clear up what the function actually expected to be passed
in. It's tough to guarantee a result when your only argument
will except any number of any data type, including objects.

Personally, I quite agree! Indeed, I tend to write code in exactly
that fashion myself, but I also think it's important to know _all_ the
options, not just those I happen to agree with.

You're right. So let's add that a paramarray is always optional, must
always be the last argument in the list and is always passed ByVal.
Always being passed ByVal means it can't be used as a way to
return values to the calling code.

[DenBorg]

rkc wrote:

You're right. So let's add that a paramarray is always optional, must
always be the last argument in the list and is always passed ByVal.
Always being passed ByVal means it can't be used as a way to
return values to the calling code.

Correct, except for the part stating that ParamArray arguments are
always passed ByVal and that it can't be used to return values to the
calling code.

[Thelma Lubkin]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote:
: Chuck Grimsby wrote:
:> On Fri, 08 Jul 2005 02:45:12 GMT, rkc
:> <rk*@rochester.yabba.dabba.do.rr.bomb> wrote:
:>
:>
:>>Chuck Grimsby wrote:
:>>
:>>>You can also pass a bunch of variables to a function as a ParamArray:
:>>>(This example was actually written by Joe Foster, of "Nuke Me Zemu"
:>>>legend)
:>>>Function RowAvg(ParamArray Stuff() As Variant) As Variant
: You're right. So let's add that a paramarray is always optional, must
: always be the last argument in the list and is always passed ByVal.
: Always being passed ByVal means it can't be used as a way to
: return values to the calling code.

Does the ParamArray type have any useful function other than
reminding the coder what [s]he's doing? I would write such
code using an ordinary array and restricting it for the
functionality that I needed. Maybe that's because I've never
written code for a large project where it's assumed that
someone else will need to expand and maintain it.
--thelma

[lylefair]

Huh?

[lylefair]

I suppose that we could say that we can pass arrays as elements of the
ParamArray and that those array elements will be passed ByRef and can
be changed (used in a way to return values to the calling code).
But this is a stretch. OTTOMH I don't see any particular use for this,
unless perhaps we might want to process a series of arrays.

Sub temp1(ParamArray a())
a(0)(2) = StrConv(a(0)(2), vbUpperCase)
End Sub

Sub temp2()
Dim b(4)
b(2) = "the"
temp1 b
Debug.Print b(2)
'Displays "THE"
End Sub

I haven't checked this out but I'm guessing that if we send an object,
say a recordset, as an element of a ParamArray then it would be passed
ByRef and that we could modify it directly in the Called Proecdure. Is
there any use for this? I can't think of one.

[DenBorg]

RTK incorrectly stated that the modified values of elements passed into
a ParamArray is not and cannot be returned to the calling routine.

WHY do you think that the original values are left unchanged? No need
to resort to passing Arrays of Arrays just to get the original values
to be changed.

Try out the following code, call Demo and see what happens to the
values of S1, S2, and S3.

Add the following to a Standard Code Module (i.e. not a class module),
and in the immediate window, type "Demo" and press Return:
Public Sub ParmArraySub(ParamArray X() As Variant)
Dim I As Long

For I = 0 To UBound(X)
If I <> 1 Then
X(I) = UCase$(X(I)) 'Convert to Upper Case
Else
X(I) = "Modified!!!"
End If
Next 'I
End Sub

Public Sub Demo()
Dim S1 As String
Dim S2 As String
Dim S3 As String

S1 = "abc"
S2 = "jkl"
S3 = "xyz"
ParmArraySub S1, S2, S3
MsgBox S1
MsgBox S2
MsgBox S3
End Sub

[lylefair]

It seems you are right. Thank you for the clear demonstration.

[rkc]

DenBorg wrote:

RTK incorrectly stated that the modified values of elements passed into
a ParamArray is not and cannot be returned to the calling routine.

I stand corrected.

[Chuck Grimsby]

On 8 Jul 2005 12:31:54 GMT, Thelma Lubkin <th****@alpha2.csd.uwm.edu>
wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote:
: Chuck Grimsby wrote:
:> On Fri, 08 Jul 2005 02:45:12 GMT, rkc
:> <rk*@rochester.yabba.dabba.do.rr.bomb> wrote:
:>
:>
:>>Chuck Grimsby wrote:
:>>
:>>>You can also pass a bunch of variables to a function as a ParamArray:
:>>>(This example was actually written by Joe Foster, of "Nuke Me Zemu"
:>>>legend) :>>>Function RowAvg(ParamArray Stuff() As Variant) As Variant
: You're right. So let's add that a paramarray is always optional, must
: always be the last argument in the list and is always passed ByVal.
: Always being passed ByVal means it can't be used as a way to
: return values to the calling code. Does the ParamArray type have any useful function other than
reminding the coder what [s]he's doing? I would write such
code using an ordinary array and restricting it for the
functionality that I needed. Maybe that's because I've never
written code for a large project where it's assumed that
someone else will need to expand and maintain it.

To be honest, the only time I've ever implemented a ParamArray is when
I was going to use the function in a query where I wasn't _always_
going to know how many variables I was going to be passing to the
query.

For example, a query where I was selecting on a crosstab where I
wouldn't know how many columns were going to be there, or how many
fields would have Null values. Passing the columns I needed to do
<whatever> with as a ParamArray resolved that issue, however I did
have to handle the Nulls in the code of the function.

Even still, I've always thought of it as a bit of a "Hack". But it
works, it solved the problem, and is rather stable. So I guess
there's no real problem using them, I'm just not a big fan.

Personally, I never use Subs to modify an existing array (or any other
kind of variable for that matter), it's just not something I do, so
that's never an issue with me. (I prefer to have a function return
something, as it's _far_ clearer to programmers "down the line" who
may have to work on my code. Feel free to do as you feel the need to
do however. I don't begrudge the people who do this, it's just not
something *I* do.)

I should also mention that I rarely pass arrays around anyways.
Again, no real reason, it's just something I don't do. I'd rather use
a Type, so that I _know_ (and have semi-documented in the Type's
Declare statement) what is supposed to be in what element.

When passing _anything_ _anywhere_ however, I try to be as explicit as
possible when I write the function. I also (perhaps sadly) tend to
leave a lot of "dead" code with lots of comments on the dead code, and
what the (working) code is doing. This not only helps the programmers
down the line, but me as well, when I have to go back to the code
several years (months, projects) later and make a fix. The "School of
Hard Knocks" has some fairly harsh teachers!
--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

[David Schofield]

On Thu, 07 Jul 2005 17:40:44 -0500, Chuck Grimsby
<c.*******@worldnet.att.net.invalid> wrote:

You can also pass a bunch of variables to a function as a ParamArray:

(This example was actually written by Joe Foster, of "Nuke Me Zemu"
legend)

<snip>

Hi
It was "Nuke me Xemu"

After reading
http://www.googlism.com/what_is/x/xemu/
I am none the wiser though it seems to be related to scientology.

On the subject of the post I was going to suggest returning a private
type but by the time I had looked for Xemu I see someone has done
that. Types (structures) have rather gone out of fashion, displaced by
objects, which is odd for database users - after all a recordset is
only an a collection or array of structures. VB has no way of
automatically creating a type to match a recordset, and disconnected
recordsets were introduced as an out-of-language feature as though
they were something new.

David

[David W. Fenton]

Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote in
news:uh********************************@4ax.com:

On Fri, 08 Jul 2005 02:45:12 GMT, rkc
<rk*@rochester.yabba.dabba.do.rr.bomb> wrote:

Chuck Grimsby wrote:

You can also pass a bunch of variables to a function as a
ParamArray: (This example was actually written by Joe Foster, of
"Nuke Me Zemu" legend)
Function RowAvg(ParamArray Stuff() As Variant) As Variant

That's nice, but using an array of a defined type would
clear up what the function actually expected to be passed
in. It's tough to guarantee a result when your only argument
will except any number of any data type, including objects.

Personally, I quite agree! Indeed, I tend to write code in
exactly that fashion myself, but I also think it's important to
know _all_ the options, not just those I happen to agree with.

Passing a parameter array, though, is extremely useful for cases
where you want to pass multiple values, all of the same type, but
don't know until runtime exactly how many. It's a stupid example,
but what if you had a function that concatenated a list of items and
returned them as a list. With a parameter array, you don't need to
know how many items your passing in.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Thelma Lubkin <th****@alpha2.csd.uwm.edu> wrote in
news:da**********@uwm.edu:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote:
: Chuck Grimsby wrote:
:> On Fri, 08 Jul 2005 02:45:12 GMT, rkc
:> <rk*@rochester.yabba.dabba.do.rr.bomb> wrote:
:>
:>
:>>Chuck Grimsby wrote:
:>>
:>>>You can also pass a bunch of variables to a function as a
:>>>ParamArray: (This example was actually written by Joe Foster,
:>>>of "Nuke Me Zemu" legend)
:>>>Function RowAvg(ParamArray Stuff() As Variant) As Variant
: You're right. So let's add that a paramarray is always optional,
: must always be the last argument in the list and is always passed
: ByVal. Always being passed ByVal means it can't be used as a way
: to return values to the calling code.

Does the ParamArray type have any useful function other
than reminding the coder what [s]he's doing? I would write
such code using an ordinary array and restricting it for
the functionality that I needed. Maybe that's because I've
never written code for a large project where it's assumed
that someone else will need to expand and maintain it.

I answered this question in a previous post, in a context where my
answer didn't make much sense. :)

I wrote:

Passing a parameter array, though, is extremely useful for cases
where you want to pass multiple values, all of the same type, but
don't know until runtime exactly how many. It's a stupid example,
but what if you had a function that concatenated a list of items and
returned them as a list. With a parameter array, you don't need to
know how many items your passing in.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

dn***************@blueyonder.co.uk (David Schofield) wrote in
news:42cf013f.20153419@localhost:

On Thu, 07 Jul 2005 17:40:44 -0500, Chuck Grimsby
<c.*******@worldnet.att.net.invalid> wrote:

You can also pass a bunch of variables to a function as a
ParamArray:

(This example was actually written by Joe Foster, of "Nuke Me
Zemu" legend)

<snip>

It was "Nuke me Xemu"

After reading
http://www.googlism.com/what_is/x/xemu/
I am none the wiser though it seems to be related to scientology.

When L. Ron Hubbard invented Scientology, part of the mythology was
that there was a being called Xemu who populated the Earth with
souls, called Thetans. He then bombarded the Earth with nuclear
weapons and killed all the people. The souls of these tormented
Thetans now inhabit living people, and all unhappiness in people in
the present day is a result of the torment of the Thetan within you.

If you want to get rid of the unhappiness, you have to pay 100s of
thousands of dollars to the Church of Scientology for them to do
various things to you, like take various electric readings of your
brain and so forth that are supposed to treat the problems.

Of course, we can't really say what it all means as the documents
describing all of this are copyrighted and the Scientologists have a
vicious legal department that goes after anyone who tries to publish
those documents in the interests of criticism (and/or parady, as if
there were a need for it).

It is as ludicrous as it sounds, and Joe's signature was a way of
poking fun at the Scientologists back in the days when they were
going after everybody and his dog on the Internet for mentioning
Scientology and any of the "facts" found in the copyrighted "church"
documents.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[rkc]

David W. Fenton wrote:

If you want to get rid of the unhappiness, you have to pay 100s of
thousands of dollars to the Church of Scientology for them to do
various things to you, like take various electric readings of your
brain and so forth that are supposed to treat the problems.

A great situation in which to use a paramarray since you don't
know how many or what type of readings will need to be passed
to the brainReadings() function.

[rkc]

David W. Fenton wrote:

I answered this question in a previous post, in a context where my
answer didn't make much sense. :)

I wrote:

Passing a parameter array, though, is extremely useful for cases
where you want to pass multiple values, all of the same type, but
don't know until runtime exactly how many. It's a stupid example,
but what if you had a function that concatenated a list of items and
returned them as a list. With a parameter array, you don't need to
know how many items your passing in.

The only advantage I can find that a paramarray has over an array as
a parameter is that using ubound on an uninitialized paramarray returns
-1 instead of throwing an error. But then IsMissing() gives you the
same information without having to know that.