Can I use IsMissing in Case statement?

[JenniferMurphy]

The following code snippet works. The optional parameter p3 can be "ON", "OFF", or omitted (missing).

Public Function Temp(p1, p2, Optional p3 As Variant) As Variant
  . . .
If Not IsMissing(p3) Then
  Select Case UCase(pErrMsgSw)
    Case "ON":  ErrMsgSw = True
    Case "OFF": ErrMsgSw = False
    Case Else
       . . . some error handling . . .
  End Select
End If

But I would like to move the IsMissing test inside the Case statement. This code gets an error, but is something like it possible?

Public Function Temp(p1, p2, Optional p3 As Variant) As Variant
  . . .
Select Case UCase(pErrMsgSw)
  Case Is Missing
  Case "ON":  ErrMsgSw = True
  Case "OFF": ErrMsgSw = False
  Case Else
       . . . some error handling . . .
End Select
End If

 

[tlowry]

Here's some code...

Public Function Temp(p1, p2, Optional p3 As Variant) As Variant    
    Dim ErrMsgSw As Variant
    Select Case True
        Case IsMissing(p3)
             'is missing is true here<missing>
        Case Else
            Select Case UCase(p3)
                Case "ON"
                    ErrMsgSw = True
                Case "OFF"
                    ErrMsgSw = False
                Case Else
                    ErrMsgSw = vbNull
            End Select
    End Select
    Temp = ErrMsgSw
End Function

</missing>

 

[JenniferMurphy]

Here's some code...<missing></missing>

Thanks, but that solution is at least as complicated as mine.

So the answer to my question as to whether there is some simple way to put IsMissing in a Case statement as in my example is "No", right?

 

[DanteAmor]

But in a Selected Case you can only ask for one item and you want to be considered two pErrMsgSw and p3

What do you have in the variable pErrMsgSw?

 

[JenniferMurphy]

But in a Selected Case you can only ask for one item and you want to be considered two pErrMsgSw and p3

What do you have in the variable pErrMsgSw?

Doh! My mistake. I was trying to simplify the example and forgot to change one variable.

The working code is:

Public Function Temp(p1, p2, Optional p3 As Variant) As Variant
   . . .
Dim ErrMsgSw as Boolean
If Not IsMissing(p3) Then
  Select Case UCase(p3)
    Case "ON":  ErrMsgSw = True
    Case "OFF": ErrMsgSw = False
    Case Else
       . . . some error handling . . .
  End Select
End If

And I'd like something like this:

Public Function Temp(p1, p2, Optional p3 As Variant) As Variant
   . . .
Dim ErrMsgSw as Boolean
Select Case UCase(p3)
  Case Is Missing
  Case "ON":  ErrMsgSw = True
  Case "OFF": ErrMsgSw = False
  Case Else
       . . . some error handling . . .
End Select
End If

Is that better?

 

[Tetra201]

Try

Select Case True
    Case IsMissing(p3): ...
    Case UCase(p3) = "ON": ...
    ...