{"id":12160,"date":"2012-10-13T19:18:44","date_gmt":"2012-10-13T19:18:44","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=2406"},"modified":"2022-08-12T11:15:12","modified_gmt":"2022-08-12T11:15:12","slug":"select-case-statement","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/","title":{"rendered":"VBA Programming : Decision – Select Case"},"content":{"rendered":"

Hello friends in the last article<\/a> of VBA Programming Tutorial<\/a> you have learnt the use of If..else statement<\/a>. In this lesson you are going to learn another decision making statements in VBA. It means it is another way to control the flow of program, that is nothing but Select… Case<\/strong> structure.
\n <\/p>\n

Syntax:<\/h2>\n


\n  Select Case<\/strong> <test_expression><\/p>\n

    Case<\/strong> <condition_1>
\n       <Your code 1>
\n     Case<\/strong> <condition_2>
\n       <Your code 2>
\n     …
\n     Case<\/strong> <condition_n>
\n        <your code n><\/p>\n

    Case Else<\/strong>
\n       <your else code><\/p>\n

  End Select<\/strong>
\n<\/font><\/p>\n

Where:<\/h3>\n

Test Expression:<\/strong> is a string or numeric value which you are going to compare with the list of conditions.
\nCondition 1, 2…n:<\/strong> These are the different conditions. Whichever condition is going to match with the value of Test Expression, Statements under that condition will be executed. Rest other codes in other conditions will be ignored.
\nYour Code 1, 2…n:<\/strong> is the code which is going to be execute when the condition is found true.
\n <\/p>\n

Note:<\/h3>\n

Case Else<\/strong> Statement gets executed when none of the conditions are true for a Test Expression. Note that this Statement is an Optional Statement. It means it is not necessary to put Case Else Statement unless you want to execute a piece of code to be executed even when none of the conditions are matching.
\n
\n

\"Select

Select Case Statement<\/p><\/div>
\n<\/p>\n

Use of Case Statement<\/h2>\n

It is always a good practice to use Select Case Statement<\/strong> when multiple If-Else conditions are involved. As the number of If-Else conditions increases, debugging and understanding all the flow becomes tedious job. To overcome that problem, It is advised to use Select Case statement in nested If-Else conditions.<\/p>\n

Example:<\/h2>\n

I am going to explain different ways of putting the condition expressions in Case Clause. Refer the below 4 examples:
\n 
\nTo Practice your own, you can download this Excel Workbook<\/font><\/a><\/strong> with all the four examples.
\n 
\n1. Single Expression or value as a Case Condition<\/strong><\/font>
\nIn the below example user will be asked to Enter a Number of the Day like 1, 2, 3…7 and using Select Case statement function will return the corresponding Name of the Day. Note: Here first day of the week is considered as “Monday”.<\/p>\n

\n\nSub Find_Day_1()\nDim DayNumber As Integer\nDayNumber = InputBox(\"Enter the Day Number\")\nSelect Case DayNumber\n    Case 1\n        MsgBox (\"Monday\")\n    Case 2\n        MsgBox (\"Tuesday\")\n    Case 3\n        MsgBox (\"Wednesday\")\n    Case 4\n        MsgBox (\"Thursday\")\n    Case 5\n        MsgBox (\"Friday\")\n    Case 6\n        MsgBox (\"Saturday\")\n    Case 7\n        MsgBox (\"Sunday\")\n    Case Else\n        MsgBox (\"Invalid Day Number\")\nEnd Select\nEnd Sub\n<\/code><\/pre>\n
<\/p>\n
2. Using a comma to separate multiple expressions or ranges in each Case Clause.<\/strong> <\/font><\/p>\n
Multiple expressions or ranges can be specified in each Case clause, by separating each expression with a comma It is same as logical operator OR<\/strong>. In the below example in each of the Case clause there are two expression: One is numeric and other one is string. It means if you Input : 1 then Also it will return “Monday” and If you enter “Mon” then also it will return “Monday”.<\/p>\n
\nSub Find_Day_2()\nDim DayNumber As Variant\nDayNumber = InputBox(\"Enter the Day Number\")\nSelect Case DayNumber\n    Case 1, \"Mon\"\n        MsgBox (\"Monday\")\n    Case 2, \"Tue\"\n        MsgBox (\"Tuesday\")\n    Case 3, \"Wed\"\n        MsgBox (\"Wednesday\")\n    Case 4, \"Thurs\"\n        MsgBox (\"Thursday\")\n    Case 5, \"Fri\"\n        MsgBox (\"Friday\")\n    Case 6, \"Sat\"\n        MsgBox (\"Saturday\")\n    Case 7, \"Sun\"\n        MsgBox (\"Sunday\")\n    Case Else\n        MsgBox (\"Invalid Day Number\")\nEnd Select\nEnd Sub\n<\/code><\/pre>\n
<\/p>\n
3. Using To Keyword to Specify upper and lower limit of a range.<\/strong><\/font>
\nIf you want to compare a value within a range then you can use To<\/strong> Keyword in your case clause as shown in the below example. Below example will take Marks in Percentage as input and it will return the Grade of the Student based on which range does that % belongs to.<\/p>\n
\nSub Find_Grade()\nDim Percentage As Integer\nPercentage = InputBox(\"Enter the Obtained Percentage Marks by a Student\")\nSelect Case Percentage\n    Case 0 To 33\n        MsgBox (\"Failed\")\n    Case 33 To 44\n        MsgBox (\"Third Div\")\n    Case 45 To 59\n        MsgBox (\"Second Div\")\n    Case 60 To 74\n        MsgBox (\"First Div\")\n    Case 75 To 100\n        MsgBox (\"First Div with Distinction\")\n    Case Else\n        MsgBox (\"Marks Obtained in % Can not be more than 100\")\nEnd Select\nEnd Sub\n<\/code><\/pre>\n
<\/p>\n
4. Using the Is Keyword with a comparison operator to compare.<\/strong><\/font>
\nUsing Is<\/strong> keyword you can compare the case value using comparison operator like < , > ,=<, >= etc. as shown in the below example:<\/p>\n
\nSub Find_Temp_Status()\nDim temp As Single\ntemp = InputBox(\"Enter the Temperature\")\nSelect Case temp\n    Case Is >= 40\n        MsgBox \"Extremely Hot\"\n    Case Is >= 25\n        MsgBox \"Moderately Hot\"\n    Case Is >= 0\n        MsgBox \"Cool Weather\"\n    Case Is < 0\n        MsgBox \"Extremely Cold\"\nEnd Select\nEnd Sub\n<\/code><\/pre>\n
<\/p>\n<\/span>","protected":false},"excerpt":{"rendered":"
Hello friends in the last article of VBA Programming Tutorial you have learnt the use of If..else statement. In this lesson you are going to learn another decision making statements in VBA. It means it is another way to control the flow of program, that is nothing but Select… Case structure.   Syntax:   Select Case […]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[5204],"tags":[],"yoast_head":"\nVBA Programming : Decision - Select Case - Let's excel in Excel<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"VBA Programming : Decision - Select Case\" \/>\n<meta property=\"og:description\" content=\"Hello friends in the last article of VBA Programming Tutorial you have learnt the use of If..else statement. In this lesson you are going to learn another decision making statements in VBA. It means it is another way to control the flow of program, that is nothing but Select… Case structure.   Syntax:   Select Case […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2012-10-13T19:18:44+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-08-12T11:15:12+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/10\/Select-Case-Statement.png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"VBA Programming : Decision – Select Case\",\"datePublished\":\"2012-10-13T19:18:44+00:00\",\"dateModified\":\"2022-08-12T11:15:12+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/\"},\"wordCount\":609,\"commentCount\":2,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"articleSection\":[\"VBA Programming\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/\",\"name\":\"VBA Programming : Decision - Select Case - Let's excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"datePublished\":\"2012-10-13T19:18:44+00:00\",\"dateModified\":\"2022-08-12T11:15:12+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"VBA Programming\",\"item\":\"https:\/\/vmlogger.com\/excel\/vba-programming\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"VBA Programming : Decision – Select Case\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/twitter.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"VBA Programming : Decision - Select Case - Let's excel in Excel","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/","og_locale":"en_US","og_type":"article","og_title":"VBA Programming : Decision - Select Case","og_description":"Hello friends in the last article of VBA Programming Tutorial you have learnt the use of If..else statement. In this lesson you are going to learn another decision making statements in VBA. It means it is another way to control the flow of program, that is nothing but Select… Case structure.   Syntax:   Select Case […]","og_url":"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2012-10-13T19:18:44+00:00","article_modified_time":"2022-08-12T11:15:12+00:00","og_image":[{"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/10\/Select-Case-Statement.png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"VBA Programming : Decision – Select Case","datePublished":"2012-10-13T19:18:44+00:00","dateModified":"2022-08-12T11:15:12+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/"},"wordCount":609,"commentCount":2,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"articleSection":["VBA Programming"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/","url":"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/","name":"VBA Programming : Decision - Select Case - Let's excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"datePublished":"2012-10-13T19:18:44+00:00","dateModified":"2022-08-12T11:15:12+00:00","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2012\/10\/select-case-statement\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"VBA Programming","item":"https:\/\/vmlogger.com\/excel\/vba-programming\/"},{"@type":"ListItem","position":3,"name":"VBA Programming : Decision – Select Case"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/twitter.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12160"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=12160"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12160\/revisions"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=12160"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=12160"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=12160"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}