CODEWITHSUNDEEP
excelvba
Shevy
Shevy

Reputation: 57

How can I add an if clause in a [vba] custom function

I wrote this function

Function potential(Volume, Original, Better)
potential = (Original - Better) * Volume
End Function

Its pretty basic but what i really need is in the case when Better is blank the answer should be 0

I tried potential = Application.WorksheetFunction.If(Better = 0, 0, (Original - Better) * Volume) result is always an error

or potential = ((Original - Better) * Volume) * (Better / Better) only gave an error when Better is blank

So I tried potential = Application.WorksheetFunction.IfError(((Original - Better) * Volume) * (Better / Better), 0) with the same results

What am I doing wrong here?

Upvotes: 0

Views: 200

Answers (1)

Scott Craner
Scott Craner

Reputation: 152535

Use:

Function potential(ByVal Volume As Double, ByVal Original As Double, Optional ByVal Better As Double = 0) As Double
    If Better = 0 Then Exit Function
    potential = (Original - Better) * Volume
End Function

Upvotes: 2

Related Questions