Select Case Statement

[Glory]

    Select Case Err.Number
        Case Is <> 0 And Not 3704
 
        'Code

This case statement executes even when the second condition is met (err.number = 3704). How do I need to write it to make sure it won't execute when the error number is 3704?

 

[cgcamal]

Hi Glory,

Try with:

 Case [COLOR=Navy]Not [/COLOR]3704, [COLOR=Navy]Not [/COLOR]0

Regards

 

[Glory]

This is very, very annoying. The messagebox appears. Why? Is it not possible to use And in a Select Case statement?

Public Sub test()
a = 1
Select Case a
    Case Is > 0 And Not 1
        MsgBox "Hey"
 
End Select
 
End Sub

Edit: Thank you. I guess I just don't understand it well enough yet.

 

[Marcelo Branco]

Glory,

A possible work-around

Select Case Err.Number
Case 1 To 3703, 3705 To 100000
'Code

HTH

M.

 

[Ruddles]

This is very, very annoying. The messagebox appears. Why? Is it not possible to use And in a Select Case statement?

Public Sub test()
a = 1
Select Case a
    Case Is > 0 And Not 1
        MsgBox "Hey"
 
End Select
 
End Sub

It is possible but your syntax is incorrect. When you write Case Is > 0 And Not 1 you're saying if a is greater than (zero And Not 1). VBA evaluates the expression (zero And Not 1) before it performs the comparison in exactly the same way as you'd expect If a > b+1 to evaluate b+1 before it compared the resulting value to a.

In your code (1) evaluates to TRUE so (Not 1) evaluates to FALSE, so (zero And Not 1) - in other words (zero And FALSE) evaluates to FALSE (or zero). VBA says "is a > FALSE?" and the answer is "yes" because a=1 and FALSE=0.

This would be one way to do it:-

 Select Case TRUE
    Case a > 0 And a <> 1

You have to be very careful what you tell VBA to do because it will do exactly what you tell it rather than what you think you meant to tell it!