[SOLVED] Select Case never reaching final statement

Select Case never reaching final statement

Case Else not being reached.

Private Sub nM03KG_AfterUpdate()
    
    Select Case nVcap03
        Case Is < 80
            Me.nVcap03.BackColor = vbRed
            Me.nVcap03.ForeColor = vbWhite
        Case Is > 80 < 90
            Me.nVcap03.BackColor = RGB(255, 194, 14)
            Me.nVcap03.ForeColor = RGB(47, 54, 153)
        Case Else
            Me.nVcap03.BackColor = RGB(205, 220, 175)
            Me.nVcap03.ForeColor = RGB(47, 54, 153)
    End Select

End Sub

The first 2 case options work without issue, but! the 'Case Else' is never reached. If I enter say 11,000, this should represent > 90% capacity which is the trigger point for changing the field colour to Green, with Blue Forecolor.

Solution

That is not how you express a range in a case statement.

Case Is takes a list of expressions separated by commas, and if any are true, the statements under that case are executed.

This means that Case Is > 80 < 90 will be evaluated as the single expression > (80 < 90), and 80 < 90 will be evaluated to True, which is then cast to -1 in a comparison against a number. Thus, it will be the selected case for all numbers larger than -1 (thanks @Christian Buse for the correction).

Either rely on the previous expression (which already filters out numbers below 80), or use the To keyword in your Case

So either

Select Case nVcap03
    Case Is < 80
        Me.nVcap03.BackColor = vbRed
        Me.nVcap03.ForeColor = vbWhite
    Case Is < 90
        Me.nVcap03.BackColor = RGB(255, 194, 14)
        Me.nVcap03.ForeColor = RGB(47, 54, 153)
    Case Else
        Me.nVcap03.BackColor = RGB(205, 220, 175)
        Me.nVcap03.ForeColor = RGB(47, 54, 153)
End Select

Select Case nVcap03
    Case Is < 80
        Me.nVcap03.BackColor = vbRed
        Me.nVcap03.ForeColor = vbWhite
    Case 80 To 90
        Me.nVcap03.BackColor = RGB(255, 194, 14)
        Me.nVcap03.ForeColor = RGB(47, 54, 153)
    Case Else
        Me.nVcap03.BackColor = RGB(205, 220, 175)
        Me.nVcap03.ForeColor = RGB(47, 54, 153)
End Select

Note that this second option includes 90 in the second case, while the first option does not.