A function that returns more than one argument

[mortgageman]

As I have been writing my wari game, I need a function to return two arguments. I came up with this way. It may already be known, but I thought I would share it with anyone who needs it. The idea is to return an array into a variable THAT MUST BE DIMENSIONED AS VARAINT. The Function itself declares the array. I have called it a package because back when I was an APL'er we had a similar (but more advanced) concept that was called a package.

FWIW, this worked fine in VBA. When I tried arrayfunction in Excel using just enter I only get the first argument returned. When I tried CSE, I get a device IO error. So user beware.

Gene, "The Mortgage Man", Klein




Public Sub getafunctionarray()
Dim package As Variant
package = arrayfunction
MsgBox package(1)
MsgBox package(2)
End Sub


Public Function arrayfunction()
Dim package(1 To 2) As Integer
package(1) = 1
package(2) = 2
arrayfunction = package
End Function

 

[Jay Petrulis]

I had no problems using this as a UDF. (Excel 2003 SP2)

Can you replicate this on another machine? Can you change the name of the package variable and reenter? Possibly package has some meaning, not as a reserved word, but similar. Doubtful, but might be worth a look.

 

[mortgageman]

I had no problems using this as a UDF. (Excel 2003 SP2)

Did you use it as a CSE or normal? Did you get both results in the same cell?

Gene, "The Mortgage Man", Klein

 

[Jay Petrulis]

If the UDF calculates an array, to return the array you need to also call it as an array formula.

In a 1 row x 2 column range, enter the formula and CSE to confirm.

If you want the results in a 2 row x 1 col range, use the TRANSPOSE function with the UDF. This can be done within the UDF as well.

 

[mortgageman]

In a 1 row x 2 column range, enter the formula and CSE to confirm.

Worked like a charm!

Thanks for the info

Gene, "The Mortgage Man", Klein

 

[JenniferMurphy]

I came across this thread while searching for a way to have a VBA macro return more than 1 result. This is an old thread, but I found some good information, so I thought I'd post it in case it might be useful to someone else.

My search turned up this link, which offers 8 ways:

http://www.geeksengine.com/article/vba-function-multiple-values.html

I am using the ByRef method, which is doing just what I needed.