Return Information from Functions

[Austin Amaya]

Hello --

I'm looking for ideas of how to get multiple values from a function. Ideally, I'm looking for a function to return an array, but this hasn't seemed to work.

I'd appreciate suggestions/ideas an/or solutions. At the moment, I'm considering returning a string containing comma-separated values, then processing this string. Is there a better solution?

Thanks,
Austin

 

[Auric__]

Hello --

I'm looking for ideas of how to get multiple values from a function.
Ideally, I'm looking for a function to return an array, but this hasn't
seemed to work.

I'd appreciate suggestions/ideas an/or solutions. At the moment, I'm
considering returning a string containing comma-separated values, then
processing this string. Is there a better solution?

Thanks,
Austin

If the return values are all of the same type, you can declare an empty
array and assign the return value to that. (This works in Excel 2000. No
clue about other versions.)
Sub foo()
Dim x As Integer
Dim y() As Byte
x = 12345
y = bar(x)
For n = 0 To UBound(yz)
MsgBox y(n)
Next
End Sub
Function bar(a As Integer) As Byte() 'returns an array
Dim b(1) As Byte
b(0) = Abs(a \ 256)
b(1) = Abs(a Mod 256)
bar = b
End Function

Alternately, you can pass the variables that will hold the return values
by reference and skip arrays, so you can return multiple data types.
(Note that the function has become a sub, since it doesn't need to
return a value.)
Sub foo()
Dim x As Integer, y As Byte, z As String
x = 12345
bar x, y, z
MsgBox y & " " & z
End Sub
Sub bar(ByRef a As Integer, ByRef b As Byte, ByRef c As String)
b = Abs(a \ 256)
c = CStr(a Mod 256)
End Sub
--
auric "underscore" "underscore" "at" hotmail "dot" com
*****
Be a yardstick of quality. Some people aren't used to an environment
where excellence is expected.
-- Steve Jobs

 

[Austin Amaya]

Many many thanks! Works like a charm.

-Austin

-----Original Message-----


If the return values are all of the same type, you can declare an empty
array and assign the return value to that. (This works in Excel 2000. No
clue about other versions.)
Sub foo()
Dim x As Integer
Dim y() As Byte
x = 12345
y = bar(x)
For n = 0 To UBound(yz)
MsgBox y(n)
Next
End Sub
Function bar(a As Integer) As Byte() 'returns an array
Dim b(1) As Byte
b(0) = Abs(a \ 256)
b(1) = Abs(a Mod 256)
bar = b
End Function

Alternately, you can pass the variables that will hold the return values
by reference and skip arrays, so you can return multiple data types.
(Note that the function has become a sub, since it doesn't need to
return a value.)
Sub foo()
Dim x As Integer, y As Byte, z As String
x = 12345
bar x, y, z
MsgBox y & " " & z
End Sub
Sub bar(ByRef a As Integer, ByRef b As Byte, ByRef c As String)
b = Abs(a \ 256)
c = CStr(a Mod 256)
End Sub
--
auric "underscore" "underscore" "at" hotmail "dot" com
*****
Be a yardstick of quality. Some people aren't used to an environment
where excellence is expected.
-- Steve Jobs
.