How to define a Paramarray( ) with two arguments

**Debraj Roy** · 12-08-2015, 10:58 AM

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..