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)
- Based on the course section regarding the IF THEN ELSE instructions, build an analogous example using the Select CASE statement.
- 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.
One thought on “Select CASE statement in Excel VBA”
excellent post, very informative. I’m wondering why the other specialists of this sector do not realize
this. You should proceed your writing. I’m confident,
you’ve a great readers’ base already!