Select Case in VBA

First principles. A program branches when it does different things depending on what it finds. The general-purpose branch is If:

If sales >= 2000000 Then
    rate = 0.08
ElseIf sales >= 1000000 Then
    rate = 0.06
ElseIf sales >= 500000 Then
    rate = 0.04
Else
    rate = 0.02
End If

Read the rules that make this correct. Excel tests each condition top to bottom and stops at the first True. So why are the bands written largest threshold first? Reverse them and everyone would match the >= 500000 line, and every high earner would be paid 4%. The Else catches everything that fell through. Use the block form (If … Then on its own line, closed by End If) for anything beyond a trivial one-liner. The single-line form If x > 0 Then y = 1 has no End If, and it doesn't scale to two statements without becoming unreadable.

If also combines with the Boolean operators you met in VBA I: And, Or, Not. One subtlety is worth knowing early. VBA's And/Or are not short-circuiting. In If Not rng Is Nothing And rng.Value > 0 Then, VBA still evaluates the right side even when rng is Nothing, and that can throw. When you need a guard, nest the tests instead of combining them with And.

When should you switch to Select Case?

When should you switch to Select Case? Once you're testing one value against many possibilities, the ElseIf ladder turns into noise, because you repeat the variable on every line. Select Case states the variable once and lists the cases:

Select Case grade
    Case Is >= 90:   letter = "A"
    Case 80 To 89:   letter = "B"
    Case 70 To 79:   letter = "C"
    Case 60 To 69:   letter = "D"
    Case Else:       letter = "F"
End Select

Select Case gives you three forms. Case Is >= 90 is a comparison, Case 80 To 89 is an inclusive range, and a comma list such as Case 1, 3, 5 is a set of exact values. Case Else is the catch-all. Like the If ladder, the cases are evaluated top to bottom and the first match wins, so overlapping ranges resolve to whichever you wrote first. Use Select Case for banding a single quantity (grade, tax slab, commission tier, size bucket) and If for conditions that have nothing to do with each other. Reaching for Select Case on a banding problem is the mark of someone who has written the six-branch nested IF in a worksheet and never wants to read it again.