John W. Colby
jwcolby at colbyconsulting.com
Mon Dec 27 20:16:56 CST 2004
In this case, the function is called from a query, where I need to be able
to pass in N fields from the query. The number can change. There is no
place to dimension or set up the array.
John W. Colby
www.ColbyConsulting.com
Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
Sent: Monday, December 27, 2004 2:19 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Passing Param arrays
================================
Nested Functions Using Arrays As Arguments ================================
This pertains to functions using array arguments and then returning an
array as the result. Observations placed below are based upon tests
conducted on Access XP (running on Win XP).
A function using ParamArray as the keyword for its array argument is
governed by the following limitations.
(a) Each element of the array is required to be mentioned individually.
Simply using an array variable (or a function that returns an array) in
lieu, will not do.
(b) ParamArray keyword is not to be preceded with ByVal, ByRef, or
Optional keywords.
(c) ParamArray type argument is to be used only as the last argument in
arglist to indicate that the final argument is an optional array of Variant
elements.
On the other hand, if the array argument is handled without using the
keyword ParamArray, there is far greater flexibility regarding the manner
in which the array argument can be supplied. Any of the following
alternatives will do.
(a) An array variable
(b) A function returning an array
(c) A literal array using the built-in Array() function.
Example - Array(2,4,8,16,32,64)
It would thus be seen that, if functions returning array values are to
be nested, those having ParamArray as the keyword stand disqualified, except
for being used as the very first step. All higher layer functions have to be
those using an array variable as their argument, without ParamArray keyword.
As an illustration, two sample functions are given below. Fn_PAr() uses
ParamArray as the keyword for its array argument, while Fn_Ar() uses a
normal array variable as its argument, without the keyword ParamArray.
Both these functions double the value of each element of the input array
argument and return the result as an array.
Sample test procedures for verifying the performance of above functions
under three layers of nesting are also given below. Brief details -
(a) Sub P_TestArrayFunctions_A()
Three level nesting of array functions.
Function having ParamArray as its argument is used as an array
argument for another function, which in turn, is used as
an array argument for the final function.
(b) Sub P_TestArrayFunctions_B()
Three level nesting of array functions.
Function having an array variable as its argument is used as an
array argument for another function, which in turn, is used as an array
argument for the final function.
(c) Sub P_TestArrayFunctions_C()
Three level nesting of array functions.
Function having a literal Array() function as its argument is
used as an array argument for another function, which in turn, is used as an
array argument for the final function.
Conclusion
--------------
Considering its superior flexibility as explained above, a function
using a straight array variable as its argument (without being preceded by
the keyword ParamArray) should prove universally more convenient as compared
to one with ParamArray keyword.
While it is amenable to multi-level nesting, accepting array variable
(or function returning an array) as its argument, it can also mimic the
ParamArray style input (i.e. literal supply of individual elements of array
argument) as demonstrated in test procedure Sub P_TestArrayFunctions_C()
below.
A.D.Tejpal
--------------
===================================
Function Fn_PAr(ParamArray Pa() _
As Variant) As Variant
' Doubles each element of ParamArray
' argument Pa() and returns the result as an array
Dim La As Variant, Cnt As Long
ReDim La(UBound(Pa))
For Cnt = LBound(Pa) To UBound(Pa)
La(Cnt) = 2 * Pa(Cnt)
Next
Fn_PAr = La
End Function
'------------------------------------
Function Fn_Ar(ByVal Ar As Variant) As Variant
' Doubles each element of array argument Ar
' and returns the result as an array
Dim La As Variant, Cnt As Long
ReDim La(UBound(Ar))
For Cnt = LBound(Ar) To UBound(Ar)
La(Cnt) = 2 * Ar(Cnt)
Next
Fn_Ar = La
End Function
'------------------------------------
Sub P_TestArrayFunctions_A()
' Three level nesting of array functions.
' Function having ParamArray as its
' argument is used as an array argument for
' another function, which in turn, is used as
' an array argument for the final function.
Dim Rtv As Variant, Cnt As Long
Rtv = Fn_Ar(Fn_Ar(Fn_PAr(2, 4, 8, 16, 32, 64)))
Debug.Print LBound(Rtv) & ", " & UBound(Rtv)
For Cnt = LBound(Rtv) To UBound(Rtv)
Debug.Print Rtv(Cnt)
Next
End Sub
'------------------------------------
Sub P_TestArrayFunctions_B()
' Three level nesting of array functions.
' Function having an array variable as its
' argument is used as an array argument
' for another function, which in turn, is used
' as an array argument for the final function.
Dim Rtv As Variant
Dim Cnt As Long, Ar As Variant
Ar = Array(2, 4, 8, 16, 32, 64)
Rtv = Fn_Ar(Fn_Ar(Fn_Ar(Ar)))
Debug.Print LBound(Rtv) & ", " & UBound(Rtv)
For Cnt = LBound(Rtv) To UBound(Rtv)
Debug.Print Rtv(Cnt)
Next
End Sub
'------------------------------------
Sub P_TestArrayFunctions_C()
' Three level nesting of array functions.
' Function having a literal Array() function as
' its argument is used as an array argument for
' another function, which in turn, is used as an
' array argument for the final function.
Dim Rtv As Variant, Cnt As Long
Rtv = Fn_Ar(Fn_Ar(Fn_Ar(Array(2, 4, 8, 16, 32, 64))))
Debug.Print LBound(Rtv) & ", " & UBound(Rtv)
For Cnt = LBound(Rtv) To UBound(Rtv)
Debug.Print Rtv(Cnt)
Next
End Sub
===================================
----- Original Message -----
From: John W. Colby
To: 'Access Developers discussion and problem solving'
Sent: Friday, December 24, 2004 09:35
Subject: [AccessD] Passing Param arrays
I have a function which has a ParamArray FieldNames()
This function calls another function with the same ParamArray FieldNames()
The first function attempts to call the second function, passing the
paramarray Fieldnames on to the second function.
In debug mode I can step through the ParamArray(0), etc and see that there
is indeed data in the param array. The 2nd function receives the paramarray,
but it is empty.
Does anyone know if it is possible to pass a paramarray back out to a
second function?
John W. Colby
www.ColbyConsulting.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com