EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA String and Text Functions VBA Case
 

VBA Case

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

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

Example 1-1

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

VBA Case Example 1-2

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

VBA Case Example 1-3

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 A As Integer

A = InputBox("Enter Day of Week")

End Sub

VBA Case Example 1-4

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 A As Integer

A = InputBox("Enter Day of Week")

Select Case A

End Sub

VBA Case Example 1-5

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

VBA Case Example 1-6

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

VBA Case Example 1-7

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.

Result of Example 1-8

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.

Result of Example 1-9

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

Example 2-1

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

VBA Case Example 2-2

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

VBA Case Example 2-3

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

VBA Case Example 2-4

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

VBA Case Example 2-5

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

VBA Case Example 2-6

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.

Result of Example 2-7

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.

Result of Example 2-8

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.

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 a 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?

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download VBA Case Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW