excelvbanestedsumifsparamarray

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)