VBA Function use of `isMissing()` in conditional statement

userMOkn 0
Viewed 30

My code is as follows:

Function myFunc(Optional i) As Integer
    If IsMissing(i) Or i = 1 Then
        myFunc = 1
    Else
        myFunc = 0
    End If
End Function

It returns #VALUE! if the function is called without parameter myFunc().

Because of that I have to use the more complicated code:

Function myFuncWorking(Optional i) As Integer
    If Not IsMissing(i) Then
        If i = 1 Then
            myFuncWorking = 0
        End If
    Else
        myFuncWorking = 1
    End If
End Function

Is it possible to overcome this issue?

vba excel
2 Answers

VBA will evaluate both parts of your If-Statement: If IsMissing(i) Or i = 1 Then will check for IsMissing(i) and for i = 1.

If you now omit the parameter, the first part of the statement evaluates to True (and therefore the whole expression will be true because true OR anything will result in true no matter what anything is). However, VBA will try to evaluate i = 1 anyhow and this gives you a type mismatch (because i has the value missing which cannot be compared with 1).

When you use a function as UDF and it has an (unhandeled) runtime error, Excel will display #VALUE!.

In your case, the only alternative to the "more complicated" form is to assign a default value to i when declaring the parameter:

Function myFunc(Optional i = 1) As Integer
    If i = 1 Then
        myFunc = 1
    Else
        myFunc = 0
    End If
End Function
FunThomas0

I'm not sure why that's really an issue, but you could do this:

Function myFunc(Optional i As Integer = 1) As Integer
    If i = 1 Then
        myFunc = 1
    Else
        myFunc = 0
    End If
End Function
uservF9f0
Related