VBA Select Case number to number greater than and less than

Question

Sub ss()
  Dim a As Double
  a = 6.99999999
  Select Case a
    Case 0 To 7:
      MsgBox "ok"
    Case Else:
      MsgBox "no"
  End Select
End Sub

The Case 0 to 7 results in a check for a >= 0 and a <= 7. But what I want is a >= 0 and a < 7.

I also tried Case Is >=0, Is < 7.

How can I do this in a Select Case?

a = 6.99999999 should result in "ok"
a = 7 should result in "no"

"You can use multiple expressions or ranges in each Case clause. For example, the following line is valid. `Case 1 To 4, 7 To 9, 11, 13, Is > maxNumber` " [Select...Case Statement (Visual Basic) MSDN](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/select-case-statement) — dadler, Feb 05 '18 at 15:46

score 3 · Accepted Answer · answered Feb 05 '18 at 15:45

3

select case true
  case a >= 0 and a < 7
    MsgBox "ok"
  case else
    MsgBox "no"
end select

But, unless you have more than two conditions, I would suggest you use an If instead.

answered Feb 05 '18 at 15:45

GSerg

76,472
17
159
346

3

This defeats the purpose of `Select Case` all together. `Select Case True` is just a dummy wrapper here. – trincot Feb 05 '18 at 16:03
1

@trincot To me `select case true`is a [more readable alternative](https://stackoverflow.com/a/794091/11683) to a series of `ElseIf`. I [do not think](https://stackoverflow.com/a/6540327/11683) it defeats anything. – GSerg Feb 05 '18 at 16:07

J_P · Answer 2 · 2018-02-05T15:47:45.880

1

Sub ss()
  Dim a
  a = 7
  Select Case a
    Case  7:
      MsgBox "no"
    Case 0 To 7:
      MsgBox "ok"
    Case Else:
      MsgBox "no"
  End Select
End Sub

edited Feb 05 '18 at 15:47

answered Feb 05 '18 at 15:37

J_P

761
8
17

what about if a = 6.999? – johnn Feb 05 '18 at 15:41
Then you need to use case 0 to 7, and before that another case 7 with answer "no" – J_P Feb 05 '18 at 15:42
6.99999999 should be "ok" and 7 should be "no" – johnn Feb 05 '18 at 15:45
1

Yes, that's why if you add case 7: msgBox "no", and afterwards case 0 to 7: MsgBox "ok" should be what you are looking for. Just edited my answer to deal with this situation, as at first I thought we were dealing with integers only. Anyway instead of select you could use if... – J_P Feb 05 '18 at 15:46
1

This works well for small amount of conditions, but will quickly turn into an unmanageable mess if you add more. – GSerg Feb 05 '18 at 15:50
1

@johnn then you need a better [mcve], because your current use case doesn't justify a `Select Case` structure. – Mathieu Guindon Feb 05 '18 at 16:05

score 0 · Answer 3 · answered Feb 06 '18 at 08:00

Sub ss()
  Dim a As Double
  a = 6.99999999
  Select Case a
    Case 0 To 7:
      If a = 7 Then
          MsgBox "no"
      Else    
          MsgBox "ok"
      End If
    Case Else:
      MsgBox "no"
  End Select
End Sub

I finally work out this solution. Thank you all!

VBA Select Case number to number greater than and less than

3 Answers3