How to use comparison operators in VBA Excel UDF arguments?

Question

How would i write UDF so they accept comparison operators in arguments?

When using standard functions you write like this =countif(range;x) and you would get the number of cells in the range that equals x.

Replicating this function in VBA would look something like this:

Function countifUDF(rng As Range, x As Integer)
    count = 0
    For Each cell in rng.Cells
        If cell.Value = x Then
            count = count + 1
        Next cell
    countifUDF = sum
End Function

When using the standard function you can pass a comparison operator to the function like this =countif(range;"<"&x) and you would get the number of cells in the range less than x.

How could i do this in an UDF? My UDF as =countifUDF(range;"<"&x) yields #VALUE

---

SOLUTION

Function countifUDF(rng As Range, x As String)
Dim arr() As String
Dim count As Integer
Dim i As Integer

' breaking down x character by character and puts in array
ReDim arr(Len(x) - 1)
For i = 1 To Len(x)
    arr(i - 1) = Mid$(x, i, 1)
Next

' if the last character in x is not numeric i assume the user want to count matching strings
' Like allows the user to use wildcards, LCase makes the comparision case insensitive
If IsNumeric(arr(UBound(arr))) = False Then
    x = LCase(x)

    For Each cell In rng.Cells
        If LCase(cell.Value) Like x Then
            count = count + 1
        End If
    Next cell

' if the first char in x is numeric its pretty straight forward
ElseIf IsNumeric(arr(0)) = True Then
    For Each cell In rng.Cells
        If cell.Value = x Then
            count = count + 1
        End If
    Next cell

' if the first character in x is < and the second is numeric less-than operator is used
ElseIf arr(0) = "<" And IsNumeric(arr(1)) = True Then
    ' removing < from x
    x = Replace(x, "<", "")
    For Each cell In rng.Cells
        If cell.Value < x Then
            count = count + 1
        End If
    Next cell

ElseIf arr(0) = ">" And IsNumeric(arr(1)) = True Then
    x = Replace(x, ">", "")
    For Each cell In rng.Cells
        If cell.Value > x Then
            count = count + 1
        End If
    Next cell

' if the first char is < and the second is > the is not operator is used
ElseIf arr(0) = "<" And arr(1) = ">" Then
    x = Replace(x, "<", "")
    x = Replace(x, ">", "")
    For Each cell In rng.Cells
        If cell.Value <> x Then
            count = count + 1
        End If
    Next cell

ElseIf arr(0) = ">" And arr(1) = "=" Then
    x = Replace(x, ">", "")
    x = Replace(x, "=", "")
    For Each cell In rng.Cells
        If cell.Value >= x Then
            count = count + 1
        End If
    Next cell

ElseIf arr(0) = "<" And arr(1) = "=" Then
    x = Replace(x, "<", "")
    x = Replace(x, "=", "")
    For Each cell In rng.Cells
        If cell.Value <= x Then
            count = count + 1
        End If
    Next cell

End If

countifUDF = count

End Function

Given the answers i got it seems like theres no convenient way in VBA of handling comparison operators in UDF, please correct me if i'm wrong. My solution supports both numbers and strings with wildcards. At first i tried to use the Split-method with & as delimiter. Appearantly VBA identifies '">"&x' as '>x' why i had to split x character by character and evaluate what kind of comparison operator the user typed in.

Answer 1

Have the UDF() consider the second argument as a String:

Function countifUDF(rng As Range, x As Variant) As Long
    Dim cell As Range, Count As Long, CH As String, VL As Long

    VL = Replace(Replace(x, ">", ""), "<", "")
    CH = Left(CStr(x), 1)
    Count = 0

    If CH = ">" Then
        For Each cell In rng.Cells
            If cell.Value > VL Then
                Count = Count + 1
            End If
        Next cell
    ElseIf CH = "<" Then
        For Each cell In rng.Cells
            If cell.Value < VL Then
                Count = Count + 1
            End If
        Next cell
    Else
        For Each cell In rng.Cells
            If cell.Value = x Then
                Count = Count + 1
            End If
        Next cell
    End If
    countifUDF = Count
End Function

In this example, CH is the first character of the second argument and VL is the numeric part of the second argument.