| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Function which returns an Array
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create Function which returns an Array
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create Function which returns an Array
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create Function which returns an Array
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create Function which returns an Array
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create Function which returns an Array
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create Function which returns an Array
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create Function which returns an Array
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Create Function which returns an Array
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array function that returns values within several intervals | Excel Worksheet Functions | |||
| Create VBA function that returns many values | Excel Programming | |||
| custom excel function returns array, showing #VALUE in cells | Excel Programming | |||
| How to use function that returns array of variable size? | Excel Programming | |||
| how do I create a UDF (VBA) that returns an array (ctrl+shift+enter) | Excel Programming | |||
Hybrid Mode
