A Comprehensive Guide to Using Select Case in VBA

The “Select-Case” statement allows us to make decisions based on the value of a variable. As you might imagine, it serves as an alternative to the If Else statement, and depending on the situation, it may be more suitable.

The Select Case structure directs the flow of information in our macro according to the value of the selecting variable. This selection results in executing specific actions, as illustrated in the following diagram.

The selecting variable can be of any type. One of the key differences between Select Case and the If Else statement is that the evaluation is made directly on the result of a variable, meaning we cannot use logical operators (And/Or) in this evaluation.

Structure of the Select Case in VBA

The basic structure of Select Case is as follows:

Select Case Variable
    Case Value that the variable can take
        Actions
End Select

Example of Select Case in VBA

If we want to apply a specific color to cells based on the evaluation result of “Approved” or “Disapproved,” we could use the following routine:

i = 1
Do Until Cells(i, 1) = ""
  Select Case Cells(i, 1)
    Case "Approved"
      Cells(i, 1).Interior.Color = RGB(0, 255, 0) ' Green
    Case "Disapproved"
      Cells(i, 1).Interior.Color = RGB(255, 0, 0) ' Red
  End Select
  i = i + 1
Loop

With this routine, the cells containing the word “Approved” will be painted green, while those containing “Disapproved” will turn red.

With practice, you will develop skills that allow you to write more compact and error-resistant code.

Would you like to learn more? Contact us for more information.