Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Parameters as Variants Only
Am I correct in my assumption that I can only pass arrays in the form of
variants to a procedure as a parameter. For example, the following function always works for arrays Function DoNothing(ByRef ArrIn as variant) DoNothing = arrIn End Function While the following function never seems to work for arrays even if they are dimensioned as integer arrays. Function DoNothing(ByRef ArrIn as integer) DoNothing = arrIn End Function Regards, Wayne C. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Parameters as Variants Only
Sub Main()
Dim arr() As Integer Dim v As Variant ReDim arr(1 To 3) For i = 1 To 3 arr(i) = i Next v = DoNothing(arr) For i = LBound(v) To UBound(v) Debug.Print i, v(i) Next End Sub Function DoNothing(ByRef ArrIn() As Integer) DoNothing = ArrIn End Function works for me. -- Regards, Tom Ogilvy "TheVisionThing" wrote in message m... Am I correct in my assumption that I can only pass arrays in the form of variants to a procedure as a parameter. For example, the following function always works for arrays Function DoNothing(ByRef ArrIn as variant) DoNothing = arrIn End Function While the following function never seems to work for arrays even if they are dimensioned as integer arrays. Function DoNothing(ByRef ArrIn as integer) DoNothing = arrIn End Function Regards, Wayne C. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Parameters as Variants Only
TheVisionThing wrote:
Am I correct in my assumption that I can only pass arrays in the form of variants to a procedure as a parameter. No. Perhaps you are thinking about the parameters of a ParamArray array. Alan Beban For example, the following function always works for arrays Function DoNothing(ByRef ArrIn as variant) DoNothing = arrIn End Function While the following function never seems to work for arrays even if they are dimensioned as integer arrays. Function DoNothing(ByRef ArrIn as integer) DoNothing = arrIn End Function Regards, Wayne C. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Parameters as Variants Only
Your code snippet works when the array is called within a Sub. I have seen
and tried code snippets from other posts using the same approach you outlined,but have never been able to get it to work as a worksheet function call. If the function is used in a cell on an Excel worksheet and the array values are called from a range on a worksheet, the result is the #VALUE error message For example, the values 1,2,3 are in Rows C3:C5 and named ArrIn. When =DoNothing(ArrIn) or DoNothing(ArrIn()) is entered in Cell D7 , the result is always #VALUE! . Am I missing something? thebaje "Tom Ogilvy" wrote: Sub Main() Dim arr() As Integer Dim v As Variant ReDim arr(1 To 3) For i = 1 To 3 arr(i) = i Next v = DoNothing(arr) For i = LBound(v) To UBound(v) Debug.Print i, v(i) Next End Sub Function DoNothing(ByRef ArrIn() As Integer) DoNothing = ArrIn End Function works for me. -- Regards, Tom Ogilvy "TheVisionThing" wrote in message m... Am I correct in my assumption that I can only pass arrays in the form of variants to a procedure as a parameter. For example, the following function always works for arrays Function DoNothing(ByRef ArrIn as variant) DoNothing = arrIn End Function While the following function never seems to work for arrays even if they are dimensioned as integer arrays. Function DoNothing(ByRef ArrIn as integer) DoNothing = arrIn End Function Regards, Wayne C. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Parameters as Variants Only
ArrIn isn't an array. It is a range.
In that situation you could do Public Function Test(arr As Range) As Variant Dim vArr as Variant vArr = arr.Value Test = vArr End Function Assuming you wanted to do something to the values of the cells in the range before you passed back the answer. -- Regards, Tom Ogilvy "thebaje" wrote in message ... Your code snippet works when the array is called within a Sub. I have seen and tried code snippets from other posts using the same approach you outlined,but have never been able to get it to work as a worksheet function call. If the function is used in a cell on an Excel worksheet and the array values are called from a range on a worksheet, the result is the #VALUE error message For example, the values 1,2,3 are in Rows C3:C5 and named ArrIn. When =DoNothing(ArrIn) or DoNothing(ArrIn()) is entered in Cell D7 , the result is always #VALUE! . Am I missing something? thebaje "Tom Ogilvy" wrote: Sub Main() Dim arr() As Integer Dim v As Variant ReDim arr(1 To 3) For i = 1 To 3 arr(i) = i Next v = DoNothing(arr) For i = LBound(v) To UBound(v) Debug.Print i, v(i) Next End Sub Function DoNothing(ByRef ArrIn() As Integer) DoNothing = ArrIn End Function works for me. -- Regards, Tom Ogilvy "TheVisionThing" wrote in message m... Am I correct in my assumption that I can only pass arrays in the form of variants to a procedure as a parameter. For example, the following function always works for arrays Function DoNothing(ByRef ArrIn as variant) DoNothing = arrIn End Function While the following function never seems to work for arrays even if they are dimensioned as integer arrays. Function DoNothing(ByRef ArrIn as integer) DoNothing = arrIn End Function Regards, Wayne C. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Parameters as Variants Only
thebaje wrote:
Your code snippet works when the array is called within a Sub. I have seen and tried code snippets from other posts using the same approach you outlined,but have never been able to get it to work as a worksheet function call. If the function is used in a cell on an Excel worksheet and the array values are called from a range on a worksheet, the result is the #VALUE error message For example, the values 1,2,3 are in Rows C3:C5 and named ArrIn. When =DoNothing(ArrIn) or DoNothing(ArrIn()) is entered in Cell D7 , the result is always #VALUE! . The above is simply wrong. Perhaps there is something in your code for DoNothing that causes it. With 1,2,3 in C3:C5, which is named "ArrIn", with Function DoNothing(whatever) As Integer DoNothing = Application.Sum(whatever) End Function then on the worksheet =DoNothing(ArrIn) returns 6 Alan Beban Am I missing something? thebaje |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Too few parameters | Excel Discussion (Misc queries) | |||
SQL & Like Parameters | Excel Discussion (Misc queries) | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Too few parameters | Excel Discussion (Misc queries) | |||
templated array as function parameters | Excel Programming |