Trending October 2023 # How To Use Excel Vba Case Statement With Examples # Suggested November 2023 # Top 14 Popular

You are reading the article How To Use Excel Vba Case Statement With Examples updated in October 2023 on the website Saigonspaclinic.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 How To Use Excel Vba Case Statement With Examples

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 is used to call one or any group of statements that depends on the value of an expression. If the expression or case has defined values or sentences, then if we enter the case number, we will get the values or sentences defined for that number or alphabet.

Below are the following Syntax mainly required;

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Select Case – Select Case is the text expression that 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 the Excel VBA Case statement with a few examples.

You can download this VBA Case Excel Template here – VBA Case Excel Template

Excel VBA Case Statement – Example #1

We are creating a VBA Case for text statement with numbers for different days of the week. Follow the below steps to use the 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

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 in this box to get the answer.

Code:

Sub

Case_Statement1()

Dim

As Integer

A = InputBox("Enter Day of Week")

End Sub

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

Code:

Sub

Case_Statement1()

Dim

As Integer

A = InputBox("Enter Day of Week")

Select Case

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 message box, we will use the 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

As Integer

A = InputBox("Enter Day of Week")

Select Case

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 the 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

As Integer

A = InputBox("Enter Day of Week")

Select Case

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 that will ask to Enter Day of the 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 values 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 messages with the help of String. We will use Case to print different proverbs as a text statement.

Step 1: For this, open a new module from the 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

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

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.

Sub

Case_Statement2()

Dim

As String

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

Select Case

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 the message box command MsgBox as shown below.

Code:

Sub

Case_Statement2()

Dim

As String

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

Select Case

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 the FALSE statement as well in the Else condition as shown below.

Code:

Sub

Case_Statement2()

Dim

As String

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

Select Case

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 you 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 the input feed is 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 the Select Case loop, we can use Alphabets as well instead of Numbers with the help of the String command.

Always define the Else Statement in such a way that the meaning of missing point break is clear.

Complex questionnaires can also be formed with the help of multiple and different commands.