Example SELECT CASE Operator
Here it clearly does not prevent an example showing how the SELECT Case structure can actually look like.
SELECT CASE OBJROL L OFFI L M.TYPE
Case "Slideshova"
iNTSLADE \u003d INTSLYD + 1
Case "Color Negative"
iNTROLYEGENTIVE \u003d INTROLYEGENEEGAL + 1
CASE "NEGATIVE"
iNTCBNEGAL \u003d INTCBNEGAL + 1
Mgabox " Unknown type Films. "
Basically, this snippet of the program code does the same as the above program code for an example from the IF operator section. . .Elseif (omitted only checking the storage period). True, since then our hypothetical object, representing a film roller, it seems, was a bit modified - the information about the color of the film's color is now also presented to the property of the tour, and not a separate Color property, as before.
And if it is the situation in this way, then the program has to work only with one value - with the value returned by the property of the tour. - But this value compares with several of the permissible. So Select Case is just that the doctor prescribed for our occasion.
The first application of the CASE operator in this example is equivalent to using if Objrollof Film.Type \u003d "slide" then, i.e. If the property of the object's round is "slide", the program performs the following statement, otherwise it goes to the second CASE operator.
Note that the sign of the operation, the presence of which seems to be logical at first glance, there is no criteria. The reason is that in SELECT CASE operators, equality as a comparison operation is simply meant.
From the book software built-in systems. General requirements for development and documentation Author State Standard of Russia Computer + mobile phone: Effective interaction Author Goltzman Viktor IosifovichSending from the operator's site to date already, probably, all operators support sending SMS. from your site. Messages, however, can be sent only to subscribers this operatorBut this, as a rule, it happens enough. So, to practice. Now we will look at
From the book Unix: the interaction of processes Author Stevens William RichardExample: POSIX message queues and SELECT function Descriptor message queue ( type variable MQD_T) is not an "ordinary" descriptor and cannot be used with SELECT and POLL functions (Chapter 6). Nevertheless, they can be used with the channel and the MQ_Notify function. (Similar
From VBA book for teapots author Cummings Steve. From the book Firebird Database Developer Guide by Borry HelenConducting conditions in SELECT Case operators The SELECT CASE structure does not explicitly use complete conditional expressions similar to those on which it was discussed above (see "Course on the use of conditional expressions"). You must split every condition into two parts, represented as
From the book the art of programming in the language of the team shell scenarios by the author Cooper Mendel From the book SI Language Guide The author of Prata Stephen From the book Linux and Unix: Programming in Shell. Developer's Guide. by Teinsley David. From the book C ++ for beginners by the author Lippman StennaExample 10-24. Use Case #! / Bin / Bashecho; Echo "Press the key and then the RETURN key." READ KEYPRESSCASE "$ Keypress" in) Echo "Letter in the lower case" ;; ) Echo "Letter in the Upper Register" ;; ) echo "digit" ;; *) echo "punctuation sign, a space or something else" ;; ESAC # is allowed specified
From UNIX book: Network Application Development Author Stevens William RichardExample 10-25. Creating a CASE #! / BIN / BASH # Rough Example DatabaseClear Cleaning Screen "Echo List ------" ECHO "Select the person you are interested in:" Echoecho "[E] Vans, Roland" Echo " [J] ONES, Mildred "Echo" [s] Mith, Julie "Echo" [z] Anea, Morris "Echoead Personcase" $ Person "in # Please note: the variable is taken in quotes.
From the book of the authorExample 10-26. The Case operator allows you to use the setting of commands instead of the analyzed variable #! / BIN / BASH # substitution of commands in "Case" .Case $ (Arch) in # The Arch command returns a string describing the hardware aphitecut.i386) ECHO "CPU 80386 ";; i486) echo" machine based on
From the book of the authorExample A-18. Generation of prime numbers using a module division operator (balance of division) #! / Bin / bash # primes.sh: generation of prime numbers, without using arrays. # The author: Stephane Chazelas. This scenario does not use the class algorithm "Devolo Eratosthene ", # + instead of him
From the book of the authorExpansion of the IF operator using ELSE Simplest form The IF operator is the one that we just used: if (expression) The operator usually under the expression here understand the conditional expression, the values \u200b\u200bof two values \u200b\u200bare compared (for example X\u003e Y
From the book of the author18.8.2. Termination of the execution of the CASE operator Consider the following example. In the script, an infinite cycle is performed until the user enters the number greater than 5. To interrupt the cycle and return to command line Interpreter used team Break. $ PG
From the book of the author From the book of the authorA simple example of using the SELECT function Now we will redo the code of our recipient of out-of-band data and instead of the SIGURG signal will use the SELECT function. Listing 24.3 shows the receiving program. RESTING 24.3. The receiving program in which (erroneously)
Most important operators Conditions used in Excel VBA. - These are operators IF ... Then. and Select Case.. Both of these expressions check one or more conditions and, depending on the result, perform various actions. Next, we will talk about these two operators of the Conditions in more detail.
Operator "If ... Then" in Visual Basic
Operator IF ... Then. Checks the condition and, if it is true (true), a specified set of actions are performed. A set of actions that must be executed if the condition is false (FALSE) can be defined.
Syntax operator IF ... Then. This is:
If Condition1 Then.
Actions in case condition1 is satisfied
Elseif Condition2 Then.
Actions in case condition2 is satisfied
ELSE.
Actions in case no conditions are performed
End if
In this expression items Elseif and ELSE. Operator Conditions may not be used if there is no need.
Below is an example in which using the operator IF ... Then. The color of the fill of the active cell varies depending on the values \u200b\u200bin it:
If ActiveCell.Value.< 5 Then ActiveCell.Interior.Color = 65280 "Ячейка окрашивается в зелёный цвет ElseIf ActiveCell.Value < 10 Then ActiveCell.Interior.Color = 49407 "Ячейка окрашивается в оранжевый цвет Else ActiveCell.Interior.Color = 255 "Ячейка окрашивается в красный цвет End If
Please note that as soon as the condition becomes true, the execution of the conditional operator is interrupted. Consequently, if the value of the variable ActiveCell. Less than 5, then the first condition becomes true and the cell is painted in a green color. After that, the execution of the operator IF ... Then. Interrupted and the remaining conditions are not checked.
Operator "Select Case" in Visual Basic
Operator Select Case. similar to the operator IF ... Then. In that it also checks the truth of the condition and, depending on the result, chooses one of the action options.
Syntax operator Select Case. This is:
Select Case. Expression
Case Meaning1
Actions in case the result of the expression corresponds to the value1
Case Meaning2.
Actions in case the result of the expression corresponds to value2
…
Case Else
Actions in case the result of the expression does not correspond to any of the listed options
End Select.
Element Case Else It is not mandatory, but it is recommended to use it for processing unforeseen values.
In the following example using the design Select Case. The color of the casting of the current cell varies depending on the values \u200b\u200bin it:
Select Case ActiveCell.Value Case IS<= 5 ActiveCell.Interior.Color = 65280 "Ячейка окрашивается в зелёный цвет Case 6, 7, 8, 9 ActiveCell.Interior.Color = 49407 "Ячейка окрашивается в оранжевый цвет Case 10 ActiveCell.Interior.Color = 65535 "Ячейка окрашивается в жёлтый цвет Case 11 To 20 ActiveCell.Interior.Color = 10498160 "Ячейка окрашивается в лиловый цвет Case Else ActiveCell.Interior.Color = 255 "Ячейка окрашивается в красный цвет End Select
The example above shows how to set the value for the element as different methods. Case In the design Select Case.. These are these ways:
| Case Is.<= 5 | So with the help of a keyword Case Is. can be checked if it satisfies the value Expressions Condition of view <=5 . |
| Case 6, 7, 8, 9 | So you can check whether the value matches Expressions with one of the listed values. The listed values \u200b\u200bare separated by commas. |
| Case 10. | So checked whether the value matches Expressions With a specified value. |
| CASE 11 TO 20 | In this way, you can record an expression for checking whether the value satisfies Expressions Condition of view from 11 to 20 (equivalent to inequality "11<=значение<=20”). |
| Case Else | Like this, with the help of a keyword ELSE., indicate actions for the case if the value Expressions does not correspond to any of the listed options Case. |
As soon as one of the conditions is found, appropriate actions are performed and an output is made from the design. Select Case.. That is, in any case, only one of the listed branches will be performed. Case.
In real programs, it is often necessary to perform a more complex choice in the procedures, choosing between three and more branches. In this case, you can put the operators If..then..else each other. This is called the investment of operators.
The above procedure uses several conditional transition operators in each other. It should be said that such a procedure will only work in Excel, because Uses the Application.InputBox method (see Host applications). This method does not give the user while running the function to enter anything other than the number.
If the user enters no number, then the message receives this.
If the user does not enter anything, it gets a mistake of error.
If the user uses the "Cancel" button, then the message "not entered" is received.
VBA provides abbreviated version of the operator If..then..elsewhich is compressed equivalent of nested operator If..then..elseused in listing. Such a brief form is the operator If..then..elseif
Which of the options to use is a question that decides each programmer for yourself individually. It is believed that the second option is more compact, while the first is more comfortable and understandable.
To select a choice of several possible branches of code, you can invest Operators If..then..else On many levels deep into, but to keep track of the progress of the branches is becoming harder and more difficult.
VBA has a conditional transition statement for use in cases where it is necessary to choose from a large number of different branches of code - Select Case.. It works almost the same as Else..IF, but more understood.
The Select Case keywords are used with many CASE operators, where each CASE operator checks the appearance of another condition and only one of the branches of CASE. The CASE branch may contain one, several or not a single VBA operator.
Unconditional transition operator
The unconditional transition operator can be said is a rudiment from early programming languages \u200b\u200bin which it was almost the only means of organizing cyclic execution of code blocks.
The unconditional transition statement always changes the procedure for performing operators in the procedure or function. In this case, no conditions are checked.
Syntax:
Goto Line
line - Any allowable label or row number in the same procedure or function that contains the GOTO statement.
And in Today's Post We Will Discuss About VBA Select Case Statement. VBA Select Case Can Be Used Instad Of Complex Excel Nested If States. This Makes The Vba Code Faster to Execute and Easier to Understand.
SELECT-CASE STATEMENT (ALSO CALLED AS SWITCH CASE IN SOME LANGUAZ) CHECKS A VARIABLE OR AN Expression for Different Cases (Values). IF Anyone of the Case Becomes True True Only That Case Is Executed and The Program Ignores All Other Cases.
IF You Remember in Our Last Post We Talked About, "How You Can".
SYNTAX OF VBA SELECT CASE STATEMENT:
The Syntax IS AS Under:
SELECT CASE CONDITION
Case Value_1
Code to Execute WHEN CONDITION \u003d VALUE_1
Case Value_2.
Code to Execute WHEN CONDITION \u003d VALUE_2
Case Value_3.
Code to Execute WHEN CONDITION \u003d VALUE_3
Case Else
Code to Execute When All the Other Cases Are False
End Select.
Here, 'Condition' Refers to the Variable Or The Expression That Is To Be Tested and Based on Which Anyone of the Code Segments Will Be Executed.
'Value_1', 'Value_2' and 'Value_3' Are The Possible Outcomes of the 'Condition'. Whenever Anyone of These Values \u200b\u200bMatches The 'Condition' Then Its CorreSponding Case Block Will Execute.
'ELSE' IS A Kind of Default Case Value, Which Will Only Execute When All The Above Case Statements Result Into False. 'Else' Case Is Optional But Generally It Is Considered A Good Practice To Use IT.
Examples of SELECT-CASE IN VBA:
NOW Let's Move On to Some Practical Examples of Case Statements.
Example 1:Select Case Statement with An Expression.
In The Below Example, We Have Supplied A Condition (i.e. a \u003d b) to the Select Case Statement. If This Is True Then 'Case True' Block Will Be Executed and If IT is False then 'Case False' Block Will Execute.
Sub select_case_example () "ENTER THE VALUE FOR VARIABLES A \u003d INPUTBOX (" ENTER THE VALUE FOR A: ") B \u003d INPUTBOX (" ENTER THE VALUE FOR B: ")" EVALUATING THE EXPRESSION SELECT CASE A \u003d B Case True MSGBox "The Expression IS True "Case False MSGBox" The Expressions Is False "END SELECT END SUBNote:In this Code IS Used for Getting Values \u200b\u200bFrom User.
EXAMPLE 2:Case Statement to Check Text Strings
In This Example We Will Compare Text Strings In The Case States. If A Match Is Found The CorreSponding Case Block Will Execute Otherwise The 'Case Else' Block Will Execute.
Sub select_case_example () "ENTER THE VALUE FOR VARIABLES FRUIT_NAME \u003d INPUTBOX (" ENTER THE FRUIT NAME: ")" EVALUATING THE EXPRESSION SELECT CASE FRUIT_NAME CASE "Apple" MSGBOX "You Entered Apple" Case "Mango" MSGBox "You Entered Mango" Case "Orange" MSGBox "You Entered Orange" Case ELSE MSGBox "I Didn" T KNEW This Fruit! "END SELECT END SUB
Example 3:Case Statement to Check Numbers
In The Below Example We Will Check If The Number Entered by User Is Less Than or Greater Than 5.
Sub select_case_example () "ENTER THE VALUE FOR VARIABLES NUM \u003d INPUTBOX (" ENTER ANY NUMBER BETWEEN 1 TO 10: ")" EVALUATING THE EXPRESSION SELECT CASE NUM CASE IS< 5 MsgBox "Your Number is less than 5" Case Is = 5 MsgBox "Your Number is Equal to 5" Case Is > 5 MSGBox "Your Number Is Greater Than 5" END SELECT END SUB
Note:You can use IS.keyword with Case Statement to Compare Values.
EXAMPLE 4:Select Case Statement to Check Multiple Conditions Inside A Single Case.
In this Example We Will Ask The User to End Any Number from 1-10. And The Number Is Even Or Odd by Using Multiple Conditions in the Case Statement. Notice Here I Havened a "," (Comma) to Compare Multiple Conditions in a Single Case.
Sub select_case_example () "ENTER THE VALUE FOR VARIABLES NUM \u003d INPUTBOX (" ENTER ANY NUMBER BETWEEN 1 TO 10: ")" EVALUATING THE EXPRESSION SELECT CASE NUM CASE 2, 4, 6, 8, 10 MSGBOX "YUR NUMBER IS EVEN." Case 1, 3, 5, 7, 9 MSGBox "Your Number Is Odd." Case Else MSGBox "Your Number Is Out of The Range." END SELECT END SUB
Note:I know That There Are Easier Methods to Check If a Number Is Even Or Odd, But i Havesed This example Only for Explaining How You Can Check Multiple Conditions Inside A Single Case Statement.
EXAMPLE 5:Case Statement to Check a Continuous Range AS Condition.
Here We Will Test A Continuous Range AS A Condition. We Will Ask The User to Enter Any Number Between 1-10, If the Number Is Between 1 to 5 (Including Both 1 and 5) THEN 'CASE 1 TO 5' WILL BE 'TRUE', IF THE NUMBER ENERGED by The USER IS Between 6 and 10 (Including Both 6 and 10) THEN 'CASE 6 TO 10' WILL BE 'TRUE', IF BOTH THE PREVIOUS CASE ELSE 'WILL BE EXECUTED.
Sub select_case_example () "ENTER THE VALUE FOR VARIABLES NUM \u003d INPUTBOX (" ENTER ANY NUMBER BETWEEN 1 TO 10: ")" EVALUATING THE EXPRESSION SELECT CASE NUM CASE 1 TO 5 MSGBOX "YUR NUMBER BETWEEN 1 TO 5" CASE 6 TO 10 MSGBOX "Your Number Between 6 to 10" Case Else MSGBox "Your Number Is Out of The Range." END SELECT END SUB
SO, This Was All About Vba Select Case Statement. Feel Free to Share Your Thougohts ABOUT This Topic.
About Ankit Kaul.
Ankit Is The Founder of Excel Trick. HE IS Tech Geek Who Loves to Sit In Front of His Square Headed Girlfriend (HIS PC) All Day Long. : D. ANKIT HAS A STRONG PASSION FOR LEARNING Microsoft Excel. HIS Only Aim Is To Turn You Guys Into "Excel Geeks".
When creating complex programs, one of the key points is the ability to provide several options for developing events. The easiest and most classic example is the operator " If ... Then ... Else ... End"That allows you to select one of two actions depending on the results of checking any values. It happens that as a result of this check, you must choose from a variety of options. One of the outputs: Add set" ... Elseif... "that somewhat complicates the syntax of the program (ease of reading). However, this is a very powerful operator that opens up great opportunities. More about it can be found.
Alternative to the operator " If ... End."Serves operator" Select Case."(from English" Select Case."You can translate as a" choice of situation "), which simplifies the perception of the code" on the eye ". And if" If ... End."Operator in each of its" Elseif"It is forced to turn to the verified values \u200b\u200bagain and again (for example, the expression is the same every time), then" Select Case."Does it only once, allowing the latter on large data arrays to work faster. This operator allows you to conveniently set the branch of the program from one point in a large number of branches. That is, mainly applied with multiple check conditions, when the conditions of the conditions are checked more than two.
The structure of the operator "Select Case".
Let's see what the generalized structure of the operator looks like and wonder what is that ( different examples Private use of the code will be shown at the end of the article):
Select Case [Checked] Case [Specled] [Some Action] Case ELSE [Some X] END SELECT
As a piece [Value] You can insert any variable or property whose value or you can check. You can also check the value of a particular cell. At the same time, it is possible not only with numbers, but also with the texts. And even with Boolean values True / False ("True" and "Lie"), what is not all knowing about.
[Specific value] - this is what is compared with [Verified value] . And, if one satisfies the other, then it is performed [Some Action] . There are several options for recording for block [Specific value] . For textual and numeric values, you can record different values \u200b\u200bover a comma:
Case 3, 4, 5, "Yes", "No"
For numbers, you can choose ranges:
Case 3 to 10 "from 3 to 10, including 3 and 10 themselves.
Also for numbers, you can use a logical comparison operator with a particle " IS.":
Case Is.< 2 "Меньше 2, НЕ включая 2 Case Is = 3 "Равно 3-м. Избыточная запись, достаточно Case 3 Case Is >\u003d 4 "more either equal to 4 case is<> 0 "Not equal to zero
It is permissible to use logical operators, which will allow to provide for the most difficult cases and carry out parallel comparisons with other variables. In addition to the operator " Or.", Which is replaced by the usual comma.
Case ... and ... Case Not ...
[Some Action] May be absolutely any. If you miss it - for this case, the program will inactive. " Case [Specific value] "Together with a part [Some Action] fold in one block:
CASE [Specific Value] [Some Action]
Such blocks can be any quantity that will be put into the limit dimensions of the procedure (it should weigh no more than 64 kilobytes). It is useful to know that VBA browsing compliance [Specific value] and [Value Value] Along the blocks from top to bottom. That is, you can have two blocks with the same " Case"But only the one that will be previously found by the program when viewing the code from top to bottom.
Case Else - these are all other cases that never approached [Specific value] in all blocks of the operator " Select Case."If the block" Case Else"None and no other block approached, the program makes a logical" nothing. " Case Else Must be the last inspected case among all blocks of verification in the operator. After it, other blocks should not be, otherwise we will get a syntactic error " CASE WITHOT SELECT CASE".
At the end of the operator should stand " End Select.", which serves as a" point "in the" offer "of the operator.
Examples of using.
Consider several examples of using the code and begin with the simplest. In the first example Depending on the value, the message is displayed.
Sub selectCase_example_1 () Dim X As long x \u003d 1 "You can change this digit and see what happens. SELECT CASE X CASE 1 MSGBox" One "Case 2 MSGBox" Two "Case 3 MSGBox" Three "Case ELSE MSGBox" is chosen. That's different "END SELECT END SUB
The second example Shows some types of recording values. Depending on the number of sheets in the book with a macro, a different message is displayed. Please note that if sheets in the book 7, then the first will work " Case 7.", Although the condition" CASE 5 TO 12"Also suitable, but it is later.
Sub selectcase_example_2 () "We introduce a variable and calculate the number of sheets in the current book: Dim X AS Long X \u003d thisworkbook.sheets.count Select Case X" Depending on the number of sheets in the book, we will withdraw a message. Case 1 "If 1 sheet, then ... msgbox" One sheet in the book "Case 2, 3, 4" if sheets 2 or 3 or 4 msgbox "Several sheets in the book" Case 7 "if sheets 7 msgbox" Beautiful number of sheets "Case 5 to 12" If sheets from 5 to 12 msgbox "Almost brochure" Case IS\u003e \u003d 14 "If sheets are more or equal to 14 msgbox" Sheets like in Foliant "Case Else" all other cases, namely 13 msgbox "Chestov dozen Sheets "END SELECT END SUB
Third example Focusing on Boolean value True.or False. Checked, visible or hidden the latest sheet in the current book with a macro. Using a colon, a string transfer can be replaced to obtain a more elegant code.
Sub selectCase_example_3 () "We introduce a variable and give it to the last sheet in the book: DIM SHTX AS WORKSHEET: SETX \u003d THISWORKBOOK.SHEETS (thisworkbook.sheets.count) SELECT Case Shtx.Visible" Check whether the sheet is hidden or not Case True: MSGBox "Last List in the Book Available" "If the last sheet is visible Case False: MSGBox" Last list in the book is hidden "" If the last sheet is hidden End Select End Sub
Fourth example shows that " Case»It can be focused on other variables. IN this case We will check the equality of three variables using a logical operator " And.»:
Sub selectcase_example_4 () "We introduce several variables: dim x%, y%, z%" equate all to the top three: x \u003d 3: y \u003d 3: z \u003d 3 Select Case True "We check the equality of all variables Case Z \u003d x and y \u003d X: msgbox "All are equal" "If all are equal to CASE ELSE: MSGBox" Someone is different "" If at least someone is different END SELECT END SUB
Fifth example Shows how through the comma in the valid value for " Case»You can specify a whole set of numbers. Suppose there is some function and we check whether our number can be used in this feature. By condition, we are satisfied with the numbers in the range from 5 (not including 5) to minus infinity, from 12 to 15, including ends and from 20 (including 20) to plus infinity.
Sub selectCase_example_5 () "We introduce a variable and give it the value manually Dim X AS Double X \u003d InputBox (" Enter the numeric value of the variable x ") Select Case x" Check whether some imaginary function is suitable for CASE IS< 5, Is >\u003d 20, 12 to 15 "Range of suitable MSGBox values" Actual value for some "Case ELSE" not suitable MSGBox values \u200b\u200b"The value cannot be used in some feature" END SELECT END SUB
Summing up, I note that the operator " Select Case."The structure is quite simple and easy to use. It is less flexible compared to " If ... End."If the checks are required to change the verifiable value, but significantly wins with a variety of checks of the same expression. What is actually created.
Thanks for attention.
Article with examples amounted to Roman "Rioran" Voronov for www.Syt.
