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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

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,867
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,124
Messages
5,835,544
Members
430,366
Latest member
glastonbury

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top