Custom Function in VBA - Error Message

Question

I would consider myself a pretty experienced excel user, but I don't know anything about VBA. I am trying to create a simple function below

Function IFZERO(number As Double, new_value)

    If (number = 0) Then

        new_value

    Else

        number

    End If

End Function

But I keep getting the following error message

"Compile error: Expected Sub, Function, or Property"

Does anyone know why I am getting this? Debugging doesn't help me.

Answer 1

The problem is that #N/A is an error value. This blows up the function. To get around this, test for an error first:

Function IFZERO(number, new_value)

    If IsError(number) Then

        IFZERO = new_value

    ElseIf number = 0 Then

        IFZERO = new_value

    Else

        IFZERO = number

    End If

End Function

Answer 2

Thanks! That's what I was expecting, but what tripped me up is that #N/As still give me a #VALUE! error. I actually removed the "As Double" from the formula because I want this formula to really work for all date types -- i.e., in all cases, if the input isn't the number zero, then return the input (number, string, or otherwise), else if the input is the number 0, return what you tell it to return. How to I make sure the input #N/A still returns #N/A because #N/A does not equal 0?

Answer 3

How are you using the function? If you want to use it in a cell formula, that formula should look like the following examples:

=IFZERO(A1,"tadaa")

If A1 contains a non-zero number, the formula will return that number.

If A1 contains 0, the formula will return the text string "tadaa" (without quotes).

If A1 contains a non-numeric value, the formula will return #VALUE!

=IFZERO(B1,C1)

If B1 contains a non-zero number, the formula will return that number.

If B1 contains zero, the formula will return the value of C1.

If B1 contains a non-numeric value, the formula will return #VALUE!

Answer 4

Thanks! I'm no longer receiving the  "Compile error: Expected Sub, Function, or Property" message, but I am still receiving a #VALUE error. Any idea why?

Answer 5

You have to assign a value to the function name:

Function IFZERO(number As Double, new_value)

    If number = 0 Then

        IFZERO = new_value

    Else

        IFZERO = number

    End If

End Function