[SOLVED] how to use missing and boolean values in the same case statement in excel vba

how to use missing and boolean values in the same case statement in excel vba

In VBA (MS Excel 2016): How to combine missing and boolean values in a select case statement? The argument val could be passed as a boolean or not passed at all, or passed as something else unexpected.

public sub test(optional val as variant)
  select case val
    case true, isMissing(val): msgbox("foo")
    case else: msgBox("bar")
  end select
end sub

Above results in run-time error '13': Type mismatch on the "case true, isMissing(val):" line.

The preference is to use the same case statement allowing for multiple values to result in showing msgbox("foo"). Also would prefer not to set a default value for the optional argument on the function definition.

Each of these calls should work:

call test        // expect msgBox("foo")
call test("abc") // expect msgBox("bar")
call test(true)  // expect msgBox("foo")

Solution

While not very elegant, another option might be:

Public Sub test(Optional val As Variant)
  Select Case IIf(IsMissing(val), True, val)
    Case True: MsgBox ("foo")
    Case Else: MsgBox ("bar")
  End Select
End Sub