Missing ParamArray in a UDF

Question

I'm trying to determine when a user defined function (UDF) using a ParamArray has been used on the worksheet with no ParamArray parameter(s) supplied.

I've tried the IsEmpty, IsArray, IsError, comparing to nothing, comparing to a zero-length string, etc.

I'm aware that a paramarray is always byVal and that the variant array is always zero-based, but if it doesn't exist, how is it an array?

• comparing the first element of the array to Nothing or Not Nothing throws an error
• IsEmpty reports False
• IsArray oddly reports True
• IsError reports False for both tests ans tests(0)

testParams UDF:

Public Function testParams(ByRef ndx As Long, ParamArray tests())
    Select Case ndx
        Case 1
            testParams = CBool(tests(LBound(tests)) Is Nothing)
        Case 2
            testParams = IsEmpty(tests)
        Case 3
            testParams = IsArray(tests)
        Case 4
            testParams = IsError(tests)
        Case 5
            testParams = CBool(tests(LBound(tests)) = vbNullString)
    End Select
End Function

In the following example, I am simply returning to the worksheet what trying to access the ParamArray returns.

How can I determine if the user has not provided the ParamArray parameter(s) in a real-world UDF that requires them? I would prefer a simple boolean check vs. testing against testing whether something is nothing.

Answer 1

An excellent explanation of Array Allocation is at http://www.cpearson.com/Excel/IsArrayAllocated.aspx

Excert:

The function below, IsArrayAllocated will accurately return True or False indicating whether the array is allocated. This function will work for both static and dynamic arrays of any number of dimensions, and will correctly work for unallocated arrays with valid (non-error-causing) LBound values

And the function

Function IsArrayAllocated(Arr() As Variant) As Boolean
    On Error Resume Next
    IsArrayAllocated = IsArray(Arr) And _
        Not IsError(LBound(Arr, 1)) And _
        LBound(Arr, 1) <= UBound(Arr, 1)
End Function

Using it with a Functionwith a Parameter Array

Function MyFunc(ParamArray pa()) As Variant
    Dim v()
    v = pa
    If IsArrayAllocated(v) Then
        'Do stuff with the parameters
    Else
        'There are no parameters...
    End If
End Function

Answer 2

_{TL;DR - use if IsMissing(tests) Then ...}

NTL&WR:

Even though a ParamArray cannot be used together with any declared Optional parameter (including the ParamArray itself), no error is thrown if it is omitted. It could easily be stated that a ParamArray is always optional although it cannot be labelled as such.

^{From: Parameter Arrays (Visual Basic)}
_{The parameter array is automatically optional. Its default value is an empty one-dimensional array of the parameter array's element type.}

A ParamArray that the user did not provide will be a variant array that was instantiated but neither populated nor positively dimensioned.

It will be defined with an LBound of 0 (zero) and a UBound of -1 (minus one). This is the same as any other variant array declared but not dimensioned.

Since it is always a variant type parameter, it will also correctly report its presence or absence with IsMissing as well.

Public Function testParams(ByRef ndx As Long, ParamArray tests())
    Select Case ndx
        Case 1
            testParams = CBool(tests(LBound(tests)) Is Nothing)
        Case 2
            testParams = IsEmpty(tests)
        Case 3
            testParams = IsArray(tests)
        Case 4
            testParams = IsError(tests)
        Case 5
            testParams = CBool(tests(LBound(tests)) = vbNullString)
        Case 6
            testParams = CBool(UBound(tests) = -1)
        Case 7
            testParams = IsMissing(tests)
    End Select
End Function

Testing for either IsMissing or a UBound value of -1 will determine if the user has provided a paramarray.