excel vba - VBA UDF ParamArray Sequence -

i want create function sumifs, i'm having hard time figuring our how handle paramarray portion. i'm looking solution allows same range1,criteria1,range2,criteria2,...,rangen,criterian sum ifs in "sumifscontains" function. i've attached code singular case, "sumifcontains" can see starting point:

function sumifcontains(phraserange range, criteria string, sumrange range) dim element range  elementcount = 0 each element in phraserange     elementcount = elementcount + 1 next element  dim sumarray: redim sumarray(1 3, 1 elementcount)  elementcount = 0 each element in sumrange     elementcount = elementcount + 1     sumarray(2, elementcount) = element next element  elementcount = 0 each element in phraserange     elementcount = elementcount + 1     sumarray(1, elementcount) = element     if instring(cstr(element), criteria)         sumarray(3, elementcount) = sumarray(2, elementcount)     else         sumarray(3, elementcount) = 0     end if next element  sumifcontains = 0 item = 1 elementcount     sumifcontains = sumifcontains + cdbl(sumarray(3, item)) next item  end function 

before got answer last night came working option follows:

function sumifscontains(sumrange range, paramarray criteria() variant) dim element range dim ccriteria string dim phraserange range  'exit function dim phraserangearray(): redim phraserangearray(lbound(criteria()) (((ubound(criteria()) + 1) / 2) - 1)) dim criteriaarray(): redim criteriaarray(lbound(criteria()) (((ubound(criteria()) + 1) / 2) - 1))  currentpair = 0 = lbound(criteria()) ubound(criteria())     if mod 2 = 0         phraserangearray(currentpair) = criteria(i)     else         criteriaarray(currentpair) = criteria(i)         currentpair = currentpair + 1     end if next  elementcount = ubound(phraserangearray(0)) dim sumrng: redim sumrng(1 elementcount) = 1 each element in sumrange     sumrng(i) = element     = + 1 next element dim sumarray: redim sumarray(0 2 + ubound(phraserangearray), 1 elementcount)  = 1 elementcount     sumarray(1, i) = sumrng(i)     rc = 2 2 + ubound(phraserangearray)         if instring(cstr(phraserangearray(rc - 2)(i, 1)), cstr(criteriaarray(rc - 2)))             sumarray(rc, i) = 1         else             sumarray(rc, i) = 0         end if     next rc     sumarray(0, i) = sumarray(1, i)     mult = 2 2 + ubound(phraserangearray)         sumarray(0, i) = sumarray(0, i) * sumarray(mult, i)     next mult next   sumifscontains = 0 item = 1 elementcount     sumifscontains = sumifscontains + cdbl(sumarray(0, item)) next item  end function 

but i'm still curious how make range/criteria pair not parced out of "criteria" array later.

if understand correctly you're trying do, need iterate on paramarray step 2. add test make sure passed parameters come in pairs, grab them set of criteria , sumrange in loop:

public function pairedparamarrayie(phraserange range, paramarray values())      dim counter integer     dim criteria string     dim sumrange range      if ubound(values) mod 2 <> 1         err.raise -1, vbnullstring, "invalid paramarray"     end if      counter = lbound(values) + 1 ubound(values) step 2         criteria = values(counter - 1)         set sumrange = values(counter)         debug.print criteria         debug.print sumrange.addresslocal     next counter  end function