vba excel if函数的条件数组

Question

我正在使用 worksheetfunction.averageifs() 和 worksheetfunction.countifs() 函数。

我有一些条件语句指定应查找的条件，因此我只想有一个可以添加新条件的数组，这样就可以代替一系列困惑的条件语句:

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

我可以传递一个数组，例如:

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

然后

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

Answer 1

您可以使用 For...Next 循环来设置公式和 Evaluate 函数来构建它。

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