vba excel statements. Description of VB (Visual Basic) statements. Incorrect transfer Correct transfer

VBA Operators: Arithmetic, Boolean, Comparisons, Assignments

A statement is the smallest unit of VBA code that can be executed. A statement can declare or define a variable, set a VBA compiler option, or perform some action in a program.

There are only 7 arithmetic operators in VBA. Four standard ones: addition (+), subtraction (-), multiplication (*), division (/) and three more:

• exponentiation (^), for example 2^3 = 8;
• Integer division (). Divides the first number by the second, discarding (not rounding) the fractional part. For example, 52 = 2;
• modulo division (Mod). Divides the first number by the second, returning only the remainder of the division. For example, 5 Mod 2 = 1.

The assignment operator in VBA is the equals sign. It can be written like this:

or even simpler:

In the second case, don't confuse the equals sign with the equals operator.

Expression

means "set the variable nVar to 10", and if the line looks like this:

it means "if the value of the variable nVar is equal to 10".

If you need to assign an object to a variable, then this is done in other ways.

There are only 8 comparison operators in VBA:

• equality (=), for example, If (nVar = 10);
• greater than and less than (> and
• greater than or equal to and less than or equal to (>= and
• · not equal (
• comparison of objects (Is). Determines whether object variables refer to the same object or to different ones, for example, If(obj1 is obj2);
• similarity (Like). Compares a string object to a pattern and determines if the pattern matches.

Comparison operators always return true or false -- true if the assertion is true and false if it is false.

A little about comparing string values:

• · when comparing string values, case is taken into account;
• spaces in string values ​​are also taken into account;
• · when comparing text strings by more/less, by default, just binary character codes are compared - which ones are more or less. If you want to use the order that goes in the alphabet, you can use the Option Compare Text command

A little more about the Like operator. Its general syntax looks like

Expression1 Like Expression2

In this case, Expression1 is any VBA text expression, and Expression2 is a template that is passed to the Like operator. You can use special wildcard characters in this pattern (see Table 1)

Tab. 1 Wildcards for the LIKE operator

Very often, when testing several conditions, logical operators are used:

• · AND -- logical AND, both conditions must be true;
• · OR -- logical OR, at least one of the conditions must be true;
• · NOT -- logical negation, returns TRUE if the condition is false;
• · XOR is a logical exception. In an E1 XOR expression, E2 returns TRUE if only E1 = TRUE or only E2 = TRUE otherwise FALSE;
• · EQV -- equivalence of two expressions, returns TRUE if they have the same value;
• · IMP -- implication, returns FALSE if E1 = TRUE and E2 = FALSE, otherwise -- TRUE.

You need to remember about AND, OR, NOT, other logical operators are rarely used.

Almost every VBA program uses concatenation operators. There are two of them in VBA -- + or &. It is recommended to always use & because:

• · when using &, the automatic conversion of numeric values ​​to strings is performed - there is no danger of making a mistake;
• · When using the + operator, adding a string value to a value of type Null yields Null. Example:

MsgBox "Message to user" & vUserName

The order in which operators are applied can be controlled using parentheses.

3 . Variables and data types

VBA Variables, Variable Declaration, Option Explicit, Naming Rules, VBA Data Types, Initial Variable Values

Variables are containers for storing mutable data. Almost no program can do without them. For simplicity, the variable can be compared with a number in the wardrobe - you hand over some data to the "cloakroom", in response you are given a number. When you need this data again, you "show the number" and get it. An example of working with variables in VBA might look like this:

Dim nMyAge As Integer

nMyAge = nMyAge + 10

Dim nMyAge As Integer

How to decrypt this line:

Dim is the scope of the variable. VBA provides 4 keywords for scoping variables:

• · Dim -- used in most cases. If a variable is declared as Dim in the declaration area of ​​a module, it will be available throughout the module, if in a procedure, only for the duration of that procedure;
• Private - when declaring variables in VBA, it means the same as Dim;
• · Public -- such a variable will be available to all procedures in all modules of this project if you declare it in the declaration area of ​​the module. If you declared it inside a procedure, it will behave like Dim/Private;
• · Static -- such variables can only be used within a procedure. These variables are visible only within the procedure in which they are declared, but they retain their value between different calls to that procedure. Usually used to accumulate some values. For example:

Static nVar1 As Integer

nVar1= nVar1 + 1

If there are no special requirements, then it makes sense to always choose the Dim scope.

The second word in our declaration (nMyAge) is the identifier (in other words, the name) of the variable. The rules for choosing names in VBA are the same for many elements (variables, constants, functions and procedures, etc.). Name:

• Must begin with a letter
• · must not contain spaces and punctuation characters (exception -- underscore);
• maximum length -- 255 characters;
• must be unique in the current scope (for more details, see below);
• · Reserved words (those that are highlighted in a different color in the code editor window) cannot be used.

When creating VBA programs, it is highly recommended that you decide on the rules by which names will be assigned to objects - a naming convention. The most commonly used is the so-called Hungarian convention (in honor of one of the Microsoft programmers, Charles Simonyi, an ethnic Hungarian):

• · The variable name must begin with a prefix written in lowercase letters. The prefix indicates what exactly will be stored in this variable:
  • o str (or s) -- String, character value;
  • o fn (or f) is a function;
  • o c (or capitalize all letters) -- constant;
  • o b -- Boolean, boolean value (true or false);
  • od -- date;
  • o obj (or o) -- reference to an object;
  • o n is a numeric value.
• The names of functions, methods, and each word in a compound word must begin with a capital letter:

MsgBox objMyDocument.Name

Sub CheckDateSub()

In early versions of VB there was no word Const - all constants were defined as variables, and to distinguish them they were written in capital letters, underscores were put between words: COMPANY_NAME

Many programmers still use this approach to designate constants (but the use of the Const keyword is now mandatory - more on that in the next section).

The third part of our declaration - As Integer - is an indication of the data type of our variable. The data type determines what kind of data can be stored in our variable.

VBA provides the following data types:

numeric (byte is an integer from 0 to 255, integer is an integer from -32768 to 32767, long is a large integer, currency (a large decimal number with 19 positions, including 4 positions after the decimal point), decimal (still greater decimal number with 29 positions), single and double -- floating point value (double twice));

Attention! Attempting to declare a variable of type Decimal (eg Dim n As Decimal) will result in a syntax error. To be able to work with the Decimal type, the variable must initially be declared as Variant or declared without a type at all (Dim n), since the Variant data type is used in VBA by default.

• string (string of variable length (up to about 2 billion characters) and fixed length (up to about 65400 characters);
• date and time (date -- from 01/01/100 to 12/31/9999);
• boolean (boolean -- can only store True and False values);
• object (object -- stores a link to any object in memory);
• · Variant is a special data type that can store any other data type.

You can also use custom data types, but you must first define them with a Type expression. Typically, user-defined data types are used as an additional means of validating user-entered values ​​(postal code is a classic example).

Some points related to the choice of data types for variables:

• · the general principle is to choose the smallest data type that can accommodate the values ​​you choose. When in doubt, choose a larger data type to avoid errors;
• · if possible, it is better not to use floating point data types (single and double). Working with such data types is slower, in addition, there may be problems with comparisons due to rounding;
• · If possible, it is better not to use the Variant type. This type is still cast by VBA to one of the other types, but it requires more memory. In addition, errors may occur during such implicit formation;
• · When defining variables, you can use the so-called type definition symbols (% -- integer, $ -- String, etc.). For example, in our example, you need to comment out the Dim nVar 1 As Integer line, and write in the second line:

nVar1% = nVar1% + 1

This approach is deprecated and not recommended.

When declaring variables, it is possible not to specify its type. For example, our declaration might look like this: Dim nVar1

In this case, the variable will be automatically declared with the Variant type.

In principle, you can work in VBA without declaring variables. For example, this code

nVar1= nVar1 + 1

will be fully functional. If we use a variable in a program without declaring it, a new variable of the Variant type will be automatically created. However, variables must be declared! And at the same time, it is desirable to explicitly specify the desired data type. Why:

• The number of errors is reduced: from the very beginning, the program will refuse to accept a value of the wrong type (for example, a string instead of a numeric one) into a variable;
• · When working with objects, hints on properties and methods are valid only when we initially declared an object variable with the desired type. For example, in Excel, the two code options will work the same way:

first option:

Dim oWbk As Workbook

Set oWbk = Workbooks.Add()

second option:

Set oWbk = Workbooks.Add()

But the hint on the properties and methods of the oWbk object will only work in the second case.

All experienced developers generally prohibit the use of variables without explicitly declaring them. To do this, you can use a special compiler command (it is placed only in the declaration section of the module) Option Explicit

or you can insert this command into all modules when they are created automatically - by setting the Require Variable Declarations checkbox in the code editor window (Tools -> Options menu, Editor tab).

To illustrate why they do this, you can use a simple example:

In appearance, the code should not cause any problems and simply display one in the message box. Actually it will pop up an empty message box. The reason is hidden very insidiously: in the third line, n is not the English letter N at all, but the Russian letter P. It is very difficult to distinguish them in the view in the code editor window. At the same time, the VBA compiler, upon encountering such code, will simply create a new variable with the Variant data type, which will have an empty value. It may take some time to detect such an error.

A good rule of thumb is to declare variables ahead of time, not when they are needed. This makes the program more readable and well planned.

You can declare multiple variables on one line, like so:

Dim n1 As Integer, s1 As String

Assigning values ​​to variables looks like this:

If you need to increase an already existing value of a variable, then the command might look like this:

nVar1 = nVar1 + 1

In both examples, the equal sign does not mean "equal to", but assign.

When assigning values ​​to variables, remember the following:

String values ​​are always enclosed in double quotes:

sVar1 = "Hello";

the date/time value is enclosed in "pounds" -- pound symbols:

dVar1 = #05/06/2004#

Note that when assigning a date/time value in this "explicit way" we will have to use US standards: 05 in this case is the month, 06 is the day. The display of this value (for example, in a message box) will depend on the regional settings on the user's computer.

If you need to pass a hexadecimal value, then it is preceded by &H characters:

What is contained in variables before they are assigned values?

• · In variables of all numeric data types -- 0.
• · In string variables of variable length -- "" (string of zero length).
• · In fixed-length string variables, a string of given length with ASCII 0 characters (these characters are not displayed on the screen).
• · In Variant, an empty value.
• · In Object -- nothing (there is no reference to any of the objects).

VBA Operators: Arithmetic, Boolean, Comparisons, Assignments

Operator is the smallest unit of VBA code that can run. A statement can declare or define a variable, set a VBA compiler option, or perform some action in a program.

There are only 7 arithmetic operators in VBA. Four standard ones: addition (+), subtraction (-), multiplication (*), division (/) and three more:

• exponentiation (^), for example 2^3 = 8 ;
• integer division (\). Divides the first number by the second, discarding (not rounding) the fractional part. For example, 5\2 = 2 ;
• modulo division (Mod). Divides the first number by the second, returning only the remainder of the division. For example, 5 Mod 2 = 1.

The assignment operator in VBA is the equals sign. It can be written like this:

Let nVar = 10

or even simpler:

nvar = 10

In the second case, don't confuse the equals sign with the equals operator.

Expression

nvar = 10

means "set the variable nVar to 10", and if the line looks like this:

If (nVar = 10)

it means "if the value of the variable nVar is equal to 10".

If you need to assign an object to a variable, then this is done in other ways.

There are only 8 comparison operators in VBA:

• equality (=), for example, If (nVar = 10);
• greater than and less than (> and<), например, If (nVar > 10);
• greater than or equal to and less than or equal to (>= and<=), например, If (nVar >= 10);
• not equal (<>), For example, If(nVar<>10) ;
• comparison of objects (Is). Determines whether object variables refer to the same object or to different ones, for example, If(obj1 is obj2);
• similarity (Like). Compares a string object to a pattern and determines if the pattern matches.

Comparison operators always return true or false - true if the assertion is true and false if false.

A little about comparing string values:

• when comparing string values, case is sensitive;
• spaces in string values ​​are also taken into account;
• when comparing text strings by more/less, by default, just binary character codes are compared - which is more or less. If you need to use the order that goes in the alphabet, you can use the command

Option Compare Text

A little more about the Like operator. Its general syntax looks like

Expression1 Like Expression2

In this case, Expression1 is any VBA text expression, and Expression2 is a template that is passed to the Like operator. You can use special wildcards in this pattern (see Table 3.1)

Tab. 3.1 Wildcards for the LIKE operator

Very often, when testing several conditions, logical operators are used:

• AND - logical AND, both conditions must be true;
• OR - logical OR, at least one of the conditions must be true;
• NOT - logical negation, returns TRUE if the condition is false;
• XOR is a logical exception. In an E1 XOR expression, E2 returns TRUE if only E1 = TRUE or only E2 = TRUE otherwise FALSE;
• EQV - equivalence of two expressions, returns TRUE if they have the same value;
• IMP - implication, returns FALSE if E1 = TRUE and E2 = FALSE, otherwise TRUE.

You need to remember about AND, OR, NOT, other logical operators are rarely used.

Almost every VBA program uses concatenation operators. There are two of them in VBA - + or &. It is recommended to always use & because:

• when using &, the automatic conversion of numeric values ​​​​to strings is performed - there is no danger of making a mistake;
• when using the + operator, adding a string value to a value of type Null yields Null.

MsgBox "Message to user" & vUserName

The order in which operators are applied can be controlled using parentheses.

Operator	Syntax	Description
AND	A AND B	Conjunction: If A and B are True, then True. Otherwise - False
OR	A OR B	Disjunction: If either operand is True, then True. Otherwise - False
NOT	NOT A	Negation: If A is False, then True. Otherwise - False
XOR	A XOR B	Exception: If A is True or B is True, then True. Otherwise - False
EQV	A EQV B	Equivalence: If A has the same value as B, then True. Otherwise - False
IMP	A IMP B	Implication: If A is True and B is False, then False. Otherwise - True

As an operand for a logical operator, you can use any valid expression that has a Boolean result, as well as a number that can be converted to a Boolean value.

The result of a logical operation is a value of type Boolean (or Null if at least one of the operands is Null).

Logical operator AND

Syntax:
Operand_1 AND Operand_2

The AND operator performs logical conjunction.

The result of this operation is True only when both operands are True, otherwise False.

truth table

The AND operator can be used on multiple operands:

(5 3) AND (5=6) result is False

Regardless of the number of operands, the logical AND operation will result in True only if all operands of the expression evaluate to True. In any other case, the result will be False. Note that the operands are enclosed in parentheses. VBA first evaluates the value of each operand inside the brackets, and then the entire expression.

Logical operator OR

Syntax:
Operand_1 OR Operand_2

The OR operator performs logical disjunction.

The result of this operation is True if at least one of the operands is True, otherwise False.

truth table

The OR operator can be used on multiple operands:

(5 3) OR (5=6) result is True

Regardless of the number of operands, the result of the logical OR operation will always be True if at least one of the operands of the expression evaluates to True. Otherwise, the result will be False.

The AND and OR operators can be combined:

((5 3)) OR (5=6) result is True

Boolean operator NOT

Syntax:
NOT Operand

The NOT operator does logical negation.

The NOT operator uses only one operand.

truth table

AND OR NOT operators can be combined:

((5 3)) OR NOT (5=6) result is True

Logical operator XOR

Syntax:
Operand_1 XOR Operand_2

The XOR operator performs logical exception.

The result of this operation is True if the operands have different values, otherwise False.

truth table

((5 3)) OR NOT (5=6) XOR (5=5) result is False

Logical operator EQV

Syntax:
Operand_1 EQV Operand_2

The EQV operator is the operator logical equivalence.

The result of this operation is True if the operands have the same value, otherwise False.

truth table

((5 3)) OR NOT (5=6) EQV (5=5) result is True

Boolean operator IMP

Syntax:
Operand_1 IMP Operand_2

The IMP statement performs a logical operation implications.

truth table

((5 3)) OR NOT (5=6) IMP (5=5) result is True

The IMP logical operator is the least intuitive of all logical operators. Fortunately, the need for its use arises quite rarely.

Language instructions (or operators) are program units that perform some kind of action or describe data.

A statement consists of one or more keywords and optionally parameters. Several statements located on the same program line are separated from each other by a colon. If the statement is too long, you can break it into several lines using the underscore character _ for wrapping.

ABOUT P edator appropriations

The assignment statement is used to assign a new value to a variable during program execution. Assignment sign "=".

For example, operator:

x = Sqr(5 + Tan(1.8) ^ 2)

assigns the value of the expression to x. As a result of evaluating the expression written on the right side, we get a real number. But the value that will be assigned to the variable x depends on how the type of this variable has been declared. So, if the variable x is of type Single, it will be assigned the result 4.834464, if Double, then 4.83446368725481, and if Integer, then 5.

The data type of the variable x must be compatible with the data type of this expression. In addition to implicit conversion from one type to another during assignment, the Visual Basic language provides the ability to convert types using functions. For example, the function cdbl converts data to double type, CInt– to type Integer, clng– to type Long, csng– to Single type, CStr- to type String, etc.

The CInt and Clng functions round the result. In this case, if the fractional part of the number is 0.5, rounding is performed to the nearest even number.

assignment operator allows not only to assign a value to a variable, but also to set the values ​​of properties of VBA objects. For example, the operator

Rows("1:1").Font. Bold=True

makes the first line of the worksheet bold.

LINE BREAK

Combination<Пробел> + <Знак подчеркивания>at the end of a line ensures that the next line is a continuation of the previous one. In doing so, you must remember that:

§ Do not wrap string constants

§ No more than seven continuations of the same line are allowed

§ The string itself cannot contain more than 1024 characters

Example.

Incorrect transfer Correct transfer

String = "Visual Basic for _ String = "Visual Basic" _

Applications" & "for Applications"

COMMENTS

The text following the symbol (") in the program up to the end of the line is ignored by the compiler and is a comment. Comments are used to add explanations and descriptions to the program. Comments are useful when debugging a program: they allow you to temporarily disable program lines during debugging.

Conditional operator

The conditional operator executes certain instructions (operators) depending on the value of the condition expression. The flowchart for checking the condition looks like this:

Rice. 1. Branching may be incomplete (such a structure is also called bypass).

Rice. 2. The conditional operator has two forms of syntax: inline and block.

Lowercase form

If condition Then [ operators_if_true]

The algorithm presented in fig. 1, in lowercase form will be written as

If condition Then operators_if_true Else operators_if_false

Figure 2 corresponds to the entry

If condition Then operators_if_true

Figure 3 corresponds to the entry

If condition Then else operators_if_false

block shape

If condition-1 Then

[operators_if condition–1_true]

[statements_if_all_conditions_false]]

The conditions listed in the If and ElseIf parts are relational or boolean expressions. When one of the conditions is met, the statements following the matching Then keyword are executed, and the rest of the statements are ignored (i.e., no further checks are performed and control is transferred to the statement following the End If). If none of the logical conditions is true, then the statements_if_all_previous_conditions_false, or, if this part is omitted, control is transferred to the program line following the conditional statement.

The block form of the If statement is preferable if:

When the condition is met or not met, several statements are executed (the lowercase form can also be used in this case, but the line will be too long and the program less understandable);

Several conditions are checked sequentially, and when the next condition is met, it is not advisable to check subsequent conditions (this is what the ElseIf keyword is used for).

Example

The company provides discounts to wholesale buyers.

Based on the known volume of the order, it is necessary to determine its cost.

To calculate the cost of the order, we use the function:

Public Function OrderCost(Quantity As Long) As Double

If Quantity<= 999 Then

Order_cost = Quantity * 5

ElseIf Quantity<= 1999 Then

Order_cost = Quantity * 4.8

Order_cost = Quantity * 4.75

In this case, you could also use the lowercase form of the IF statement:

Public Function Order_Cost1(Quantity As Long) As Double

If Quantity<= 999 Then Стоимость_заказа1 = Количество * 5

If Number >= 1000 And Number<= 1999 Then Стоимость_­ заказа1 = Количество * 4.8

If Quantity >= 2000 Then OrderCost1 = Quantity * 4.75

If we do not consider the shortcomings that the lines are long, and for any order volume all checks are performed sequentially (the first procedure works faster for small order volumes), then the program is written correctly and, perhaps, even more visually.

However, examples can be given when, if one of the conditions is met (or not met), the others simply cannot be checked.

For example, some statements must be executed when the conditions are met together: checks use operator

If x>0 and y

then with a negative value of x, an error will occur when calling the sqr function (under the root is a negative number).

This error can be avoided by using the construction

If x > 0 Then If y< Sqr(x) Then

In the last notation, if there is an Else keyword, then it refers to the last If statement.

Select statement

Select Case expression

[instructions_else]]

The expression parameter is any numeric or string expression. Instead of evaluating a logical condition, the value of the expression is compared with each of the values ​​specified by the parameter expression_list-n.

Comparison values ​​included in expression-list-n, can be given in the form:

– values;

– range of values ​​in the form initial_value To end_value;

– comparison expressions in the form Is comparison_operator value;

- a list of any of the listed types of expressions (separator - comma).

A statement can have any number of Case blocks. If none of the conditions is true, then the statements of the Case Else block are executed.

For example, if the condition is a score above three, then this condition can be written: Case 4, 5 or Case Is >3 or Case Is >= 4 or Case 4 To 5.

Note. The keyword Is can be omitted, it will be added by itself.

Example

The discounted price example discussed above can be solved using the Select Case construct as follows:

Public Function Order_Cost2(Quantity As Long) As Double

Select Case Quantity

Order_cost2 = Quantity * 5

Case 1000 to 1999

Order_cost2 = Quantity * 4.8

Case Is >= 2000

Order_Cost2 = Quantity * 4.75

A VBA program is a sequence of statements.

There are a number of conventions to be followed in programming. So, you can place several statements on one line. A colon is placed between statements on the same line.

Any line can be split into two by placing the characters "Space" + "Underscore" (_) at the end of the first, in which case the second line will be considered a continuation of the first.

Comments are used to make the program easy to read. There are two ways to enter comments in VBA: using an apostrophe (‘), which can be placed anywhere in a line, and using the reserved word Rem instead of an apostrophe.

1. Dim statement is for declaring variable types.

Dim A As Integer - variable A is declared as an integer, i.e. it will store only integer values .

Dim D As Date - variable D is declared to store dates.

· Dim Surname, Name As String – variables are declared. Surname and Name, intended for storing text.

Dim B(12) As Integer - a one-dimensional array (vector) is declared, consisting of 12 integers, and by default the first element of the array will be B(0), and the last B(12).

Dim B(3,3) As Single – a two-dimensional 3x3 array (matrix) is declared, consisting of real numbers.

If the variable type is not specified, then the Variant type is used by default. However, specifying a specific variable type makes the program more reliable and speeds up its work, because VBA doesn't have to spend time recognizing an undeclared variable each time it is accessed.

If the size of the array M is not known in advance and is determined during the program, then when describing the array, the number of elements is not indicated, and the array is defined as follows:

Dim M() As Integer

After determining the number of array elements, for example, N, you need to write the operator

2. assignment operator is used to assign a value to a variable.

Syntax:

Variable (or object property) = expression.

a=5 - variable A to assign the value 5 ;

b="Manager" - variable b assign meaning "Manager";

Address=Sheets("Organizations").Cells(2,2) – assign the contents of cell B2, which is located on the Organization sheet in the current workbook, to the Address variable;

LastName=UserForm1.TextBox1.Text - variable Last name to assign the contents of the TextBox1 field of the user form UserForm1.

3. With/End with statement saves the programmer from a large number of repetitions of the name of the same object.

Syntax:

With object

operator1

operator2

operatorN

For example, instead of a sequence of statements

UserForm1.TextBox1.Text = Date

UserForm1.TextBox2.Text = " "

UserForm1.ComboBox1.Text = " "

can be written like this

TextBox1.Text = Date

. TextBox2.Text = " "

. ComboBox1.Text = " "

REM On sheet Sheet1 in column A, starting from the second line, the ‘rates of employees’ are recorded. Populate the ComboBox1 combo box in the UserForm1

‘The first line of the program is on sheet Sheet1 in column A ‘the number of filled cells is counted, the result is ‘assigned to the variable N

N=Application.CountA(Sheets("Sheet1").Range("A:A")).

D=”A2:A”&Cint(N)

Sheets("Sheet1").Range(D).Name="Rates"

TextBox1.Text = Date

. TextBox2.Text = " "

. ComboBox1.Text = " "

. ComboBox1.Rowsource = "Rates"

4. Conditional If/Then/Else Statement- allows you to check a certain condition and, depending on the results of the check, perform one or another action

Syntax:

If condition Then operators1 [ Else operators2]

If the condition is true, then statements1 are executed, otherwise statements2 are executed.

It is also allowed to use a complex conditional operator, which is written as a block:

If condition1 Then

operators1

ElseIf condition2 Then