Creating a function called IFTRUE that behaves as IFERROR but for a function returning TRUE. Worsheetfunction.if doesn't work. What am I doing wrong?

Question

I wanted to create a custom function that would be a shorthand for if(somefunction(arg)=something,"sometext",somefunction(arg)) So I don't have to duplicate somefunction(arg) each time I do this, just like how iferror lets us do away with if(iserror(somefunction(arg)),"sometext",somefunction(arg)

For example, I want to be able to type iftrue(somefunction(arg),"=A1","message") and that to be equivalent to if(sumfunction(arg)=A1,"message",sumfunction(arg))

I tried:

Function iftrue(Fx, condition, show)
    iftrue = Application.WorksheetFunction.if(Fx & condition, show, Fx)
End Function

But it gives #value.

---

To diagnose my problem, I tried some simpler functions, to see where I was going wrong. So I duplicated the SUM and If functions.

This "sum" function works.

Function testsum(a, b)
    test = Application.WorksheetFunction.Sum(a, b)
End Function

But this "if" function doesn't work.

Function testif(a, b, c)
    testif = Application.WorksheetFunction.if(a, b, c)
End Function

So I think my problem is the way I'm invoking the worksheet.function.if.

I know I could just get around this by using VBA ifs instead but that's not really what I want to do.

Answer 1

There is no Application.WorksheetFunction.If()

Even if there was you would still need to push extra quotes into the test part of the if. for example if the fx resolved to "test" and condition was "=test" the resultant string would be "test = test"

Put that

So use Evaluate instead.

We need to parse the string in a certain format for Evaluate.

we need to push extra quotes into the resultant string. for example if the fx resolved to "test" and condition was "=test" the resultant string would be "test = test".

Putting this in Evaluate, the function would look for a function named test. So we need a string that is like ""test"="test"", which will resolve to True.

If the condition was always an equality and never an inequality we could simply use IF fx = condition then in place of everything up to and including If tst Then.

This function is more dynamic than that as it allows inequalities:

Function IFTrue(fx, condition As String, show)
Dim tst As Boolean
Dim z As Integer
Dim t As String
'test whether the condition is assuming "="
If InStr("<>=", Left(condition, 1)) = 0 Then condition = "=" & condition
'Find whether there is one or two qulifiers
If InStr("<>=", Mid(condition, 2, 1)) > 0 Then z = 2 Else z = 1
'Parse string to remove qulifiers from the quotes and make everything a string
t = """" & fx & """" & Left(condition, z) & """" & Mid(condition, z + 1) & """"
'evaluate the formula string to resolve to True or False
tst = Application.Caller.Parent.Evaluate(t)

If tst Then
    IFTrue = show
Else
    IFTrue = fx
End If
End Function

You would then call it like this

=IFtrue(SUM(A1,A2),"=A3","Must Be True")

---

Edit

You can use the IIF() and reduce the number of lines

Function IFTrue2(fx, condition As String, show)
Dim z As Integer

'test whether the condition is assuming "="
If InStr("<>=", Left(condition, 1)) = 0 Then condition = "=" & condition
'Find whether there is one or two qulifiers
If InStr("<>=", Mid(condition, 2, 1)) > 0 Then z = 2 Else z = 1

IFTrue2 = IIf(Application.Caller.Parent.Evaluate("""" & fx & """" & Left(condition, z) & """" & Mid(condition, z + 1) & """"), show, fx)

End Function