User defined function with list as a result

[sandros]

Hi, I've created an UDF which takes some arguments and returns a list as a result, see example:

Function myFunction(a, b, c)

Dim MyFunction_result(1 To 4) As Double

result1 = a + b + c
result2 = a * b * c
result3 = Sqr(a ^ 2 + b ^ 2 + c ^ 2)
result4 = a * (b + c)

MyFunction_result(1) = result1
MyFunction_result(2) = result2
MyFunction_result(3) = result3
MyFunction_result(4) = result4

MyFunction = myFunction_result

End Function

Now, I'm refering to that function in subroutine where I'm asigning a variable to a one of the list members, say:

Sub testMyFunction()

variable = myFunction(5, 6, 7)(3)

End Sub

which gives me a expected result of 10.488, i. e. Sqr(5^2 + 6^2 + 7^2).

How could I use my function in a sheet and get same result, depending which member of a list I want?

If I enter formula "=myFunction(5, 6, 7)(3)" in a cell, I get #REF! error, but when I enter it as "=myFunction(5, 6, 7)" I get spilled result with all list members in adjacent cells. So, I just want to get, say, 3rd member of a list. How to do that?

 

[StephenCrump]

Try: =INDEX(myFunction(5,6,7),3)

 

[sandros]

Try: =INDEX(myFunction(5,6,7),3)

Hi StephenCrump, thank you for your reply! It works!

One more question, do you think that defining functions this way can be in some way problematic and, perhaps, bad practice? I used this principle in Fortran but I don't know is this a good way in VBA.

 

[StephenCrump]

I'd be happy using something similar, and wouldn't call it bad practice, but hey, I'm not a real programmer!

If you were going to keep it in VBA, you could make it a little more formal by defining your own data type:

Type MyResult
    Result1 As Double
    Result2 As Double
    Result3 As Double
    Result4 As Double
End Type
Sub Test()

    Dim x As MyResult
    
    x = myFunction(5, 6, 7)
    MsgBox x.Result3
    
End Sub
Function myFunction(a, b, c) As MyResult

    Dim temp As MyResult
    
    temp.Result1 = a + b + c
    temp.Result2 = a * b * c
    temp.Result3 = Sqr(a ^ 2 + b ^ 2 + c ^ 2)
    temp.Result4 = a * (b + c)
        
    myFunction = temp

End Function

Or to act even more like a programmer, you could create a VBA class, i.e. your own customised object. There's lots of good material that a quick Google will identify, including
VBA Class Modules - The Ultimate Guide - Excel Macro Mastery

 

[sandros]

Thank you for your reply and advice! I'll definitely consider a VBA class!