IFNEGATIVE function

[JeanM01]

Hi everyone,

I am looking to write a VBA defined function that is similar to IFERROR(Action, what to do if error) instead mines would be
IFNEGATIVE( Action, what to do if negative). If I could create this, it would cut down on the number of IF's I have for a formula.


Thanks in advance,
Jean

 

[Jonmo1]

If I could create this, it would cut down on the number of IF's I have for a formula.

Yes and No..

Yes, it would reduce the IF's in your formulas.
However, the IF would still exist, they're just being done by VBA instead of Formula.
So the same amount of work is being done, you've just delegated some of it to VBA instead of your formulas.
And VBA is NOT Faster than Formula.
UDF's actually tend to be slower than formulas.

But anyway, this will do what you're asking

Public Function IfNeg(c As Range, rslt As Variant)
If c.Value < 0 Then
    IfNeg = rslt
Else
    IfNeg = c.Value
End If
End Function

used like
=IFNEG(A1,"It's Negative")

 

[Smitty]

Welcome to the Board!

What formula are you using now? As for evaluating negative, you can use something like

If Target.Value < 0 Then

HTH,

 

[JeanM01]

I am writing a code like this: If(Cell1*Cell2 + Cell3<0, Cell4, Cell1*Cell2+Cell3) It gets more complicated as I am doing a case basis.

 

[Smitty]

It'll help if you post an example of your data and your expected outcome.

 

[Jonmo1]

Probably should change
c As Range
to
c As Variant
Since your doing a calcuation in the formula, not just referencing a cell.

Public Function IfNeg(c As Range, rslt As Variant)
If c.Value < 0 Then
    IfNeg = rslt
Else
    IfNeg = c.Value
End If
End Function

 

[JeanM01]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Value[/TD]
[TD]Mean[/TD]
[TD]Slope1[/TD]
[TD]Slope2[/TD]
[TD]Intercept[/TD]
[TD]Estimate[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]400[/TD]
[TD]250[/TD]
[TD]2[/TD]
[TD]-3[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
So right now my formula for F5 is

IF(E5 + D5*A5+C5*A4<0, B2 ,E5 + D5*A5+C5*A4)

which seems like an awful amount to write versus IFNEGATIVE(E5 + D5*A5+C5*A4, B5)

 

[Marcelo Branco]

Not sure if this helps, but maybe...

Function IfNegative(s1 As String, s2 As String) As Variant
    Application.Volatile
    If Evaluate(s1) < 0 Then
        IfNegative = Evaluate(s2)
    Else
        IfNegative = Evaluate(s1)
    End If
End Function

Usage (enter the formulas as strings)
=IfNegative("=E5+D5*A5+C5*A4", "=B2")

Or if you want a message when negative
=IfNegative("=E5+D5*A5+C5*A4", """message""")

M.