Select CASE statement in Excel VBA

Last Updated on January 11, 2023 by token

SELECT CASE instruction – introduction

Another instruction next to IF THEN ELSE used in the decision making process by your programs is the SELECT CASE VBA statement. The instruction checks entered value with the defined conditions. On this basis, one scenario is chosen in your decision-making process. After selecting the scenario, the action stored in the block is executed Case scenario and the statement ends its operation. The model of CASE VBA statement looks like this:

Select Case Entered_Value
 Case Entered_Value_1
    Block_of_code when True
 Case Entered_Value_2
     Block_of_code when True
 Case Entered_Value_n
     Block_of_code when True
 Case Else
     Block_of_code when all conditions are not True
End Select

An example of the Select CASE VBA instruction

For starters, let’s do a simple example. We want the word to display after entering the month number in the InputBox window . 

So for January it will be the number 1, for February 2, etc. In case when the value entered will go out from the range 1-12, or the entered value will not be an integer, we will receive a message asking for the correct value.

Sub CaseExample()
 Dim IntMonthNumber As Variant
 IntMonthNumber = InputBox("Please enter value from 1 to 12")
 Select Case IntMonthNumber
 Case 1
 MsgBox "January"
 Case 2
 MsgBox "February"
 Case 3
 MsgBox "March"
 Case 4
 MsgBox "April"
 Case 5
 MsgBox "May"
 Case 6
 MsgBox "June"
 Case 7
 MsgBox "July"
 Case 8
 MsgBox "August"
 Case 9
 MsgBox "September"
 Case 10
 MsgBox "October"
 Case 11
 MsgBox "November"
 Case 12
 MsgBox "December"
 Case Else
 MsgBox "Entered value is wrong. Please enter integer value from 1 to 12"
 End Select
End Sub

Tasks (You can enter the solution in a comment)

1. Based on the course section regarding the IF THEN ELSE instructions,  build an analogous example using the Select CASE statement.
2. Based on the example in this chapter, write a program which after entering the number representing the day of the week will return its verbal version.