Trying to return multiple values in a function VBA [duplicate]

Question

For a complex calculation I have written a VBA function. But ideally I would return more than one result during testing.

My_Function(someinput)
Result1 = "Result1"
Result2 = "Result2"
Result3 = "Result3"
Result4 = "Result4"
End Function

In a cell I would like to write:

=My_Function.Result1(someinput)

And the return would be:

"Result1"

Is this possible?

Do you mean you want to be able to get back one or Mutiple results to a single cell? — Shai Rado
– Shai Rado, Commented Aug 28, 2023 at 15:20
"I would return more than one result during testing" - why? Can you expand on this a little? In your example, why is "Result1" the expected return value? — Tim Williams
– Tim Williams, Commented Aug 28, 2023 at 15:41
If I understand you correctly, you want to have a function, that returns one of several possibilities. There is no syntax of the form you want, but you can easily achieve the same thing by having a second parameter which tells your function which return value is required — Jonathan Willcock
– Jonathan Willcock, Commented Aug 28, 2023 at 16:25
I want for debugging purposes to define which value I return from within the complex function. Now I am constantly changing the function output to a single value and not getting the full result — Jacco
– Jacco, Commented Aug 28, 2023 at 20:39

rotabor · Accepted Answer · 2023-08-28 17:54:41Z

1

The way you can use is:

Function My_Function(someinput As Variant, selector As Integer) As Variant
  My_Function = Choose(selector, "Result1", "Result2", "Result3", "Result4")
End Function

=My_Function("asda"; 2) --> "Result2"

Another way is already mentioned returning of an array:

Function My_Function(someinput As Variant) As Variant
  Dim av As Variant
  ReDim av(3)
  av(0) = "Result1"
  av(1) = "Result2"
  av(2) = "Result3"
  av(3) = "Result4"
  My_Function = av
End Function

=My_Function("asda") --> { "Result1", "Result2", "Result3", "Result4" }

edited Aug 28, 2023 at 17:54

answered Aug 28, 2023 at 17:48

rotabor

5,6112 gold badges5 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lord-JulianXLII · Accepted Answer · 2023-08-29 07:30:34Z

1

You can return a User Defined Type (https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/type-statement)

Type Results
    result1 As String
    result2 As String
    result3 As Long
End Type

Function myFunction(someInput As Variant) As Results
    myFunction.result1 = "Result1"
    myFunction.result2 = "Result2"
    myFunction.result3 = 15
End Function

Sub test()
    Debug.Print myFunction("hi").result1
End Sub

This approach has the added benefit, that all return values are type checked.

answered Aug 29, 2023 at 7:30

Lord-JulianXLII

1,3091 gold badge10 silver badges19 bronze badges

1 Comment

rotabor Over a year ago

This solution doesn't allow to use the function on a worksheet.

Collectives™ on Stack Overflow

Trying to return multiple values in a function VBA [duplicate]

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related