Can I use IsMissing in Case statement?

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,687
Office Version
  1. 365
Platform
  1. Windows
The following code snippet works. The optional parameter p3 can be "ON", "OFF", or omitted (missing).

Code:
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?

Code:
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Here's some code...

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>
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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! :banghead: My mistake. I was trying to simplify the example and forgot to change one variable.

The working code is:
Code:
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:
Code:
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? :confused:
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top