Create Function which returns an Array

E

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
 
C

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
 
C

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!
 
J

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
 
J

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.
 
J

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.
 
E

ExcelMonkey

Slick. Thanks again

EM

Jim Thomlinson said:
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
 
C

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.
 
C

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top