CODEWITHSUNDEEP
excelvbaselectcase-statement
ciso
ciso

Reputation: 3050

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")

Upvotes: 1

Views: 289

Answers (2)

TechnoDabbler
TechnoDabbler

Reputation: 1265

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

Upvotes: 1

ciso
ciso

Reputation: 3050

Found a work around. VBA allows for setting a default value when an optional argument is missing. So this worked:

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

Results as expected:

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

Still would like to know if there is a way to do this on the case statement. Maybe there isn't and this is the correct approach.

Upvotes: 0

Related Questions