How to define a Paramarray( ) with two arguments

Hi Forum,

I am planning to create a ParamArray with dual argument.. Dual argument means.. always 2/4/6.. parameter need to specify..
if you look at SUMIFS function -- CriteriaRange1, Criteria1 and CriteriaRange2, Criteria2 are always in a set..
Currently I am managing it with lbound to ubound step 2 method.. but I have strong believe, there must be some method to define dual/triple paramarray defining option..

Function fCreateSQLString(ByVal strTableName As String, ParamArray varColumnName() As Variant) As String
    
    Dim intCounter          As Integer
    Dim strSQL              As String
    
    strSQL = "SELECT * FROM " & strTableName & " where "
    For intCounter = LBound(varColumnName) To UBound(varColumnName) Step 2
        strSQL = strSQL & varColumnName(intCounter) & " = """ & varColumnName(intCounter + 1) & """" & vbNewLine
    Next intCounter
    
    fCreateSQLString = strSQL
    
End Function

Formula:

fCreateSQLString("ExcelForum","UserName","Debraj","Section","VBA","Solved","No")

I am just worried about that red section, how to declare it something like..

ParamArray {varColumnName(),varWhereClause()} As Variant

If you will try SumIfs function like =SUMIFS(E11:E20,F11:F20,D8,G11:G20) [last criteria parameter missing]
excel will through error..

So the query is "How to declare paramarray with dual argument"..

any Input will be appreciated..

Hi,

I think you are stuck with the 'Step 2'.

Per the following article:

However, since the ParamArray accommodates an arbitrary number of arguments as defined by the caller, it must be the last of the arguments.

http://www.tushar-mehta.com/publish_...ramArray.shtml

This implies to me that there can only be one ParamArray in the function. You should probably generate an error in your function if the ParamArray input has the wrong number of arguments.

ParamArray varColumnName() As Variant is the only syntax I've ever seen. Since 'Variant' is the default type 'as Variant' can be omitted, but I always include it anyway.

Lewis

Originally Posted by Debraj Roy

Currently I am managing it with lbound to ubound step 2 method.. but I have strong believe, there must be some method to define dual/triple paramarray defining option..

I agree with Lewis; there is no dual-paramarray. Just use the one ParamArray and test if it has an even number of elements (in your case) for the 1st dimension.

Public Function fCreateSQLString(ByVal strTableName As String, ParamArray varColumnName() As Variant) As String
    
    Dim intCounter          As Integer
    Dim strSQL              As String
    
    If UBound(varColumnName) Mod 2 = 0 Then
        MsgBox "You've entered too few arguments for this function. ", vbExclamation, "Missing Argument: fCreateSQLString"
    Else
    
        strSQL = "SELECT * FROM " & strTableName & " where "
        For intCounter = LBound(varColumnName) To UBound(varColumnName) Step 2
            strSQL = strSQL & varColumnName(intCounter) & " = """ & varColumnName(intCounter + 1) & """" & vbNewLine
        Next intCounter
        
        fCreateSQLString = strSQL
    End If
    
End Function

Hi Lewis and AlphaFrog,

Thanks for your input,
however, if you look at SUMIFS function,
ParamArray.png

its showing two parameter after entering param argument section, so is this something to play in RegisterUDF section.. or this tooltip is designed is this way and working with single paramArray() only!

Originally Posted by Debraj Roy

this tooltip is designed is this way and working with single paramArray() only!

I think I got your point.. Thanks AlphaFrog ..

I can't speak to the actual code for SUMIFS, but do I know the tool tip text for a function is just text that can be anything; it isn't defined by the function's argument(s). If you do a web search for something like Excel UDF tooltip, there are several guides on how to make your own.