A function that returns more than one argument

[lupacexi]

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

 

[mikerickson]

One way to have a function return two results would be to use ByRef arguments.

Function PolarToRect(R As Double, Theta As Double, ByRef xCoord As Double, ByRef yCoord As Double) As Boolean
    xCoord = R * Cos(Theta)
    yCoord = R * Sin(Theta)
    PolarToRect = True
End Function

Sub demo()
    Dim xVal As Double, yVal As Double
    Dim Pi As Double: Pi = 4 * Atn(1)
    
    PolarToRect 3, Pi / 3, xVal, yVal
    
    MsgBox xVal & ", " & yVal & " is at 60 degrees"
End Sub

 

[Marcelo Branco]

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.

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

If you select a horizontal range, say A2:B2, and type
=arrayfunction()
and confirm with Ctrl+Shift+Enter, it should work.

M.

 

[Kenneth Hobson]

Welcome to the forum!

I guess enter it as an array as you are returning an array?

Use arrayfunction=Application.Transpose(package) if you want to return a column array.

Public Sub getafunctionarray()
  Dim package As Variant
  package = arrayfunction
  MsgBox Join(package, vbLf)
End Sub

'=arrayfunction() 'Select 2 cells in row, Alt+Ctrl+Enter
Public Function arrayfunction()
  Dim package(1 To 2) As Integer
  package(1) = 1
  package(2) = 3
  arrayfunction = package
End Function

 

[Marcelo Branco]

If you do need the values in a vertical range, say A2:A3, select both cells and try
=TRANSPOSE(arrayfunction())
Ctrl+Shift+Enter

M.