Select case vba excel examples. Visual Basic programming language. Branch programming. VBA Select Case Statement Syntax
VBA has a conditional jump operator for use in cases where you need to choose from a large number of different branches of code: operator SelectCase(it's very convenient to implement a multiple choice structure). It works in much the same way as multiple independent statements If, but it is more understandable for the one who writes the code and the one who reads this code. This operator is more efficient than the operator If… Then… Else.
Keywords SelectCase used with many operators Case,where each operator Case checks the occurrence of another condition and only one of the branches is executed Case.Branch Case may contain one, more, or no VBA statements.
Select Case– a control statement that executes one of several blocks of statements depending on the value of the expression.
SelectCase selection_expression
[ Case Expression_list_1
[Operator_Block_1]
[Case Expression_list_2 ]
[Operator_block_2]]
……………………………………………………………………………
[Case Else
[Operator_Block_N]]
EndSelect
– Selection_expression – any numeric or symbolic expression;
– Expression_list – each list is a list of logical expressions separated by commas; have the same type as choice_expression;
– Statement_block – contains any number of VBA statements.
When executing the statement SelectCase VBA first evaluates the Selection_Expression, and then compares the result of that expression with every expression listed in each Expression_List . If the value represented by Selection_Expression matches the expression in Expression_List for one of Case,VBA executes StatementBlock for this statement Case.If the value of Selection_Expression matches more than one operator Case,VBA only executes statements In the first matching sentence Case.Often a choice_expression is simply the name of a single variable, mathematical or numerical, rather than a logical expression. Expressions in Expression_List - These are usually logical expressions.
After the statements in the first matching statement have finished executing Case VBA continues executing code from the first statement after keywords EndSelect, which indicate the end SelectCase.
If the value of Selection_Expression does not match any of the Case, and optional CaseElse is present, VBA executes the statements represented by Statement_Block_N before moving to the operator after SelectCase. Operator block Case Else executed only if Selection_Expression does not satisfy any of the conditions Case. Typically used to handle unwanted values. Operator action SelectCase illustrated by the block diagram shown in Fig. 5.
Fig 5. Block diagram of the design SelectCase
Expression List elements must have one of the following three forms:
Expression_1, Expression_2, …, Expression_N
Expression To Expression
Is Expression with operation
– Expression_ – any numeric, symbolic or logical expression of the same type as the selection expression;
– Expression with operation – an expression containing any of the following operations:<, <=, >, >=, < >, =.
If the keyword is used That to determine the limits of an expression, the smaller value must come first. For example, block statements Case -1 To -5 are not executed if Selection_Expression is -4. This line should be written like Case -5 To -1.
Comparison operations can only be used with a keyword Is, with the exception of the equality operation. If there is no key Is the intelligent editor will insert it in the right place.
You can use multiple expressions or limits in each condition Case. For example, Case 1 TO 4, 7 TO 9, 11, 13, Is > n %.
Example 4. A program that calculates a discount depending on the purchase amount.
Sub skidka()
"Definition of discount (in%) depending on
"quantity of goods sold
Dim skidka As Integer
Dim summa As Single
summa = InputBox(“Enter purchase amount”, “Discount calculation”, 0)
If summa > 0 Then
Select Case summa
Case Is > 1000
Case Is > 500
Case Else
End Select
MsgBox"Discount" & skidka & "%"
MsgBox"Purchase amount not specified"
And in today’s post we will discuss about VBA select case statement. VBA Select Case can be used instead of complex Excel Nested If statements. This makes the VBA code faster to execute and easier to understand.
Select-Case statement (also called as Switch Case in some languages) checks a variable or an expression for different cases (values). If anyone of the case becomes true then 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 = value_1
Case value_2
Code to Execute When Condition = value_2
Case value_3
Code to Execute When Condition = 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 matches 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=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 = InputBox("Enter the value for A:") b = InputBox("Enter the value for B:") " Evaluating the expression Select Case a = 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 from user.
Example 2: Case statement to check Text Strings
In this example we will compare text strings in the Case statements. If a match is found then the corresponding case block will execute otherwise the ‘Case Else’ block will execute.
Sub Select_Case_Example() "Enter the value for variables fruit_name = 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 know 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 = 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 enter any number from 1-10. And then we will check if the number is even or odd by using multiple conditions in the case statement. Notice here I have used a “,” (comma) to compare multiple conditions in a single case.
Sub Select_Case_Example() "Enter the value for variables Num = InputBox("Enter any Number between 1 to 10:") " Evaluating the expression Select Case Num Case 2, 4, 6, 8, 10 MsgBox "Your 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 have used 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 entered 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 cases are 'False' then 'Case Else' will be executed.
Sub Select_Case_Example() "Enter the value for variables Num = InputBox("Enter any Number between 1 to 10:") " Evaluating the expression Select Case Num Case 1 To 5 MsgBox "Your 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 thoughts 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".
PROGRAMMING LANGUAGE VISUAL BASIC. PROGRAMMING BRANCHES
Branching in Visual Basic is organized using:
- conditional IF statement;
- built-in IIF function;
- selection operator CASE.
To test one condition and execute a statement or block of statements, use conditional statement IF...THEN. This operator can be used with different syntaxes: single-line (linear) and multi-line (block).
The linear operator has the following syntax:
If<условие>Then<операторы!>
The block operator has the following syntax:
If<условие>Then
<блок операторов 1>
End If
If the given condition is True, the statement block is executed, otherwise the statement block2 is executed. If the Else clause is not specified, ifWhen the condition is met, control is immediately transferred to the next operator after If.
The If statement can be nested, that is, located inside statement blocks. To test more than one condition and execute one of several blocks of statements, use an extended conditional statement of the form:
If<условие
1>Then
<блок операторов 1>
Else<условие 2>Then
<блок операторов 2>
Else<условие n>Then
<блок операторов n>
End If
To select one of the values depending on the satisfaction or failure of some condition, use the IIF conditional function, which has the following syntax:
IIF(<условие>, <значение1>, <значение2>)
This function returns value1 if the condition is true and value2 if the condition is false.
You can use a Boolean expression as a condition that returns True or
False, or any arithmetic expression (a zero value is equivalent to False, and a non-zero value is equivalent to True).
SELECT CASE statement used to test a single condition and execute one of several blocks of statements.
Operator record format:
Select Case<проверяемое
выражение>
Case<список выражений 1>
<операторы 1>Case<список выражений 2>
<операторы 2>Case<список выражений 3>
<операторы 3>
Case Else
<операторы группы Else>
End Select
The expression being tested is evaluated at the beginning of the Select Case statement. This expression can return a value of any type (boolean, numeric, string).
A list of expressions is one or more expressions separated by a standard delimiter character (semicolon).
When executing the operator, it is checked whether at least one of the elements of this list matches the expression being tested.
These expression list alimony may take one of the following forms:
- <выражение>- checks the coincidence of a given expression with one of the expressions - list elements;
- <выражение 1>That<выражение 2>- checks whether a given expression falls within the specified range;
- < Is <логический оператор> < выражение>- checks the fulfillment of the specified condition for a given expression.
In real programs, it is often necessary to make more complex choices in procedures, choosing between three or more branches. In this case, you can place operators If..Then..Else into each other. This is called statement nesting.
The above procedure uses several nested conditional statements. It should be said that this procedure will only work in Excel, because... uses the Application.InputBox method (see Host Application Functions). This method prevents the user from entering anything other than a number while the function is running.
If the user enters a non-number, he receives a message about this.
If the user does not enter anything, he receives an error message.
If the user uses the "Cancel" button, he receives the message "No data entered."
VBA provides a shorthand version of the statement If..Then..Else, which is the condensed equivalent of nested statements If..Then..Else, used in the listing. This short form is the operator If..Then..ElseIf
Which option to use is a question that each programmer decides for himself individually. It is believed that the second option is more compact, while the first is more convenient and understandable.
You can nest statements to select from multiple possible code paths If..Then..Else many levels deep, but keeping track of the progress of the branches is becoming more and more difficult.
VBA has a conditional jump operator for use in cases where you need to select from a large number of different code branches - Select Case. It works almost the same as Else..If, but is more understandable.
Select Case keywords are used with many Case statements, where each Case statement tests for the occurrence of a different condition and only one of the Case branches is executed. A Case branch can contain one, more, or no VBA statements.
Unconditional jump operator
The unconditional jump operator can be said to be a rudiment from early programming languages, in which it was practically the only means of organizing cyclic execution of code blocks.
The unconditional jump operator always changes the order in which statements in a procedure or function are executed. In this case, no conditions are checked.
Syntax:
GoTo line
line- any valid label or line number in the same procedure or function that contains the GoTo statement.
The Select Case construct is an alternative to the If construct. . . Then. . . Else when executing a block consisting of a large set of statements. The Select Case construct provides a capability similar to that of the If construct. . . Then. . . Else, but unlike it, it makes the code more readable when there are multiple selections.
The Select Case construct operates on a single expression to be tested, which is evaluated once upon entry into the construct. VBA then compares the resulting result with the values specified in the Case statements of the construct. If a match is found, the block of statements associated with the Case statement is executed:
Select Case expression to be tested
[operator_blockn]]
Each expression list is a list of one or more values. If there is more than one value in one list, they are separated by commas. Each statement block contains several or none statements. If it turns out that the calculated value of the expression being tested matches values from several Case statements, then the block of statements associated with the first Case statement of all matches found is executed. VBA executes the block of statements associated with the Case Else statement (note that it is optional) if no matches are found between the value of the expression being tested and the values from all lists of Case statements.
Let's look at an example of calculating the function
Sub example2()
Const pi2 = 1.57
Let x = CSng(InputBox("enter x", "Input", 0))
MsgBox "Invalid source data!"
Call out("D1", z)
Note that the Select Case construct evaluates the expression only once upon entry, while the If construct. . . Then. . . Else evaluates a different expression for each Elself statement. The If construct. . . Then. . . Else can be replaced by a Select Case construct only if the If statement and each Elself statement evaluate the same expression.
Loop operators. Nested Loops
1.Loop operators
2. Nested loops
1. Loop operators.
Loops allow you to execute one or more lines of code multiple times. VBA supports the following loops:
The For construct. . . Next. When the number of repetitions is known in advance, a For loop is used. . . Next. A For loop uses a variable called a loop variable or loop counter, which is incremented or decremented by a specified amount each time the loop is repeated. The syntax for this construct is as follows:
For counter = start To end
operators
The counter, start, end, and increment parameters are numeric.
Note. The increment parameter can be either positive or negative. If it is positive, the start parameter must be less than or equal to the end parameter, otherwise the loop will not execute. If increment is negative, then start must be greater than or equal to end for the body of the loop to be executed. If the Step parameter is not specified, the default value of the increment parameter is 1.
VBA executes a For loop in the following sequence:
1. Sets the value of the loop variable counter to start.
2. Compares the value of the loop variable counter and the value of the end parameter. If counter is greater, VBA ends the loop. (If increment is negative, VBA stops executing the loop if the value of the loop variable counter is less than the value of end.)
3. Executes the statements in the body of the statements loop.
4. Increments the value of the loop variable counter by 1 or by the value of the increment parameter, if specified.
5. Repeats steps 2 to 4.
Consider an example: Calculate the value of the function f(t)
for given a, b, n, if t changes from a to b with step Dt=(b-a)/(n-1).
Sub example3()
Dim f() As Single
Dim a As Single, b As Single, t As Single, dt As Single
Dim i As Integer, n As Integer
Call read("a1", a) : Call read("b1", b) : Call read("c1", n)
ReDim f(1 To n - 1)
dt = (b - a) / (n - 1) : t = a
Call out("a2", "i") : Call out("b2", "t") : Call out("c2", "f(t)")
For i = 1 To n - 1
If t<= -1 Then
ElseIf t > 1 Then
Call out("a" & (2 + i), i) : Call out("b" & (2 + i), t) : Call out("c" & (2 + i), f(i))
The For Each construct. . . Next
For Each loop. . . Next is similar to a For loop. . . Next, but it repeats a group of statements for each element from a set of objects or from an array, instead of repeating the statements a specified number of times. It is especially useful when it is not known how many elements are in a set.
Syntax of the For Each loop construct. . . Next is:
For Each element In group
operators
Keep the following limitations in mind when using a For Each loop. . . Next:
For sets, the element parameter can only be a variable of type variant, a generic variable of type object, or an object listed in the Object Browser
For arrays, the element parameter can only be a Variant variable
You cannot use a For Each loop. . . Next with an array that has a user-defined type because a variable of type variant cannot contain a value of a user-defined type
Do...Loop design
The Do loop can be used to execute a block of statements an unlimited number of times. There are several variations of the Do design. . . Loop, but each of them evaluates a condition expression to determine when to exit the loop. As with the If construct. . . Then the condition must be a value or expression that evaluates to False (zero) or True (not zero).
In the following construction, Do. . . Loop statements are executed as long as the condition value is True:
Do While condition
operators
When executing this loop, VBA first checks the condition. If the condition is False, it skips all the loop statements. If it is True, VBA executes the loop statements, returns to the Do While statement, and tests the condition again.
Therefore, the loop represented by this construct can be executed any number of times as long as the condition value is not zero or True. Note that the statements of the loop body are not executed even once if the first time the condition is checked, it turns out to be false.
Consider an example: Calculate the sum of a series
with a given accuracy.
Sub example4()
Dim e As Single, x As Single, s As Single
Dim m As Single, p As Single, i As Single
Call read("a1", x) : Call read("b1", e)
s = 0: i = 1: m = 1: p = -1
Call out("a2", "i") : Call out("b2", "m") : Call out("c2", "s")
Do While Abs(m) >= e
Call out("a" & (2 + i), i) : Call out("b" & (2 + i), Abs(m)) : Call out("c" & (2 + i), s)
Another variation of the Do design. . . Loop first executes the statements of the loop body and then checks the condition after each execution. This variation ensures that the loop body statements are executed at least once:
operators
While condition
The other two variations of the loop construction are similar to the previous ones, except that the loop is executed while the condition is false:
The loop is not executed at all or is executed many times:
Do Until condition
Loop operators
The loop is executed at least once:
operators