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

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

"ExcelMonkey" wrote:

> 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

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!



"Charlie" wrote:

> 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
>
> "ExcelMonkey" wrote:
>
> > 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

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

Jim Thomlinson


"ExcelMonkey" wrote:

> 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

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

Jim Thomlinson


"Charlie" wrote:

> 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!
>
>
>
> "Charlie" wrote:
>
> > 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
> >
> > "ExcelMonkey" wrote:
> >
> > > 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

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

Jim Thomlinson


"Charlie" wrote:

> 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
>
> "ExcelMonkey" wrote:
>
> > 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

Slick. Thanks again

EM

"Jim Thomlinson" wrote:

> 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
> --
> HTH...
>
> Jim Thomlinson
>
>
> "ExcelMonkey" wrote:
>
> > 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

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.

"Jim Thomlinson" wrote:

> 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.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Charlie" wrote:
>
> > 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!
> >
> >
> >
> > "Charlie" wrote:
> >
> > > 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
> > >
> > > "ExcelMonkey" wrote:
> > >
> > > > 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

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.

"Jim Thomlinson" wrote:

> ... Your example is a 1d array which is not what was asked for.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Charlie" wrote:
>
> > 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
> >
> > "ExcelMonkey" wrote:
> >
> > > 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