Often, at a certain place in the program, it is necessary to execute certain operators, depending on some conditions. This possibility in Visual Basic is implemented using so-called control structures (or structures), which in turn consist of decision-making structures and loops. Following is the detailed description two existing decision-making constructs.

If: Then construct. There are several variations of this structure. If, when a certain condition is fulfilled, one operator must be executed, then you need to use a construction of the following form:

If condition Then statement

In the case when the result of checking the condition is the value True (true), then the statement located after the service word Then is executed. On the other hand, if after checking the condition, the value False was obtained, then execute the next statement in order. All described parameters of this structure must be specified in one line.

If, when the condition is met, it is required to execute not one, but several operators, then you should use the following construction:

If conditions Then Statements End If

If the checked condition is true, the operators located after keyword Then. On the other hand, if the condition is false, then the next statement after the given construction is executed. In the case when there is only one operator in the statement block, then this structure must still end with the service phrase End If.

If it is necessary to execute this or that statement (or block of statements) depending on the result of checking a certain condition, the following construction should be used in the Visual Basic language:

If conditions Then Statements1 Else Statements2 End If

If condition Then Statements1 Else: Statements2 End If

If the result of the condition check is True, then the block of statements1 located after the keyword Then will be executed. On the other hand, if the condition check yielded False, then the statement2 block located after the Else service word will be executed.

In the second of the above options, as a block operators2 can be used as one operator (then it is written after the ":" sign in the same line as the service word Else), and several (in this case, each operator, starting with the second, is written in on a separate line).

In the case when a certain action (or set of actions) needs to be performed after checking not one, but several conditions in the Visual Basic language, use the following control structure:

If condition Then Statements1 ElseIf condition2 Then Statements2. ... End If

If condition1 after the If keyword is true, then the statement1 block after Then is executed. If it is false, then the condition2 located after the service word ElseIf is checked, if it is true, the block of statements2 is executed, etc. If none of the conditions is true, that is, the result of all tests is False, then the N statements block located after the Else keyword will be executed (this block is optional).

In addition to the above If: Then structure, you should also consider the IIf function, which returns one of two values, depending on the condition being tested.

The syntax for this function is:

IIf (condition, value1, value2)

In the case when the result of checking the condition is True, the function returns value1, and when the check is False, the result is returned - value2. For example:

Dim intA As Integer, strA As String intA = 6 strA = IIf (intA Mod 2 = 0, "Even", "Not even")

If the number intA is divisible by 2 without a remainder, then the string strA will be assigned the value "Even", otherwise - "Not even".

Select: Case construction. When there are several statements (or blocks of statements) that must be executed in the event that a particular condition is true, then the record of the If: Then construction will be quite cumbersome. Therefore, in such cases, you should use the Select: Case structure, which improves the readability of the program. Its construction is as follows:

Select Case variable Case values1 Operators1 Case values2 Operators2. ... ... End Select

If the variable contains the value1, located after the first in order of the keyword Case, then the block of statements1 is executed. On the other hand, if the content of the variable is equal to value2, then the statement block2 is executed, and so on. When the contents of the variable are not equal to any of the given values, then the block of statements N is executed, which is located after the service phrase Case: Else, which is optional in the considered construction.

In the case when it is necessary to execute the same statement (block of statements) for several values ​​of a variable, then the list of these values ​​must be specified after the keyword Case, separated by commas.

For example:

Select Case x Case 1 x = x + 1 Case 2, 3, 4 x = 10 Case Else x = 20 End Select

Conditional operator

Conditional operator VBA allows you to check a certain condition and, depending on the results of the check, perform one or another action. Thus, conditional operator Is a means of branching a computational process.

In VBA there are 2 types of conditional operator: linear and block.

1. Linear conditional operator is used to execute any one operator if some condition is true.

The syntactic construction of a linear operator has two forms: no alternative, alternative.

The structure of the uncontested conditional operator (shorthand):

If<условие>Then<оператор 1>

The structure of the alternative conditional operator ( full version):

If<условие>Then<оператор 1>Else<оператор 2>

where

If, Then, Else - reserved words (if, then, otherwise);

<условие>- an arbitrary expression of a logical type;

<оператор 1>, <оператор 2>- any language operators VBA.

Job. The conditional expression is evaluated first<условие>... If there is a result True (true), then it is executed<оператор 1>, a<оператор 2>skipped. If there is a result False (false), on the contrary,<оператор 1>skipped but executed<оператор 2>.

2. Block conditional operator is used if the condition is true, it is necessary to execute several program statements (statement block). The block operator has two forms: non-alternative, alternative.

The structure of the uncontested block operator (shorthand)

If<условие>Then

<оператор1>

<оператор2>

…………….

<оператор n >

End If

where

End If - indicates the end of the operator block If.

Alternative block operator structure:

If<условие>Then

<оператор1>

<оператор2>

…………….

<оператор n >

Else

<оператор1>

<оператор2>

…………….

<оператор n >

End If

Example 1.

Formulation of the problem. Create a custom procedure for calculating an equation of the form in a standard module ax 2 + bx + c = 0.

1. Initial data:

a, b, c  R

Result: x1, x2  R.

2. Type the following custom procedure in the standard project module:

Private Sub yravnenie ()

a = InputBox ("a =", a)

b = InputBox ("b =", b)

c = InputBox ("c =", c)

d = b ^ 2 - 4 * a * c

If d> = 0 Then

x1 = (-b + Sqr (d)) / (2 * a)

x2 = (-b - Sqr (d)) / (2 * a)

MsgBox (x1)

MsgBox (x2)

Else

MsgBox ("No solutions")

End If

End Sub

Alternative block operator If it is used in cases when, when a condition is met, it is necessary to implement one set of program statements, and if it is not met, another set.

IF statements can be nested within each other. Such operator nesting is used if you need to check some condition under another condition that is true.

Nested operator format If:

If<условие1>Then

If<условие2>Then

<оператор1>

<оператор2>

…………….

<оператор n >

Else

<оператор1>

<оператор2>

…………….

< оператор n>

End If

End If

Example 2.

Formulation of the problem. Create in standard module custom function finding the maximum among three given numbers y 1 = a + 2 * b; y 2 = a * b + c; y 3 = c 2 + 1.

Task execution technology:

1. Initial data:

a, b, c  R

Result: Max  R.

2. Type the following user-defined function in the standard project module:

Function y (a, b, c)

y1 = a + 2 * b

y2 = a * b + c

y3 = c ^ 2 + 1

If y1> y2 Then

If y1> y3 Then y = y1 Else y = y3

Else

If y2> y3 Then y = y2 Else y = y3

End If

End Function

3. Calculate the roots of a quadratic equation for arbitrary input data.

When using nested operators If it is important not to confuse the combinations of conditions. The rule to remember is: the alternative Else considered to belong to the nearest operator If having no branch Else.

In VBA Designed for multi-operator operation If ... These operators are used in cases where it is necessary to consider several more conditions in addition to the original one. For this, the structure is used: If… Then… ElseIf ... Unlike nested operators, the multi-operator construct If allows you to check additional condition if the initial condition takes the value False.

Recording format:

If<условие1>Then

<оператор1>

ElseIf<условие2>Then

<оператор2>

Else

<.оператор3>

EndIf

Example 3.

Formulation of the problem. The sales manager needs to develop a function to calculate commissions. The percentage of commission depends on the volume of goods sold and is calculated according to the following rule, presented in table 15.

Commission calculation rules

Sales volume per week, p.	Commission, %
0 to 9999
10000 to 19999
20,000 to 39999
More than 40,000

Task execution technology:

1. Initial data:

Sales  Z.

Result: Commission R.

2. Build a custom function for calculating commissions in a standard module:

Function Commission (Sales)

If Sales<= 9999 Then

Commissions = Sales * 0.08

ElseIf Sales<= 19999 Then

Commissions = Sales * 0.1

ElseIf Sales<= 39999 Then

Commissions = Sales * 0.12

Else

Commissions = Sales * 0.14

End If

End Function

3. Calculate.

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 the user to press the button.

It has the following syntax:

MsgBox Message [, Buttons] [, Title]

Arguments:

Message - obligatory argument specifying the information message to be displayed in the window. 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

Display	Argument
OK button
OK and Cancel buttons
Yes and No buttons
Yes, No and Cancel buttons
Stop, Redo, and Ignore buttons	VbAbortRetryIgnore
Redo and Cancel buttons.
Information sign
Question mark
Exclamation mark

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 program execution will resume from the statement immediately behind the call to MsgBox.

3.3.2. Function InputBox

Function InputBox enters variable values ​​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 the 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 on the OK button, you must enter the desired value for the variable N.

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 writing simple conditions, all possible operations of the relationship indicated in Table 1 can be used. 3.2.

table3 .2. Logical relationship

Operation	Name	Expression	Result
			True if A is equal to B
			True if A is not equal to B
			True if A is greater than B
			True if A is less than B
	More or equal		True if A is greater than or equal to B
	Less than or equal to		True if A is less than or equal to B

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

	Name	Expression			Result
	Logical negation
	Logical AND
	Logical OR

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

IfCONDITIONThen

OPERATOR_BLOCK_1

OPERATOR_BLOCK_2

End If

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

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 some 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 ELSE_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

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

Checking Conditions in VBA, If ... Then ... Else Statement, Nested If Constructs

Conditional jump operators are some of the most important and commonly used elements in programming languages. The general principle of 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 applies:

• 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 be on the same line as the If and the condition. If you move it to the next line, an error message will be displayed;
• 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”

Single-line and multi-line If ... Then ... Else statement constructs and IIf function used in Excel VBA code - syntax, components, examples.

If ... Then ... Else Statement

Operator If ... Then ... Else is designed to transfer control to one of the operator blocks, depending on the results of the checked conditions.

One-line construct

Operator If ... Then ... Else can be used in a one-liner without keywords Else, End If.

The syntax of the one-line If ... Then ...

If[condition] Then[operators]

One-Line Components If ... Then ...

• condition True or False;
• operators condition returns the value True;

If the component condition returns the value False, the block of operators of the construction If ... Then ...

Example 1

Sub primer1 () Dim d As Integer, a As String d = InputBox ("Enter a number from 1 to 20", "Example 1", 1) If d> 10 Then a = "Number" & d & "is greater than 10" MsgBox a End Sub

Multi-line construction

If ... Then ... Else Multi-Line Syntax

If[condition] Then[operators] ElseIf[condition] Then[operators] ---------------- Else[operators] End If

Components of a multi-line construct If ... Then ... Else:

• condition- numeric or string expression following the keyword If or ElseIf and returning boolean True or False;
• operators- a block of statements of VBA Excel code that is executed if the component condition returns the value True.
• dotted line denotes additional building blocks from a string ElseIf [condition] Then and the lines [operators];
• statement block after the keyword Else executed anyway, but a structural block of code from a line Else and the lines [operators] is optional and can be omitted.

If the component condition returns the value False followed by a block of operators of the construction If ... Then ... Else is skipped and program control is transferred to the next line of code.

The simplest version of a multi-line construction If ... Then ... Else:

If[condition] Then[operators] Else[operators] End If

Example 2

Sub primer2 () Dim d As Integer, a As String d = InputBox ("Enter a number from 1 to 40", "Example 2", 1) If d< 11 Then a = "Число " & d & " входит в первую десятку" ElseIf d >10 And d< 21 Then a = "Число " & d & " входит во вторую десятку" ElseIf d >20 And d< 31 Then a = "Число " & d & " входит в третью десятку" Else a = "Число " & d & " входит в четвертую десятку" End If MsgBox a End Sub

IIf function

Function IIf checks the specified condition and returns a value depending on the result of the check.

Function syntax

IIf([condition], [if True], [if False])

Components of the IIf function

• condition- a numeric or string expression that returns a boolean value True or False;
• if True IIf, if condition returned value True;
• if False- the value returned by the function IIf, if condition returned value False.

Example 3

Sub primer3 () Dim d As Integer, a As String d = InputBox ("Enter a number from 1 to 20", "Example 3", 1) a = IIf (d< 10, d & " - число однозначное", _ d & " - число двузначное") MsgBox a End Sub

When you click the "Cancel" button or close the InputBox dialog box with a cross from the examples, an error is generated, since in these cases it returns an empty string. Assigning an empty string to an Integer variable d raises an error. When you click the "OK" button of the dialog box, the numbers entered in the input field in text format VBA Excel automatically converts the d variable to the number format.