VBA Case

Excel VBA Case

There is no direct provision in Excel to call a sentence or statement by looking for any word or number. This can be done with the help of insert functions but it will take too many arguments. VBA Case used to call one or any group of statements which depends on the value of an expression. If the expression or case has in defined values or sentence then if we enter the case number we will get the values or sentence defined for that number or alphabet.

Below are the following Syntax mainly required;

• Select Case – Select Case is the text expression which is used which can be in numeric or string.
• Case – Case has expressionist-n and statement-n which means expressionist requires if there is any case and statement-n is optional if test expressions match with any of expressionist-n.
• Else Statement – If test expressional doesn’t match with any of the Case expressions then Else statement comes up.

How to Use Excel VBA Case Statement?

We will learn how to use Excel VBA Case statement with a few examples.

Excel VBA Case Statement – Example #1

We creating a VBA Case for text statement with numbers for different days of a week. Follow the below steps to use Case Statement in VBA.

Step 1: Go to the VBA window and open a new module by selecting Module from the Insert menu tab as shown below.

Step 2: Once we do that we will get a new module opened. Now write Subcategory in the name performed functioned or by any other name as per your choice as shown below.

Code:

Sub Case_Statement1()

End Sub

Step 3: As we are creating the case statement with numbers so first define an Integer as shown below.

Code:

Sub Case_Statement1()

Dim A As Integer

End Sub

Step 4: Now assign an Input Box to the defined integer and input the box name with “Enter Day of Week” as shown below. It will notify us to enter the case number to this box to get the answer.

Code:

Sub Case_Statement1()

Dim A As Integer

A = InputBox("Enter Day of Week")

End Sub

Step 5: Now to integrate defined Integer with Case, use Select command as shown below. It will assign Case as Integer.

Code:

Sub Case_Statement1()

Dim A As Integer

A = InputBox("Enter Day of Week")

Select Case A

End Sub

Step 6: As we have 7 days in a week so starting from Monday as Case 1 considering Monday is the first day of work till the last day of the week which is Sunday as Case 7. And to print all the cases into the text a message box we will use MsgBox command with all the Cases from 1 to 7 individually for each day of the week as shown below.

Code:

Sub Case_Statement1()

Dim A As Integer

A = InputBox("Enter Day of Week")

Select Case A

        Case 1: MsgBox "Day is Monday"
        Case 2: MsgBox "Day is Tuesday"
        Case 3: MsgBox "Day is Wednesday"
        Case 4: MsgBox "Day is Thursday"
        Case 5: MsgBox "Day is Friday"
        Case 6: MsgBox "Day is Saturday"
        Case 7: MsgBox "Day is Sunday"

End Sub

Step 7: Now comes the Else Statement part of Case where we will write if any of the defined cases is not TRUE then we will get Else Case statement message as “Incorrect Day”. And for this too, we will use MsgBox and End Select it.

Code:

Sub Case_Statement1()

Dim A As Integer

A = InputBox("Enter Day of Week")

Select Case A

        Case 1: MsgBox "Day is Monday"
        Case 2: MsgBox "Day is Tuesday"
        Case 3: MsgBox "Day is Wednesday"
        Case 4: MsgBox "Day is Thursday"
        Case 5: MsgBox "Day is Friday"
        Case 6: MsgBox "Day is Saturday"
        Case 7: MsgBox "Day is Sunday"

        Case Else: MsgBox "Incorrect Day"

End Select

End Sub

Step 8: Once done then compile and run the code. We will get a message box which will ask to Enter Day of Week as shown below. Here, enter any case number from 1 to 7. We will enter 5 and see what comes up. We got the message that “Day is Friday” which was assigned to Case 5.

Step 9: Now let’s enter any number other than from 1 to 7. Let it be 9. Once we do, we will get the message as “Incorrect Day” as shown below, because there are only 7 days in a week. Other value will give below error message which we defined in Else Case Statement.

Excel VBA Case Statement – Example #2

In a similar fashion in this example, we will use VBA Case to print text message with the help of String. We will use Case to print different proverb as a text statement.

Step 1: For this open a new module from Insert tab and write Subcategory in the name of the performed function as shown below.

Code: 

Sub Case_Statement2()

End Sub

Step 2: Now define a String where we will store the text values as shown below.

Code: 

Sub Case_Statement2()

Dim A As String

End Sub

Step 3: Now assign an Input Box to defined String with a sentence “Enter Word of Your Choice”.

Code: 

Sub Case_Statement2()

Dim A As String

A = InputBox("Enter Word of Your Choice")

End Sub

Step 4: Now open a Select Case loop and end with End Select for defined string A.

Code: 

Sub Case_Statement2()

Dim A As String

A = InputBox("Enter Word of Your Choice")

Select Case A

End Select

End Sub

Step 5: Now inside the Select Case loop for string A, write any proverb with a message box to pop up. Here we have selected 3 proverbs starting from Case 1 to 3 with message box command MsgBox as shown below.

Code:

Sub Case_Statement2()

Dim A As String

A = InputBox("Enter Word of Your Choice")

Select Case A

        Case 1: MsgBox "Such is the way of Life"
        Case 2: MsgBox "What goes around, comes around"
        Case 3: MsgBox "Tit For Tat"

End Select

End Sub

Step 6: Now the above statement is TRUE only between Case 1 to 3 numbers. Other than these number we need to define FALSE statement as well in Else condition as shown below.

Code:

Sub Case_Statement2()

Dim A As String

A = InputBox("Enter Word of Your Choice")

Select Case A

        Case 1: MsgBox "Such is the way of Life"
        Case 2: MsgBox "What goes around, comes around"
        Case 3: MsgBox "Tit For Tat"

        Case Else: MsgBox "Nothing Found"

End Select

End Sub

Step 7: Now compile and run the code. We will get a message where it will ask to enter any word of your choice.

As we have already defined some cases above with numbers, here we will enter any number from 1 to 3. Let’s enter 2 and see what comes up.

As we can see above proverb stored in Case 2 is showed in the message box.

Step 8: Now let’s test with any number or alphabet other than 1 to 3. Let’s take A. As we did not define anything for A, we got an error message as Nothing Found.

Pros of VBA Case Statement

• No need to write a big insert function statement in Excel instead of VBA Case Statement is quite short.
• It is quite helpful in creating some questionnaire where input feed in short.
• Quickest and easiest way to a method for adding case statement.
• Fewer chances of getting an error.

Things to Remember

• Remember to save the excel file as Macro-Enabled Excel, by this we can use created code multiple times.
• In Select Case loop, we can use Alphabets as well instead of Numbers with the help of String command.
• Always define the Else Statement in such a way that meaning of missing point break is clear.
• Complex questionnaires can also be formed with the help of multiple and different commands.

Recommended Articles

This has been a guide to Excel VBA Case Statment. Here we discussed how to use Excel VBA Case along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

1. Excel VBA MsgBox
2. Complete Guide to VBA On Error
3. Format Numbers with VBA Number Format
4. How to Use VBA TRIM?