VBA User Defined Function Ifelse(

[xcelljunkie]

Hi Gurus,

I'm somewhat new to vba and I'll bet it won't take more than a minute for one you experts to be able to solve this.
I'm trying to write a udf that will function similar to the iferror function. However, instead of looking for an error, I would like to test if the equation is equal to value x. For example: =iftest(vlookup(a1,B:C,2,0),x,y)
The idea is that i would be able to test if vlookup(a1,B:C,2,0)=x If this is true, then return the result of the vlookup. If false, then return y.

I'm trying to save time and increase readability of a nested ifs by eliminating the need of re-typing/copy and pasting redundant formulas in an already complex equation. For example.

Long way =if(vlookup(a1,B:C,2,0)=x,vlookup(a1,B:C,2,0),y)
Shortway =if(vlookup(a1,B:C,2,0),x,y)

I have made an attempt below, but this doesn't seem to work, and I am not sure where to go from here.

Function Iftest(varFormula, xtest, yvalue)
If varFormula = xtest Then
varFormula
Else
yvalue
End If
End Function

Thanks in advance!

 

[mikerickson]

I think this is it. This version addresses the Error values and "100"<"23" issues that post 9 has.

Function IfTest(ByVal formulaValue As Variant, ByVal testCriteria As Variant, Replacement As Variant) As Variant
    Dim qt As String:   qt = Chr(34)
    Dim strCompSymbol As String, strCValue As String, strFValue As String
    
    testCriteria = CStr(testCriteria)
    
    If (Left(testCriteria, 2) = "<>") Or (Left(testCriteria, 2) = "<=") Or (Left(testCriteria, 2) = ">=") Then
        strCompSymbol = Left(testCriteria, 2)
        strCValue = Mid(testCriteria, 3)
    ElseIf testCriteria Like "[<>=]*" Then
        strCompSymbol = Left(testCriteria, 1)
        strCValue = Mid(testCriteria, 2)
    Else
        strCompSymbol = "="
        strCValue = testCriteria
    End If
    
    If IsNumeric(strCValue) Then
        strCValue = CStr(Val(strCValue))
    Else
        strCValue = qt & strCValue & qt
    End If
    
    strFValue = CStr(formulaValue)
    If IsNumeric(strFValue) Then
        strFValue = CStr(Val(strFValue))
    Else
        strFValue = qt & strFValue & qt
    End If
    
    
    If Evaluate(strFValue & strCompSymbol & strCValue) Then
        IfTest = Replacement
    Else
        IfTest = formulaValue
    End If
End Function