Conditions are very useful in programming because they allow us to perform actions depending on set criteria (the same principle is used as in IF Excel functions).
The most important function that sets the condition is IF and now we will see how it works:
If [CONDITION HERE] Then "=> IF the condition is true, THEN "Instructions if true Else "=> ELSE "Instructions if false End If
Let's get practical and go back to the example we used in the variable lesson. The purpose of this procedure was to open a dialog box that would contain the value from the string specified in the cell F5:
If you enter a letter into a cell F5, this will cause an error. We want to prevent this.
Sub variables () "Declaring variables Dim last_name As String, first_name As String, age As Integer, row_number As Integer "Assigning values to variables row_number = Range("F5") + 1 last_name = Cells(row_number, 1) first_name = Cells(row_number , 2) age = Cells(row_number, 3) "Dialog Box MsgBox last_name & " " & first_name & "," & age & " years" End Sub
Let's add a condition that will check whether the entered value is in the cell F5 number before the code is executed.
We will use the function IsNumeric to check the condition:
Sub variables () "If the value in parentheses (cell F5) is numeric (AND THEREFORE THE IF CONDITION IS TRUE) then "execute the statements that follow THEN If IsNumeric (Range ("F5")) Then "Declaring Variables Dim last_name As String , first_name As String, age As Integer, row_number As Integer "Assigning values to variables row_number = Range ("F5") + 1 last_name = Cells (row_number, 1) first_name = Cells (row_number, 2) age = Cells (row_number, 3) "Dialog Box MsgBox last_name & " " & first_name & "," & age & " years" End If End Sub
We also need to write down instructions if the condition we set is not met:
Sub variables () If IsNumeric (Range ("F5")) Then "If the condition is true "Declaring variables Dim last_name As String, first_name As String, age As Integer, row_number As Integer "Assigning values to variables row_number = Range ("F5") + 1 last_name = Cells (row_number, 1) first_name = Cells (row_number, 2) age = Cells (row_number, 3) "Dialog Box MsgBox last_name & " " & first_name & "," & age & " years" Else "If condition not executed "Dialog box: MsgBox warning "The entered value " & Range ("F5") & " is not valid!" "Deleting the contents of cell F5 Range ("F5").ClearContents End If End Sub
Now non-numeric values will not cause any problems.
Working with our array, which contains 16 rows of data, our next step will be to check whether the row_number variable is "greater than or equal to 2" and "less than or equal to 17".
But first, let's take a look at comparison operators:
and these useful operators:
Now let's add one of the above conditions AND between comparison operators:
Sub variables () If IsNumeric (Range ("F5")) Then "If numeric value Dim last_name As String, first_name As String, age As Integer, row_number As Integer row_number = Range ("F5") + 1 If row_number > = 2 And row_number
If we want to make our macro more practical, we can replace 17 to a variable that would contain the number of lines. This would allow us to add and remove rows from the array without having to change this limit each time.
In order to do this we must create a variable nb_rows and add this feature.
In this case, we use the function WorksheetFunction.CountA, which is an analogue of the function COUNTA in Excel itself.
We want this function to count the number of non-empty cells in the first column and write the resulting value to a variable nb_rows:
Sub variables () If IsNumeric (Range ("F5")) Then "IF NUMBER Dim last_name As String, first_name As String, age As Integer, row_number As Integer Dim nb_rows As Integer row_number = Range ("F5") + 1 nb_rows = WorksheetFunction.CountA (Range ("A:A")) "Row counting function If row_number > = 2 And row_number
ElseIf
ElseIf makes it possible to add additional conditions after the IF command:
If [CONDITION 1] Then "=> IF condition 1 is true, THEN "Instructions 1 ElseIf [CONDITION 2] Then "=> IF condition 1 is false, but condition 2 is true, THEN "Instructions 2 Else "=> ELSE "Instructions 3 End If
If CONDITION 1 performed, Instruction 1 will be executed and leave the statement IF(which starts with IF and ends with End If). If CONDITION 2 takes the value " lie ", then it will be executed Instruction 2, and if it in turn returns " lie ", Then Instruction 3(under Else) will be executed.
Sub scores_comment () "Variables Dim note As Integer, score_comment As String note = Range ("A1") "Comments based on the score received If note = 6 Then score_comment = "Great ball!" ElseIf note = 5 Then score_comment = "Good score" ElseIf note = 4 Then score_comment = "Satisfactory score" ElseIf note = 3 Then score_comment = "Unsatisfactory score" ElseIf note = 2 Then score_comment = "Bad score" ElseIf note = 1 Then score_comment = "Terrible score" Else score_comment = "Zero score" End If "Comment in cell B1 Range ("B1") = score_comment End Sub
Select
There is an alternative to using If with many ElseIf instructions, namely the command Select, which is more suitable for these types of situations.
Let's look at an example of a macro with the operator Select:
Sub scores_comment () "Variables Dim note As Integer , score_comment As String note = Range ("A1") "Comments based on the received score Select Case note "
It's worth noting that we could also use other comparison operators:
Case Is >=6 "if value >=6
Examples with different meanings:
Case Is = 6, 7 "if value = 6 or 7 Case Is 6, 7 "if value is not 6 or 7 Case 6 To 10 "if value = any number from 6 to 10
The most important conditional operators used in Excel VBA are the operators If...Then And Select Case. Both of these expressions test one or more conditions and, depending on the result, will perform different actions. We'll talk more about these two conditional operators next.
"If...Then" Statement in Visual Basic
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 that should be performed if the condition is false (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 if 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 necessary.
Below is an example in which, using the operator If...Then The fill color of the active cell changes depending on the value it contains:
If ActiveCell.Value< 5 Then ActiveCell.Interior.Color = 65280 "Ячейка окрашивается в green color ElseIf ActiveCell.Value< 10 Then ActiveCell.Interior.Color = 49407 "Ячейка окрашивается в оранжевый цвет Else ActiveCell.Interior.Color = 255 "Ячейка окрашивается в красный цвет End If
Note that once 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 is colored green. After this, execution of the statement If...Then is interrupted and the remaining conditions are not checked.
Select Case Statement in Visual Basic
Operator Select Case similar to operator If...Then in that it 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 if the result of the Expression matches Value1
Case Value2
Actions if the result of the Expression matches Value2
…
Case Else
Actions if the result of the Expression does not match any of the listed options Values
End Select
Element Case Else is optional, but is recommended to handle unexpected values.
In the following example, using the construct 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 example above shows how you can set a value for an element in different ways Case in design Select Case. These are the methods:
Case Is<= 5 | Thus, using the keyword Case Is you can check if the value satisfies Expressions condition of the form <=5 . |
Case 6, 7, 8, 9 | This way you can check if the value matches Expressions with one of the listed values. The listed values are separated by commas. |
Case 10 | This checks if the value matches Expressions with a given value. |
Case 11 To 20 | This way you can write an expression to check if the value satisfies Expressions condition of the form 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 options listed 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.
Control structures allow you to control the execution sequence of a program. Without control statements, all program statements will be executed from left to right and top to bottom. However, sometimes you want to repeatedly execute a set of instructions automatically, or solve a problem differently depending on the value of variables or parameters specified by the user at run time. Control constructs and loops are used for this purpose.
VBA supports the following decision constructs:
If. . . Then. . . Else
6.1 If construction. . . Then
If construct. . . Then is used when it is necessary to execute one or a group of statements depending on some condition. The syntax of this construct allows you to specify it in one line or in several lines of the program:
If condition Then expression If condition Then expression End If
Typically the condition is a simple comparison, but it can be any expression with a calculated value. This value is interpreted as False if it is null, and any non-null value is treated as True. If the condition is true, then all expressions after the Then keyword are executed. To conditionally execute a single statement, you can use either single-line syntax or multi-line syntax (block construction).
The following two statements are equivalent:
If anyDate< Now Then anyDate = Now If anyDate < Now Then anyDate = Now End If
Note that the syntax of the If statement is . . . Then for one line does not use the End If statement. To execute a sequence of statements if a condition is true, you should use an If block construct. . . 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 construct).
6.2 If construction. . . 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 it is false, VBA checks the next condition2, and so on, until it finds a true condition. Once VBA finds it, it executes the appropriate block of statements and then transfers control to the statement following the End if statement. This construct can include an Else statement block that VBA executes if none of the conditions are met.
If construct. . . Then. . . ElseIf is really just a special case of the If construct. . . Then. . . Else. Note that this construction can have any number of ElseIf blocks, or even none. An Else block can be included regardless of the presence or, conversely, absence of ElseIf blocks.
Sub example1() 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 entry", 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 the If clause. . . Then. However, the number of Elself blocks can become so large that the If construct. . . Then it will become very cumbersome and uncomfortable. In such a situation, another decision-making structure should be used - Select Case.
6.3 Select Case design
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 tested_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 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 from all found matches 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 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 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.
The use of the conditional operator is an integral part of almost any programming language. And in fact, everything comes from mathematics, since initially, programming languages were formed as tools for solving various problems. I’ll say right away that the article is boring and uninteresting. It’s simply physically impossible to squeeze anything new out of programming, in particular from the description of operators, but I’ll come up with something. And so, in the VBA language, the if statement is used to check conditions, which can exist in several variations. But first, a little humor...
A hare runs through the forest and we see a bear reading a book. The hare asks: “Clubfoot, what are you reading?” He answers with a smart look: “Logic.” Kosoy, looking surprised, asks, “What is logic?” “Well, look,” says the bear:
- M: Do you have matches?
- Z: No
- M: So you don't smoke
- Z: Yes!
- M: If you don’t smoke, then there’s no extra spending money
- Z: Right!
- M: So you go after girls?
- Z: Of course!
- M: And since you go after girls, it means you are not impotent!
- Z: Correct again!
The hare was delighted with such a clever book and asked the clubfoot to read it. He walks through the forest contentedly and rejoices, then a wolf comes towards him. The hare decided to try logic on gray and asks him:
- Z: Do you have matches?
- B: Yes
- Z: So you are impotent!
Well, yes, I retold the joke as I remembered
Okay, now let's get into the theory...
Option 1. The simplest use of the VBA if conditional statement is when a condition is checked, and if it is true, then one expression is executed:
If condition then expression
Option 2. A situation may arise when, after checking a condition, several expressions need to be executed, in which case the if statement takes the form:
If condition then
Expression 1
Expression 2
Expression N
As you can see, in this case the closing block if ... end if is used; it is also relevant in the case when statements are nested.
Option 3: To avoid creating nested conditional statements, use the following construction:
If condition then
Expression 1
Expression 2
Everything here is extremely simple: if the condition is true, the first expression is executed, if not, the second. It is worth remembering that after checking the condition, several statements may follow, as in the option above.
Option 4: Checking multiple conditions:
If condition 1 then
Expressions 1
ElseIf condition 2 then
Expressions 2
ElseIf condition 3 then
Expressions 3
The logic of the operation is as follows: first, the first condition is checked, if it is true, then the first expressions are executed and then the transition to the operator occurs, which follows End If. If the first condition is not met, the second is checked, and so on, the use of an Else expression at the end is optional, it can be followed by a statement that must be executed if none of the previous conditions are met.
Okay, now let's write a simple example of using the VBA if then construct. Create a form in the Visual Basic for Applications editor and add three text labels () and one button () to its surface. In the code editor we will write:
Private Sub GetSub() Dim MySumm As Integer Dim MyVar As Byte MyVar = 0 Do While MySumm<= 10000 MySumm = MySumm + 1 If MySumm = 10 Then Label1.Caption = "The amount has reached 10" ElseIf MySumm = 100 Then Label2.Caption = "The amount has reached 100" ElseIf MySumm = 1000 Then Label3.Caption = "The amount has reached 1000" Else Label4.Caption = "Value reached"& MySumm End If Loop End Sub Private Sub CommandButton1_Click() Call GetSub End Sub Private Sub UserForm_Initialize() Label1.Caption = "" Label1.FontSize = 13 Label1.ForeColor = vbRed Label2.Caption = "" Label2.FontSize = 13 Label2. ForeColor = vbGreen Label3.Caption = "" Label3.FontSize = 13 Label3.ForeColor = vbBlue Label4.Caption = "" Label4.FontSize = 13 CommandButton1.Caption = "Run" End Sub |
In the GetSub procedure, a loop is used, the condition for executing the loop is that the value of the MySumm variable must be less than or exactly 10000. The body of the loop itself uses VBA if then statements with several options. The conditions are checked: if the amount reaches the value of 10, then write one information in the first text field, if 100 - another information, if 1000 - the third information, otherwise - write the data in the Caption property of the fourth text field.
So far, we've looked at procedures and functions that VBA executes in a linear order - VBA starts executing code at the first statement after the procedure (function) declaration line and continues executing each statement line by line until the End Sub (End Function) statement is reached. . Such a linear algorithm can be represented by the following block diagram:
However, it is quite common to encounter situations where you need procedures or functions to perform different actions under different conditions. The following block diagrams illustrate this situation:
Operators that perform the role of branching a program based on some condition are called conditional jump operators.
The simplest conditional jump operators are the operators If..Then; If..Then..Else. The first statement allows you to select a single procedure branch (left flowchart), while the second allows you to select from two alternative procedure code branches (right flowchart) based on an evaluation of whether the condition is True or False.
Syntax If..Then
Option 1:
If Condition Then Statements
Option 2:
If Condition Then
Statements
End If
Condition- logical expression;
Statements
The first option requires writing the statement on one line, while in the Statements section you can specify several statements, separating them with a colon.
The operating logic is as follows: VBA first evaluates the logical expression presented in the Condition section; if this logical expression is True then the statement(s) of the Statements section is executed; VBA then continues executing the code following the If..Then line (Option 1) or the End If keywords (Option 2). If the logical expression is False, then the statements of the Statements section are skipped and the code following this section is executed.
Below is a listing of the elementary use of the first spelling of If..Then:
And now - the second option:
Please note that the statements in the Statements section are written using indentation (tab stops are used). This is an important point. You should immediately accustom yourself to formatting your program code in this way - this increases its readability and greatly facilitates the search for errors.
Syntax If..Then..Else
Option 1:
If Condition Then Statements Else ElseStatements
Option 2:
If Condition Then
Statements
Else
ElseStatements
End If
Condition- logical expression;
Statements, ElseStatements- one, several or no VBA statements.
By analogy with If..Then - the first option requires writing a statement in one line, while in the Statements and ElseStatements sections you can specify several statements, separating them with a colon.
The second option allows you to specify multiple statements on different lines.
The operating logic is as follows: VBA first evaluates the logical expression presented in the Condition section; if this logical expression is True then the statement(s) of the Statements section is executed; VBA then continues executing the code following the If..Then line (Option 1) or the End If keywords (Option 2). If the logical expression is False, then the statements of the ElseStatements section are executed.