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

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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!

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.

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