Vba excel array of criteria for if functions

Question

I work with the functions workheetfunction.averageifs () and workheetfunction.countifs ().

I have some conditions that determine which criteria to look for, so I would just like to have an array to which new criteria can be added, so instead of a number of cluttered conventions:

If (dep = 0) Then sortspg = True colcount = .CountIfs(column, "<3", badCol, "1") If (colcount > 0) Then colavg = .AverageIfs(column, column, "<3", badCol, "1") insert = True Else insert = False End If Else colcount = .CountIfs(column, "<3", DepColumn, dep, badCol, "1") If colcount > 0 Then colavg = .AverageIfs(column, column, "<3", DepColumn, dep, badCol, "1") insert = True Else insert = False End If End If 

I could just pass an array like:

 CondArray(column => "<3", DepColumn => dep) If colCount > 0 Then CondArray[] = (badCol => "1") 

and then

 .CountIfs(CondArray) .AverageIfs(column, CondArray) 

Answer 1

You can build it using the For...Next contour to customize the formula and Evaluate function.

 Sub Build_Formula() 'http://stackoverflow.com/questions/15317466/vba-excel-array-of-criteria-for-if-functions Dim i As Long, lOutput As Long Dim strTempArr As String Dim CondArray() As Variant Dim StrFormulaBuildUp As String Dim rng As Range 'Modify constant with applicable formula worksheet function Const STRFORMULASTART As String = "CountIfs(" 'Note used this for test data; edit as applicable Set rng = Cells.CurrentRegion 'Build array holding conditions; the way the loop is structured is for 'the "COUNTIF" function; modify as necessary CondArray = Array(rng, "<3") StrFormulaBuildUp = STRFORMULASTART 'Begin loop to build formula For i = LBound(CondArray) To UBound(CondArray) 'Test if value in condition array is a range 'if yes set the range address to a string If TypeName(CondArray(i)) = "Range" Then strTempArr = CStr(CondArray(i).Address) Else 'If condtion, then add quote marks strTempArr = Chr(34) & CStr(CondArray(i)) & Chr(34) End If StrFormulaBuildUp = StrFormulaBuildUp & strTempArr & "," Next i 'Remove extra "," from string and close formula StrFormulaBuildUp = Left(StrFormulaBuildUp, Len(StrFormulaBuildUp) - 1) & ")" 'Determine forumla value lOutput = Evaluate(StrFormulaBuildUp) MsgBox lOutput End Sub