Why does my function assume a missing argument is there?

Question

I have a function which updates a form, "LoadingInterface". The function looks like this:

Private Sub updateLoadingBar(Optional tekst As String, Optional barOnePerc As Long, Optional barTwoPerc As Long)
    If Not IsMissing(tekst) Then
        LoadingInterface.Label1.Caption = tekst
    End If
    If Not IsMissing(barOnePerc) Then
        LoadingInterface.Bar.Width = barOnePerc * 1.68
        LoadingInterface.prosent.Caption = barOnePerc & "%"
        LoadingInterface.prosent.Left = barOnePerc * 1.68 / 2 - 6
    End If
    If Not IsMissing(barTwoPerc) Then
        LoadingInterface.SubBar.Width = barTwoPerc * 1.68
    End If
    LoadingInterface.Repaint
End Sub

I then call the function like this, expecting it to only update the textfield, since the other two arguments are missing.

Call updateLoadingBar(tekst:="Test")

This works fine for updating Label1, but unfortunately the other two values are updated too - it seems that not including any values in the function-call makes VBA assume the two variables values are 0. What's more, it appears that the IsMissing function does not detect that the two values are missing when the function is called, which is the bigger problem. Stepping through the code using F8 confirms that all the if-statements are indeed entered.

Is there any way to make the code skip the two lowermost if-statements in my function, if no values are provided for the parameters barOnePerc and barTwoPerc?

Answer 1

IsMissing only works if the argument is declared as a Variant.

I don't think you can validly distinguish between 0 and no passed parameter for the Long. In that case you would need to declare as Variant in the signature. You can later cast if required.

I guess you could put a default (unlikely number) and test for that. Note: I wouldn't advise this. This just screams "Bug".

IsMissing:

IsMissing returns a Boolean value indicating whether an optional Variant argument has been passed to a procedure.

Syntax: IsMissing(argname)

The required argname argument contains the name of an optional Variant procedure argument.

Remarks: Use the IsMissing function to detect whether or not optional Variant arguments have been provided in calling a procedure. IsMissing returns True if no value has been passed for the specified argument; otherwise, it returns False.

Both methods:

Option Explicit

Public Sub Test()
    RetVal
    RetVal2
End Sub

Public Function RetVal(Optional ByVal num As Long = 1000000) As Long

    If num = 1000000 Then

        MsgBox "No value passed"
        RetVal = num
    Else

        MsgBox "Value passed " & num
        RetVal = num
    End If

End Function


Public Function RetVal2(Optional ByVal num As Variant) As Long

    If IsMissing(num) Then

        MsgBox "No value passed"

    Else

        MsgBox "Value passed " & num
        RetVal2 = CLng(num)
    End If

End Function