Help with Returning muliple values from a function

[u123136]

Please help,
I need to return multiple values from a function with for inputs:
function name(i1,i2,i3,i4) As Variant
Dim d(5)
Dim other variables
function calulation
name = Array(d1,d2,d3,d4,d5)
end function.
This code does not work as I need to horizontaly return each value each column next to each other.
Thanks,
Bill

 

[u123136]

All,
The purpose of the VB function is to compute the distance in nautical miles between two sets of Latitudes and Longitudes and return the distance. The function works fine I just wanted to also return the distance in Statute Miles, Kilometers and Feet. I though I could return an array with each value in columns next to each other. I guess that can not be done.
Bill

 

[BThiede]

It is possible, but youi need to use an array function (that is, you need to return an array). I just put together a test. Put the following in a workbook module:

Function DummyArray(val1 As Variant, val2 As Variant, val3 As Variant) As Variant
    Dim retArray(1 To 3) As Variant
    
    retArray(1) = val1
    retArray(2) = val2
    retArray(3) = val3
    
    DummyArray = retArray
End Function

Now to use the function you have to select 3 cells in a row (because the return array is 1 dimensional, 3 elements) at the same time and enter the formula:

=DummyArray(1,2,3)

And then to make it all work you MUST press Ctrl+Shift+Enter to enter the function... not tab, not enter, not anything else.

If you did it right you'll see the values (1, 2, 3) in cells going across the row and if you look at the formula bar for any of those cells it will look like {=DummyArray(1,2,3)} (notice the curly braces that get added when you press Ctrl+Shift+Enter).

I hope this points you in the right direction, but for your question (and I'm no nautical expert) can't you just add a conversion to each subsequent column (=val*conversionFactor)? This approach beats arrays in performance anyday.

Good luck,
Brendon

 

[BThiede]

Just in case anyone who stumbles accross this is interested, here's an example of a two dimensional return array:

Function DummyArray2D(val1 As Variant, val2 As Variant, val3 As Variant, val4 As Variant, val5 As Variant, val6 As Variant) As Variant
    Dim retArray(1 To 2, 1 To 3) As Variant
    
    retArray(1, 1) = val1
    retArray(1, 2) = val2
    retArray(1, 3) = val3
    retArray(2, 1) = val4
    retArray(2, 2) = val5
    retArray(2, 3) = val6
    
    DummyArray2D = retArray
End Function

Same rules apply for entry as previously mentioned, but here's a note on what to select before entering the formula. For this function the array is treated as 2 rows high and 3 columns across. If you select a larger range, you'll get #N/A values, if you select smaller, you just miss some of your values. The one dimensional arrays work similarly, except if you select more than one row, the first row is repeated.

Brendon