Nesting ParamArrays when declaring Excel VBA functions like SUMIFS?

Consider the following example: Lets say you want to make a function "JoinIfs" that works just like SUMIFS except instead of adding the values in the SumRange, it concatenates the values in "JoinRange". Is there a way to nest the ParamArray as it seems to be done in SUMIFS?

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

I imagine the declaration should look something like this:

Function JoinIfs(JoinRange As Variant, _
                  Delim As String, _
                  IncludeNull As Boolean, _
                  ParamArray CritArray(CriteriaRange As Variant, Criteria As Variant)) As String

But nothing I try seems to compile and there might not be a way to nest ParamArrays. But the existence of functions like SUMIFS and COUNTIFS seems to suggest there might be a way to nest the ParamArrays.

This question duplicates AlexR's question Excel UDF with ParamArray constraint like SUMIFS. But that was posted a few years ago with no response so either the question didn't get enough attention or it was misunderstood.

Edit for clarification: This question is specifically about nesting ParamArrays. I'm not trying to find alternative methods of achieving the outcome of the example above. Imagine nesting ParamArrays on a completely different fictional function like "AverageIfs"

Solution

As per the documentation for the Function statement and Sub statement, a Function or Sub can only contain 1 ParamArray, and it must be the last argument.

However, you can pass an Array as an Argument to a ParamArray. Furthermore, you can then check how many elements are in the ParamArray, and throw an error if it isn't an even number. For example, this demonstration takes a list of Arrays, and which element in that array to take, and outputs another array with the results:

Sub DemonstrateParamArray()
    Dim TestArray As Variant
    TestArray = HasParamArray(Array("First", "Second"), 0)

    MsgBox TestArray(0)

    Dim AnotherArray As Variant

    AnotherArray = Array("Hello", "World")

    TestArray = HasParamArray(AnotherArray, 0, AnotherArray, 1)

    MsgBox Join(TestArray, " ")
End Sub

Function HasParamArray(ParamArray ArgList() As Variant) As Variant
    Dim ArgumentCount As Long, WhichPair As Long, Output() As Variant, WhatElement As Long

    ArgumentCount = 1 + UBound(ArgList) - LBound(ArgList)

    'Only allow Even Numbers!
    If ArgumentCount Mod 2 = 1 Then
        Err.Raise 450 '"Wrong number of arguments or invalid property assignment"
        Exit Function
    End If

    ReDim Output(0 To Int(ArgumentCount / 1) - 1)

    For WhichPair = LBound(ArgList) To ArgumentCount + LBound(ArgList) - 1 Step 2
         WhatElement = ArgumentCount(WhichPair + 1)
        Output(Int(WhichPair / 2)) = ArgumentCount(WhichPair)(WhatElement)
    Next WhichPair

    HasParameterArray = Output
End Function

(A list of built-in error codes for Err.Raise can be found here)