[AccessD] Passing ParamArrays down the line

John:

It's true: you can't pass a ParamArray to a routine expecting a Variant() or
Variant parameter. You have to convert to a Variant array first.

You can pass ParamArrays to other routines accepting a ParamArray. But, the
initial ParamArray gets nested in element(0) in the ParamArray of the called
routine, making the values hard to extract.

This routine takes any ParamArray with any level of nesting, and returns the
actual values as a plain variant array:

' UnpackParamArray
'
' Unpacks the given ParamArray, and returns it as a Variant array
' * Handles nested ParamArray calls
' * Handles an array passed to a ParamArray
'
' Allows several parameter passing styles:
' 1. ParamArray from another routine    -- lowest-level nested array
' 2. Variant Array as only parameter    -- "  "
' 3. List of values                        -- returns array containing
values given
'                                          (works, but use Array() instead)
' ParamArray Nesting
' * Every time you pass a ParamArray to another routine, it gets nested as
'   element 0 in the new ParamArray:
'
'   Nest Level  Array Structure
'
---------------------------------------------------------------------------
'   1           Array(N)
'   2           Array(0) -> Array(N)
'   3           Array(0) -> Array(0) -> Array(N)
'   4           Array(0) -> Array(0) -> Array(0) -> Array(N)
'
' * This function unwraps the nested Array(0) pointers until the base array
is found.
' * When you pass a standard array to a ParamArray, you start at nest level
2.
'
' Notes:
' * ParamArrays can only be passed to other Routines as ParamArrays
' * Passing ParamArray() to Variant() or Variant will result in an
'   'Invalid Use Of ParamArray' compile error.
' * If an object with a default property is passed, the property is used
instead of the object
'
Public Function UnpackParamArray(ParamArray vParameters() As Variant) As
Variant()

    Dim vOut() As Variant
    Dim vTemp() As Variant
    Dim lUBound As Long

    On Error GoTo HandleErr

    ' empty array: LBound=0; UBound=-1
    UnpackParamArray = VBA.Array()

    vOut() = vParameters()
    lUBound = UBound(vOut)

    ' If UBound > 0, some regular array has been found, so skip this loop
    Do While (lUBound = 0)

        If IsArray(vOut(0)) Then
            ' Swap carefully to avoid fatal error
            vTemp() = vOut(0)
            Erase vOut()
            vOut() = vTemp()
            Erase vTemp()
            ' test the bounds of the new array
            lUBound = UBound(vOut)

        Else
            ' scalar or object value: return array holding it
            lUBound = 1

        End If

    Loop

    If lUBound >= 0 Then
        ' Return
        UnpackParamArray = vOut()
    End If

    Exit Function

HandleErr:
    Err.Raise Err.Number, "UnpackParamArray" & VbCrLf & Err.Source,
Err.Description
End Function


jwcolby:
> I use param arrays to allow me to pass in things like group ids that a user
> might belong to.  The following fails:
>
> Function mPLSUserInGroup(ParamArray Grps() As Variant) As Boolean
>    mPLSUserInGroup = cPLS.cCurrUser.pUserInGrp(**Grps)
> End Function
>
> However the following works:
>
> Function mPLSUserInGroup(ParamArray Grps() As Variant) As Boolean
> Dim lGrps() As Variant
>    lGrps = Grps
>    mPLSUserInGroup = cPLS.cCurrUser.pUserInGrp(**lGrps)
> End Function
>
> Thus in order to push a paramarray into another function we just need to
> dim an array, set it equal to the paramarray and then push that array on
> down.
>
> Pretty strange that a ParamArray cannot be directly passed along.
>