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!

 

[davesweep]

Ideally you would include error checking and variable definition, but in essence:

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

Iftest = output
End Function

 

[mikerickson]

CStr will allow for testing of error values, as in

=IfTest(MATCH("cat",A:A,0), NA(), "not there")

Function IfTest(formulaValue As Variant, testAgainst As Variant, Replacement As Variant) As Variant
    If CStr(formulaValue) = CStr(testAgainst) Then
        IfTest = Replacement
    Else
        IfTest = formulaValue
    End If
End Function

 

[xcelljunkie]

Both of your solutions were helpfull in understanding what I was missing with my original code. I've replaced my code with mikericksons, but I'm stuck again. How do I adjust the code so that I can use an argument as my testAgainst variant? Sorry, I should have clarified in my initial post.

For example: =Iftest(VLOOKUP(A1,$A$3:$B$5,2,0),">"&B3,0)

Thank you both for the quick responses! You are both awesome!

 

[mikerickson]

For that formula, I wouldn't use IfTest, I'd use

=IF(VLOOKUP(A1,$A$3:$B$5,2,0)>B3, VLOOKUP(A1,$A$3:$B$5,2,0), 0)

 

[xcelljunkie]

Thats what I currently do, I'm just trying to increase readability of more heavily nested formulas.

Thanks for your help!

 

[mikerickson]

This version won't test if a result is an error value, but it won't blowup if the formulaResult is an error value.

Function IfTest(formulaValue As Variant, testCriteria As String, Replacement As Variant) As Variant

    If Not (testCriteria Like "[<>=]*") Then testCriteria = "=" & testCriteria
    
    If Evaluate(CStr(formulaValue) & testCriteria) Then
        IfTest = Replacement
    Else
        IfTest = formulaValue
    End If
End Function

The formula would be just what you wrote above. TestCriteria is like the criteria for CountIf. (= implied if not present)
=IfTest(VLOOKUP(A1,$A$3:$B$5,2,0),">"&B3,0)

 

[xcelljunkie]

Brilliant! Thanks mikerickson!

 

[mikerickson]

This addresses some brittleness in the previous version.

Function IfTest(ByVal formulaValue As Variant, ByVal testCriteria As String, Replacement As Variant) As Variant
    If Not (testCriteria Like "[<>=]*") Then 
        testCriteria = "=" & Chr(34) & CStr(testCriteria) & Chr(34)
    End If
    
    If Evaluate(Chr(34) & CStr(formulaValue) & Chr(34) & testCriteria) Then
        IfTest = Replacement
    Else
        IfTest = formulaValue
    End If
End Function

 

[xcelljunkie]

mikerickson is the man!

This is great! Thanks!