VBA branch operators: a simple choice. Visual Basic Vba control constructs if then otherwise rules

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 transferred to 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.

Decision making allows programmers to control the flow of execution of a script or one of its sections. Execution is controlled by one or more conditional statements.

Below is the general form a typical decision-making structure found in most programming languages.

VBA provides the following types of solutions. Click the following links to check their details.

If ... Then - If Then

An If statement consists of a Boolean expression followed by one or more statements. If the condition is called True, the statements in the If (s) condition are fulfilled. If the condition is called False, the statements after the If loop are executed.

Syntax

Following is the syntax of the If statement in VBScript.

If (boolean_expression) Then Statement 1 ..... ..... Statement n End If

Flow diagram

example

Private Sub if_demo_Click () Dim x As Integer Dim y As Integer x = 234 y = 32 If x> y Then MsgBox "X is Greater than Y" End If End Sub

X is Greater than Y

If the statement consists of a Boolean expression, one or more operators follow.

if..else statement

An If statement consists of a Boolean expression followed by one or more statements. If the condition is called True, the statements in the If (s) condition are fulfilled. If the condition is called False, the statements in the Else Part section are executed.

Syntax

Following is the syntax of the If Else statement in VBScript.

If (boolean_expression) Then Statement 1 ..... ..... Statement n Else Statement 1 ..... .... Statement n End If

Flow diagram

example

For demonstration purpose, let's find the largest of two Excel numbers using a function.

Private Sub if_demo_Click () Dim x As Integer Dim y As Integer x = 234 y = 324 If x> y Then MsgBox "X is Greater than Y" Else Msgbox "Y is Greater than X" End If End Sub

When the above code is executed, it produces the following result.

Y is Greater than X

If another statement consists of a Boolean expression followed by one or more operators. If the condition is True, the statements in the If statements are executed. If the condition is false, the Else part of the script is executed.

if ... elseif..else statement

An If statement, followed by one or more ElseIf statements that consist of boolean expressions, and then followed by a default else statement that is executed when the entire condition is false.

Syntax

Following is the syntax of the If Elseif-Else statement in VBScript.

If (boolean_expression) Then Statement 1 ..... ..... Statement n ElseIf (boolean_expression) Then Statement 1 ..... .... Statement n ElseIf (boolean_expression) Then Statement 1 ...... ... Statement n Else Statement 1 ..... .... Statement n End If

Flow diagram

example

For demonstration purpose, let's find the largest of two Excel numbers using a function.

Private Sub if_demo_Click () Dim x As Integer Dim y As Integer x = 234 y = 234 If x> y Then MsgBox "X is Greater than Y" ElseIf y> x Then Msgbox "Y is Greater than X" Else Msgbox "X and Y are EQUAL "End If End Sub

When the above code is executed, it produces the following result.

X and Y are EQUAL

If the statement is followed by one or more ELSEIF statements, which consists of logical expressions, and then followed by an optional still statement, which is executed when all conditions become false.

nested if statements

An If or ElseIf statement within another If or ElseIf statement. Inner If statements are executed based on outer If statements. This allows VBScript to easily deal with complex conditions.

Syntax

Following is the syntax of the Nested If statement in VBScript.

If (boolean_expression) Then Statement 1 ..... ..... Statement n If (boolean_expression) Then Statement 1 ..... ..... Statement n ElseIf (boolean_expression) Then Statement 1 ..... .... Statement n Else Statement 1 ..... .... Statement n End If Else Statement 1 Statement n End If

example

For demonstration purposes, let's find the type of a positive number using a function.

Private Sub nested_if_demo_Click () Dim a As Integer a = 23 If a> 0 Then MsgBox "The Number is a POSITIVE Number" If a = 1 Then MsgBox "The Number is Neither Prime NOR Composite" ElseIf a = 2 Then MsgBox "The Number is the Only Even Prime Number "ElseIf a = 3 Then MsgBox" The Number is the Least Odd Prime Number "Else MsgBox" The Number is NOT 0,1,2 or 3 "End If ElseIf a< 0 Then MsgBox "The Number is a NEGATIVE Number" Else MsgBox "The Number is ZERO" End If End Sub

When the above code is executed, it produces the following result.

The Number is a POSITIVE Number
The Number is NOT 0,1,2 or 3

If or ElseIf statement is inside another, if or ELSEIF statement.

switch statement

When a user wants to execute a group of statements depending on the value of an expression, a switch case is used. Each value is called a case, and a variable is included on a case-by-case basis. The Case Else statement is executed if the test expression does not match any of the user-specified cases.

Case Else is an optional statement in Select Case, however there is always a Case Else statement for good programming practice.

Syntax

Following is the syntax for Switch statement in VBScript.

Select Case expression Case expressionlist1 statement1 statement2 .... .... statement1n Case expressionlist2 statement1 statement2 .... .... Case expressionlistn statement1 statement2 .... .... Case Else elsestatement1 elsestatement2 .... .. .. End Select

example

For demonstration purposes, let's find the type of an integer using a function.

Private Sub switch_demo_Click () Dim MyVar As Integer MyVar = 1 Select Case MyVar Case 1 MsgBox "The Number is the Least Composite Number" Case 2 MsgBox "The Number is the only Even Prime Number" Case 3 MsgBox "The Number is the Least Odd Prime Number "Case Else MsgBox" Unknown Number "End Select End Sub

When the above code is executed, it produces the following result.

The Number is the Least Composite Number

A switch statement allows a variable to be checked for equality against a list of values.

Basic VBA control structures:

Sub TestForEachNextRangeChanges ()

For Each iCell In Range ("A1: C5")

i = i + 1: iCell.Value = i

If iCell.Value 5 Then
iCell.Font.Size = 15
iCell.Interior.ColorIndex = 50
ElseIf iCell.Value = 13 Or iCell.Value = 5 Then
iCell.Font.Size = 20
iCell.Interior.ColorIndex = 3
Else
iCell.Clear
End If

Pay attention to:
- in this VBA code, to check the values ​​in the cell, we applied the already known If Then Else statement.
- depending on which condition passed the check, a certain piece of code is executed that uses various properties of the Range object.

An example with the WorkSheets family:

Sub TestForEachNextSheet ()

For Each iList In Worksheets
i = i + 1
iNameList = iNameList & Chr (10) & iList.Name
Next

MsgBox "Number of worksheets:" & i & Chr (10) & _
"Worksheet names:" & iNameList

In this case, using such a count of the number of worksheets is not the best way, since this object also has a property that will cope with this task much better.

3.5.1 Conditional and unconditional branch operators. Operator If ...Then ... Else

Checking conditions in VBA, If ... Then ... Else statement, nested If statements

Conditional jump operators are some of the most important and frequently used elements in programming languages. General principle Their work is simple: the compliance with some conditions is checked (the truth or falsity of any expressions) and, depending on this, the program execution is directed along one or another branch. VBA provides two conditional branch statements: If… Then ... Else and Select Case.

Operator If… Then... Else is the most popular with programmers. Its full syntax looks like this:

If Condition Then
Teams1

Wherein:

• Condition- an expression that is tested for truth. If it is true, then Commands1 are executed, if false - Commands2;
• ConditionsN- additional conditions that can also be checked. If they are executed (the expression ConditionN is true), then CommandsN are executed.

The If ... Then ... Else statement is applied:

• when you need to check for compliance with one condition and, in case of compliance, do some action:

If nTemperature< 10 Then

MsgBox "Put on a jacket"

• when you need to do the same as in the previous example, and in case of inconsistency, perform another action:

If nTemperature< 10 Then

MsgBox "Put on a jacket"

MsgBox "Put on a windbreaker"

• when you need to check for compliance with several conditions (note the use of boolean operators):

If (nTemperature< 10) And (bRain = True) Then

MsgBox "Put on a jacket and take an umbrella"

• when, if the first check returned False, you need to check for compliance with several more conditions (in this case, it is convenient to use ElseIf):

If (bIGoInCar = True) Then

MsgBox "Dress for the car"

ElseIf nTemperature< 10 Then

MsgBox "Put on a jacket"

MsgBox "You can wear a shirt"

In this example, since bIGoInCar is a Boolean variable and itself evaluates to True or False, the first line might look like this:

If bIGoInCar Then ...

Some notes on using If ... Then ... Else:

• the Then keyword must appear on the same line as the If and the condition. If you drag it to the next line, you get an error message;
• if you place the command that needs to be executed if the checked condition is true, on the same line with If and Then, then End If can be omitted:

If nTemperature< 10 Then MsgBox "Одеть куртку"

• if you use several commands or Else / ElseIf constructions, then End If must be written at the end, otherwise a syntax error will occur.
• for the If… Then expression, it is strongly recommended that you use indentation to highlight command blocks. Otherwise, it will be difficult to read the code.
• If ... Then statements can be nested within each other:

If MyVar = 5 Then

MsgBox “MyVar = 5”

If MyVar = 10 Then

MsgBox “MyVar = 10”

Basic VBA Operators

Comments (0)

3.1. Operator recording rules

When recording operators, you must adhere to the following rules:

Each new statement is written on a new line.

To write multiple operators on one line, separate them with a colon (:).

If the operator does not fit on one line, then you must put a space and an underscore (_) at the end of the line, and then continue the part that does not fit on the next line.

3.2. Assignment operator

The assignment operator is used when a variable needs to be assigned a new value. It has the following syntax:

VARIABLE NAME= EXPRESSION

The expression on the right side is evaluated first, and then the result is assigned to the variable on the left side.

For example... Write the following mathematical expression using the assignment operator:

In VBA, this expression can be written as the following operator:

Y = a ^ (1/3) + (a ^ 2 + Exp (-b)) / (Sin (a) ^ 2-Log (b))

3 .3. I / O operators

3 .3.1. MsgBox operator and function

Operator MsgBox carries out information output in the dialog box and sets the waiting mode for pressing the button by the user.

It has the following syntax:

MsgBox Message [, Buttons] [, Title]

Arguments:

Message - required argument specifying the output in the window Announcement... Can consist of several text lines, concatenated by a sign & ... Usage in this argument Chr (13) leads to a new line when displaying information.

Buttons - the value of this argument determines the categories of buttons that appear in the window. The value of the argument of the button also determines whether any icon appears in the window. If you do not specify which buttons should be displayed in the message window, then the default value corresponding to the OK button is used. Table 3.1 shows the possible combinations of buttons and icons in the message window.

Heading - sets the title of the window.

Function MsgBox returns an Integer indicating which button was clicked in the dialog box.

Table 3.1. Valid values ​​for a button variable

For example... Display a message about the current date.

MsgBox "Today is on the calendar" & Date, "Attention"

As a result, the following window will be displayed (Figure 3.1).

After clicking the OK button, the message box will close and the program will resume execution from the statement immediately behind the call to MsgBox.

3.3.2. Function InputBox

Function InputBox enters the values ​​of variables using the input window and has the following syntax:

VariableName = InputBox (Message [, Title] )

Arguments:

Message - required argument. Sets an informational message in the window, usually explaining the meaning of the entered value

Heading - sets the title of the window.

For example, Enter the value of variable N from the keyboard, with a default value of 10.

To do this, you can use the following operator:

N = InputBox ("Enter N", "Input of initial data", 10)

As a result, the following window will be displayed for entering the value of the variable N (Figure 3.2).

If the default value suits the user, then after clicking the OK button, the input window will close, the variable N will be assigned the value 10, and the program execution will resume from the operator immediately behind the call. InputBox.

If the default value does not suit the user, then before clicking the OK button, you must enter the desired value for the N variable.

3 .4. Conditional IF statement

To implement the branching computational process in VBA, the operator If… Then… Else, which is the simplest form of condition checking. It has the following syntax:

IfCONDITIONThenOPERATOR_1ElseOPERATOR_2

OPERATOR_1 is executed if CONDITION true, otherwise executed OPERATOR_2. In this case, the If… Then… Else statement is written in one line.

CONDITION is a boolean expression. The result of an expression is always boolean. Expression can be simple or complex. When recording simple conditions all possible operations of the relation indicated in table can be used. 3.2.

table3 .2. Logical relationship

Complex conditions are formed from simple ones by using logical operations and parentheses. The list of logical operations is given in table. 3.3.

table3 .3. Logical operations

In a conditional statement, it is allowed to use a statement block instead of any of the statements. In this case, the conditional operator looks like this:

IfCONDITIONThen

OPERATOR_BLOCK_1

OPERATOR_BLOCK_2

End If

Several conditions can be checked in a conditional statement. In this case, the conditional operator looks like this:

IfCONDITION_1Then

OPERATOR_BLOCK_1

ElseIfCONDITION_2Then

OPERATOR_BLOCK_2

Else

EndIf

Example 1... Write part of the program for the algorithm in Fig. 3.3.

Example 2. Write part of the program for the algorithm in Fig. 3.4.

3.5. Select Case statement

The Select Case statement is convenient to use when, depending on the value of an expression that has a finite set of valid values, you need to perform different actions. It also refers to conditional statements, but has a different form:

Select CaseTESTED_EXPRESSION

CaseVALUES_1

OPERATORS_1

CaseVALUES_ 2

OPERATORS_ 2

. . .

CaseVALUES_N

OPERATORS_N

[ CaseElse

OTHERWISE_ OPERATORS]

End Select

TESTED_EXPRESSION can be of any scalar type except real. VALUES consist of an arbitrary number of values ​​or ranges, separated by commas.

Type of VALUES must match the type TESTED_EXPRESSION.

First, it computes TESTED_EXPRESSION... If its value matches one of the values VALUES_I, then OPERATORS_I End Select... If its value does not match any of the values VALUES_I, then OTHERWISE_OPERATORS and control is transferred to the operator standing after End Select

For example. Write part of the program for the algorithm in Fig. 3.5, which determines the value of the variable S depending on the value of the variable n.

3.6. Loop Operators

To implement a cyclic computational process, i.e., multiple execution of one or more operators, the loop operator is used For… Next which has the following syntax:

ForCOUNTER = START_VALUEToCON_VALUEStepSTEP

OPERATOR BLOCK

OPERATOR BLOCK

NextCOUNTER

The For ... Next loop iterates over the values ​​of the variable COUNTER, which is a parameter of the cycle, from the start to the end value with the specified step of change. This ensures that the block of statements of the loop body is executed at each new value of the counter. If StepSTEP is absent in the construction, then by default it is assumed that the step is 1. According to the operator Exit For it is possible to exit the loop statement before COUNTER reaches the last value. *

To iterate over objects from a group of similar objects, for example, cells from a range or array elements, it is convenient to use the loop operator For ...Each… Next.

For EachElementInGroup

BLOCK_ OPERATORS

OPERATOR BLOCK

NextElement

In VBA, other loop operators are used to organize loops with a predetermined number of repetitions:

loops with precondition - DoWhile … Loop,

DoUntil … Loop;

loops with postcondition - Do … LoopWhile,

Do … LoopUntil.

Following is the syntax for these loop statements:

"Loop with preconditionDo While … Loop

Do whileCONDITION

OPERATOR BLOCK

OPERATOR BLOCK

"Loop with preconditionDo Until … Loop

DoUntilCONDITION

OPERATOR BLOCK

OPERATOR BLOCK

"Loop with postconditionDo … Loop while

BLOCK_ OPERATORS

OPERATOR BLOCK

Loop whileCONDITION

"Loop with postconditionDo … Loop Until

BLOCK_ OPERATORS

OPERATOR BLOCK

Loop UntilCONDITION

Operator DoWhile ... Loop provides multiple repetitions of a block of statements as long as CONDITION is respected, and the operator

Other news