A function that returns more than one argument

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Jay Petrulis

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
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

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
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

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
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.
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,369
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,009
Messages
5,569,586
Members
412,281
Latest member
GraYmOnD0808
Top