Control structures allow you to control the sequence of program execution. Without control statements, all program statements will run from left to right and top to bottom. However, sometimes it is required to repeatedly execute a certain set of instructions automatically, or to solve the problem differently depending on the values of variables or parameters specified by the user at runtime. Control constructs and loops are used for this.
VBA supports the following decision constructs:
If. ... ... Then. ... ... Else
6.1 The If construct. ... ... Then
If. ... ... Then is used when it is necessary to execute one or a group of statements depending on some condition. The syntax for this construct allows you to specify it on one line or in several lines of the program:
If condition Then expression If condition Then expression End If
The condition is usually a simple comparison, but it can be any expression with a calculated value. This value is interpreted as False if it is zero, and any non-zero is considered True. If the condition is true, then all expressions after the Then keyword are executed. To conditionally execute a single statement, you can use both single-line syntax and multi-line syntax (block construction).
The following two operators are equivalent:
If anyDate< Now Then anyDate = Now If anyDate < Now Then anyDate = Now End If
Note that the syntax of the If statement. ... ... Then does not use the End If statement for a single line. To execute a sequence of statements if a condition is true, use the If block clause. ... ... Then. ... ... End If.
If anyDate< Now Then anyDate = Now Timer.Enabled = False " Запретить таймер. End If
If the condition is false, then the statements after the Then keyword are not executed, and control is passed to the next line (or the line after the End If statement in a block construction).
6.2 If construct. ... ... Then. ... ... Else
defines several blocks of statements, one of which will be executed depending on the condition:
If condition1 Then expression1 ElseIf condition2 Then expression2. ... ... Else expression -n End If
When executed, condition1 is checked first. If false, VBA checks next condition2, and so on, until it finds a true condition. When VBA finds it, it executes the appropriate block of statements and then transfers control to the statement following the End if statement. In this construct, you can include an Else statement block that VBA executes if none of the conditions are met.
If. ... ... Then. ... ... ElseIf is really just a special case of the If construct. ... ... Then. ... ... Else. Note that this construction can contain any number of ElseIf blocks, or even none. The Else block can be included regardless of the presence or, conversely, the absence of ElseIf blocks.
Sub example 1 () Dim a As Single, b As Single, x As Single Dim z As Double Call read ("A1", a) Call read ("B1", b) Let x = CSng (InputBox ("enter x", "Data input", 0)) If x<= a Then z = Sin(x) ElseIf x >= b Then z = Tan (x) Else: z = Cos (x) End If Call out ("C1", z) End Sub
Note that you can add any number of Elself blocks to your If statement. ... ... Then. However, the number of Elself blocks can become so large that If. ... ... Then it will become very cumbersome and inconvenient. In such a situation, another decision-making construct should be used - Select Case.
6.3 Select Case Construction
The Select Case construct is an alternative to the If construct. ... ... Then. ... ... Else in case of execution of a block consisting of a large set of statements. The Select Case construct provides a capability similar to the If construct. ... ... Then. ... ... Else, but it makes your code more readable when you have multiple choices.
The Select Case construct operates on a single testable expression, which is evaluated once upon entry into the construct. VBA then compares the result with the values specified in the Case statements of the construct. If a match is found, the statement block associated with the Case statement is executed:
Select Case test_expression]]. ... ... ] End Select
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 multiple statements or none. If it turns out that the calculated value of the tested expression corresponds to values from several Case statements, then the block of statements associated with the first Case statement from all found matches is executed. VBA executes a statement block associated with a Case Else statement (note that it is optional) if no match is found between the validated expression value and the values from all Case statement lists.
Let's consider an example of calculating the function
Sub example2 () Const pi2 = 1.57 Dim x As Single Dim z As Double Let x = CSng (InputBox ("enter x", "Data input", 0)) Select Case x Case -pi2 z = Sin (x) Case 0 z = Cos (x) Case pi2 z = Tan (x) Case Else MsgBox "Invalid input data!" Exit Sub End Select Call out ("D1", z) End Sub
Note that the Select Case construct evaluates the expression only once when entering it, and in the If. ... ... Then. ... ... Else, a different expression is evaluated for each Elself statement. If. ... ... Then. ... ... Else can be replaced with Select Case only if the If statement and each Elself statement evaluate the same expression.
VISUAL BASIC PROGRAMMING LANGUAGE. BRANCH PROGRAMMING
Branching in Visual Basic is organized using:
- conditional IF statement;
- built-in IIF function;
- select operator CASE.
To check one condition and execute an operator or block of statements, use conditional IF ... THEN... This operator can be used with different syntax: single-line (linear) and multi-line (block).
The linear operator has the following syntax:
If<условие>Then<операторы!>
The block statement has the following syntax:
If<условие>Then
<блок операторов 1>
End If
If the specified condition is True, the statement block is executed; otherwise, the statement block2. If the Else clause is not specified,If the condition is satisfied, the control is immediately passed to the next operator after the If.
The If statement can be nested, that is, it can be inside statement blocks. To test more than one condition and execute one of several statement blocks, an extended conditional operator of the form is used:
If<условие
1>Then
<блок операторов 1>
Elself<условие 2>Then
<блок операторов 2>
Elself<условие n>Then
<блок операторов n>
End If
To select one of the values depending on the fulfillment or non-fulfillment of a certain condition, the IIF conditional function is used, which has the following syntax:
IIF (<условие>, <значение1>, <значение2>)
This function returns value1 if the condition is true and value2 if the condition is false.
As a condition, you can use a Boolean expression that returns True or
False, or any arithmetic expression (zero is equivalent to False and nonzero is True).
SELECT CASE statement is used to test one condition and execute one of several statement blocks.
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 start of the Select Case statement. This expression can return any type of value (boolean, numeric, string).
An expression list is one or more expressions separated by a standard separator character (semicolon).
When the operator is executed, it is checked whether at least one of the elements of this list matches the expression being tested.
These expression list alimony can take one of the following forms:
- <выражение>- checks the coincidence of the given expression with one of the expressions - the elements of the list;
- <выражение 1>That<выражение 2>- checks if the specified expression is within the specified range;
- < Is <логический оператор> < выражение>- checks the fulfillment of the specified condition for the specified expression.
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 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 = 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".
The most important conditional operators used in Excel VBA are the operators If… Then and Select Case... Both of these expressions check one or more conditions and, depending on the result, perform different actions. Next, we will talk about these two conditional operators in more detail.
Visual Basic If ... Then Statement
Operator If… Then checks the condition and, if it is TRUE, then the specified set of actions is performed. A set of actions can also be defined to be performed if the condition is FALSE.
Operator syntax If… Then like this:
If Condition1 Then
Actions if Condition1 is met
ElseIf Condition2 Then
Actions if Condition2 is met
Else
Actions in case none of the Conditions are met
End If
In this expression, the elements ElseIf and Else operator conditions may not be used if they are not needed.
Below is an example in which using the operator If… Then the fill color of the active cell changes depending on the value in 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
Note that as soon as the condition becomes true, the execution of the conditional statement is interrupted. Therefore, if the value of the variable ActiveCell less than 5, then the first condition becomes true and the cell turns green. After that, the execution of the operator If… Then is interrupted and other conditions are not checked.
Select Case Statement in Visual Basic
Operator Select Case similar to operator If… Then in that he also checks the truth of the condition and, depending on the result, chooses one of the options.
Operator syntax Select Case like this:
Select Case Expression
Case Value1
Actions in case the result of Expression matches Value1
Case Value2
Actions in case the result of Expression matches Value2
…
Case else
Actions in case the result of Expression does not match any of the listed options Values
End Select
Element Case else is optional, but is recommended for handling unexpected values.
In the following example, using the construction Select Case the fill color of the current cell changes depending on the value in 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 above example shows how you can set a value for an element in various ways Case in construction Select Case... These are the ways:
| Case is<= 5 | Thus, using the keyword Case is it is possible to check if the value satisfies Expressions condition like <=5 . |
| Case 6, 7, 8, 9 | This is how you can check if the value is the same Expressions with one of the listed values. The listed values are separated by commas. |
| Case 10 | This checks if the value is the same Expressions with a given value. |
| Case 11 To 20 | Thus, you can write an expression to test whether the value satisfies Expressions condition like from 11 to 20(equivalent to the inequality “11<=значение<=20”). |
| Case else | Like this, using the keyword Else, actions are indicated in the event that the value Expressions does not match any of the listed options Case. |
As soon as one of the conditions is found, the corresponding actions are performed and the structure is exited. Select Case... That is, in any case, only one of the listed branches will be executed Case.
O.K., there is no way to do what you want. You can't use anything other than Excel syntax inside a formula, so things like "A1 = 1 to 9" are just not possible.
You could write a rather complex VBA routine that took strings or whatever and parsed them, but that really comes down to designing and implementing a complete little language. And your "code" won't work well with Excel. For example, if you called something like
Cases ("(A1 =" "" "," "there is nothing" "), (else, A1)")
(note the escaped quotes) Excel will not update your A1 reference when you move it or copy a formula. Therefore, discard the entire "syntax" parameter.
However, it turns out that you can get most of the behavior that I think you really want with regular Excel formulas plus one tiny VBA UDF. First the UDF:
Public Function arr (ParamArray args ()) arr = args End Function
This allows us to create an array from a set of arguments. Since arguments can be expressions, not just constants, we can call it from the formula like this:
Arr (A1 = 42, A1 = 99)
and return an array of booleans.
With this little UDF, you can now use regular formulas for "case picking". They would look like this:
CHOOSE (MATCH (TRUE, arr (A1> 5, A1<5, A1=5), 0), "gt 5", "lt 5", "eq 5")
What happens is that "arr" returns a boolean array, "MATCH" finds the position of the first TRUE, and "CHOOSE" returns the corresponding "case".
You can emulate the "else" clause by wrapping the whole thing in "IFERROR":
IFERROR (CHOOSE (MATCH (TRUE, arr (A1> 5, A1<5), 0), "gt 5", "lt 5"), "eq 5")
If this is too much for you, you can always write another VBA UDF that will result in MATCH, CHOOSE, etc. inside and will call it like this:
Cases (arr (A1> 5, A1<5, A1=5), "gt 5", "lt 5", "eq 5")
This is close to your suggested syntax and much, much simpler.
I see that you have already come up with a (good) solution that is closer to what you really want, but I thought I would add this anyway, since my expression above is about casting MATCH, CHOOSE, etc. inside the UDF made it look simpler than it really is.
So here's the "UDF" cases:
Public Function cases (caseCondResults, ParamArray caseValues ()) On Error GoTo EH Dim resOfMatch resOfMatch = Application.Match (True, caseCondResults, 0) If IsError (resOfMatch) Then cases = resOfMatch Else Call assign (cases, caseValues (LBound) (caseValues + resOfMatch - 1)) End If Exit Function EH: cases = CVErr (xlValue) End Function
It uses a small helper routine, "assign":
Public Sub assign (ByRef lhs, rhs) If IsObject (rhs) Then Set lhs = rhs Else lhs = rhs End If End Sub
The assign procedure just makes it easier to deal with the fact that users can invoke UDFs with any value or range reference. Since we want the UDF of our "cases" to work like Excel "CHOOSE", we would like to return references as needed.
Basically, in the new UDF "cases" we make the "choice" ourselves, indexing the case values into the parameter array. I removed the error handler there, so basic things like mismatch between case condition results and case values will result in a #VALUE! Return value. You would probably add more checks to the actual function, for example, to make sure the condition results were boolean, etc.
I am glad that you have achieved an even better solution for yourself! That was interesting.
MORE ABOUT "assign":
In response to your comment, here's more about why this is part of my answer. VBA uses a different syntax for assigning an object to a variable than for assigning a simple value. Have a look at the VBA help or look at this stackoverflow question and others will like: What does the Set keyword really do in VBA?
This matters because when you call a VBA function from an Excel formula, the parameters can be Range objects, in addition to numbers, strings, booleans, errors, and arrays. (See Can an Excel VBA UDF invoked from a worksheet ever be passed an instance of any Excel VBA object model class other than "Range"?)
Range references are what you describe using Excel syntax such as A1: Q42. When you pass one to the Excel UDF as a parameter, it appears as a Range object. If you want to return a Range object from a UDF, you need to do so explicitly using the VBA "Set" keyword. If you don't use Set, Excel will instead take the value within the range and return it. Most of the time it doesn't matter, but sometimes you want the actual range, for example when you have a named formula that needs to evaluate to a range because it is used as the source for the checklist.
