{"id":1212,"date":"2013-12-10T13:01:41","date_gmt":"2013-12-10T13:01:41","guid":{"rendered":"http:\/\/excelvbatutor.com\/?page_id=1212"},"modified":"2020-04-24T11:56:39","modified_gmt":"2020-04-24T11:56:39","slug":"excel-vba-lesson-11-decision-making-2-select-case-end-select","status":"publish","type":"page","link":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-lesson-11-decision-making-2-select-case-end-select\/","title":{"rendered":"Excel 2010 VBA Lesson 11: Using Select Case"},"content":{"rendered":"\n

 [Lesson 10]<\/a><<[Table of Contents]<\/a>>>[Lesson 12]<\/a><\/strong><\/h4>\n\n\n\n

In the previous lesson, we have learned how to use  If\u2026.Then\u2026.Else<\/strong> statement. However, for multiple options, the If\u2026Then\u2026Else structure could become too bulky and difficult to debug. Fortunately, Excel  2010 VBA provides another way to handle complex multiple-choice cases, that is, the Select Case…End Select<\/strong> decision structure. The structure of Select Case…End Select is as follows:<\/p>\n\n\n\n

Select Case variable
Case value 1
Statement
Case value 2
Statement
Case value 3
Statement
.
.
Case Else
End Select<\/pre>\n\n\n\n
\n\nWe shall demonstrate the use of Select Case\u2026End Select in the following examples:<\/p>\n\n\n\n
Example 11.1<\/h4>\n\n\n\n
In this example, we want to automatically assign a remark in relation to a certain examination grade. For example, if the grade is A, we shall assign remark as High distinction, A- as distinction and so forth. To enter the code, start Excel 2010 then place a command button onto the spreadsheet and click on it to launch the Excel 2010 VBA  editor.<\/p>\n\n\n\n
The code:<\/strong>\n<\/p>\n\n\n\n
Private Sub CommandButton1_Click()\n\nDim grade As String\nDim grade As String\n grade = InputBox(\"Enter the grade(A, A-, B, C, E or F\")\nSelect Case grade\n Case \"A\"\n  MsgBox \"High Distinction\"Case \"A-\"\n  MsgBox \"Distinction\"\n Case \"B\"\n  MsgBox \"Credit\"\n Case \"C\"\n  MsgBox \"Pass\"\n Case Else\n  MsgBox \"Fail\"\nEnd Select\n\nEnd Sub\n<\/pre>\n\n\n\n