Create Function which returns an Array

[ExcelMonkey]

I want to create a function which returns an array. For Example I want
ArrayFunction to return X,Y.

I will then load the results of the Function into a 2D Array with 2 colums:

Private FunctionArray(A as String, B as String) As Variant
'This needs to produce a result that looks like X,Y

End Function

How do I set up the function to return an array result (2 values)?


Thanks

EM

 

[Charlie]

To declare a function as an array function it must be typed as String, Long,
Double, etc., not Variant

Public Function XYCoords(X as String, Y as String) As Double()

Dim dTmp as Double

ReDim dTmp(2) 'Note - I always use Option Base 1 in all my modules

dTmp(1) = CDbl(X)
dTmp(2) = CDbl(Y)
XYCoords = dTmp

End Function

End Function

 

[Charlie]

Correction

Dim dTmp() as Double


BTW, you can also ReDim dTmp as a two-dimension array and your array
function will return a 2D array!

 

[Jim Thomlinson]

Here is a simple example...

Sub test()
Dim var As Variant

var = MyArray("This", "That")
End Sub

Public Function MyArray(ByVal str1 As String, ByVal str2 As String) As Variant
Dim aryReturn(2, 2) As Variant
Dim lng1 As Long
Dim lng2 As Long

For lng1 = 0 To 2
For lng2 = 0 To 2
aryReturn(lng1, lng2) = str1 & lng1 & str2 & lng2
Next lng2
Next lng1
MyArray = aryReturn

End Function

 

[Jim Thomlinson]

You can not redim a 1d array into a 2d array. Additionally if you redim a 2 d
array you can only redimension the first element.

 

[Jim Thomlinson]

A variant can be anything so it is acceptable to declare an array as variant.
Where possible you want to avoid variants, but if you are not sure what
values are going into the array then variant is not a bad choice as it will
hold anything. Your example is a 1d array which is not what was asked for.

 

[ExcelMonkey]

Slick. Thanks again

EM

Here is a simple example...

Sub test()
Dim var As Variant

var = MyArray("This", "That")
End Sub

Public Function MyArray(ByVal str1 As String, ByVal str2 As String) As Variant
Dim aryReturn(2, 2) As Variant
Dim lng1 As Long
Dim lng2 As Long

For lng1 = 0 To 2
For lng2 = 0 To 2
aryReturn(lng1, lng2) = str1 & lng1 & str2 & lng2
Next lng2
Next lng1
MyArray = aryReturn

End Function

 

[Charlie]

Sorry for the ambiguous statment. I certainly didn't mean to imply that a 1D
array could be ReDimmed into a 2D array. I meant that the OP could initially
ReDim dTmp into a 2D array, fill it as desired, and return it through the
function name. Much like your example further down.

 

[Charlie]

I beg to differ on what was asked for. The OP indicated that he/she would
take the results of the array (X, Y) and "then load the results of the
Function into a 2D Array"

Sounded like the OP was asking for a 1D array to be returned. Which was why
I later added the part about ReDimming dTmp to a 2D array, implying that the
OP could do it all in the function and save time.