Methodical instructions and tasks. VBA language statements Vba logical expressions
Comparison operations are typically used in looping statements to make some sort of decision about the further progress of operations.
The result of any comparison operation is a Boolean value: True, False.
If both operands in a comparison expression are of the same data type, VBA performs a simple comparison for that type.
If both operands in a comparison expression are of specific types and the types are not compatible, VBA issues a type mismatch error.
If one or both operands in the comparison expression are Variant, VBA tries to convert the Variant to some compatible type.
String comparison
When comparing strings with relational operators, VBA compares each string from left to right character by character.
In VBA, one string is equal to the other only when both strings contain exactly the same characters in exactly the same order and both strings are the same length. For example, the strings "johns" "johns" "abvgd" are not equal to each other, because VBA does not ignore leading or trailing whitespace when comparing strings.
Care should be taken when comparing variable-length strings.
Binary and text string comparison
To store text, the computer uses a scheme in which each displayed character has a unique number. All letters of the alphabet have their own unique numbers. Typically, uppercase letters are numbered lower than lowercase letters. The number corresponding to a specific letter or symbol is called character code.
When performing binary comparisons on string information, VBA uses the binary equivalent of a number for each character. This comparison method is called binary or binary and is the default comparison method.
Because uppercase letters have lower binary numbers, uppercase letters appear in alphabetical order before lowercase letters. Therefore, when comparing binary strings, the string "ABC" will be smaller than the string "abc".
When comparing strings textually, VBA does not use the binary equivalent of characters, and does not "distinguish" between upper and lower case. In a text comparison, the string "abc" is equal to the string "ABC".
To select a method for comparing strings, use the Option Compare directive
Option Compare
This directive must be in the module declaration area.
String concatenation
Joining one string to another is called string concatenation.
String concatenation is commonly used to form strings from various sources in a procedure to create a message for display. VBA has two operators for string concatenation.
Concatenation Operator (&)
The operator (&) in VBA is only used for string concatenation.
Syntax
Operand_1 & Operand_2 [& Operand_3 ..]
Operand_N is any valid string or numeric expression (which is converted to string).
The data type of the result of string concatenation is String.
If an operand in a string concatenation expression is Empty or Null, VBA interprets that operand as a zero-length string (no character string).
Note! The symbol (&) of the concatenation operation must be separated by a space from the variable name, since otherwise, VBA can interpret the character as a Long type inference character.
The addition operator in string concatenation
You can also use the (+) operator to concatenate strings.
This operator has the same syntax and requirements as the (&) operator. However, it should be understood that in VBA the main purpose of the (+) operator is arithmetic addition. Therefore, in order to avoid ambiguity in reading the program code, it is strongly recommended to use the operator (&) for string concatenation.
Operation Priorities
Many of the expressions in the program code are complex (compound), i.e. consist of two or more expressions.
When evaluating complex expressions, VBA follows these rules:
- Portions of an expression enclosed in parentheses are always evaluated first;
- Specific operations are performed depending on the operator hierarchy (table below);
- If the operator hierarchy is equal, they are evaluated from left to right.
| Operator |
Language statements (or operators) Are software units that perform some action or describe data.
The operator includes one or more keywords and possibly parameters. Several operators located on the same program line are separated from each other by a colon. If the statement is too long, you can split it into multiple lines by using the underscore _ for wrapping.
ABOUTp eratorappropriation
The assignment operator is used to assign a new value to a variable during program execution. The assignment sign "\u003d".
for example, operator:
x \u003d Sqr (5 + Tan (1.8) ^ 2)
assigns the value of the expression to the variable x. As a result of calculating 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 was described. 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, Visual Basic provides the ability to convert types using functions. For example, the function CDbl converts data to type Double, CInt - to type Integer, Clng- to type Long, CSng - to type Single, CStr - to the type String, etc.
The CInt and Clng functions round off the result. Moreover, if the fractional part of the number is 0.5, rounding is performed to the nearest even number.
Assignment operator allows you not only to assign a value to a variable, but also to set the values \u200b\u200bof properties of VBA objects. For example, the operator
Rows ("1: 1"). Font. Bold \u003d 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. It should be remembered that:
§ You can not break string constants by hyphenation
§ No more than seven extensions of the same line are allowed
§ The string itself cannot contain more than 1024 characters
Example.
Invalid transfer Correct transfer
String \u003d "Visual Basic for _ String \u003d" Visual Basic "_
Applications "&" for Applications "
COMMENTS
The text following the (") character 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
A conditional operator executes certain instructions (operators) depending on the value of the condition expression. The block diagram for checking the condition is as follows:
Figure: 1. Branching may be incomplete (this structure is also called traversal).
Figure: 2. The conditional operator has two forms of syntax: line and block.
Lowercase form
If condition Then [ operators_if_true]
The algorithm shown 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 form
If condition-1 Then
[operators_if condition – 1_true]
[operators_if_all_conditions_false]]
The conditions listed in the If and ElseIf parts are relational or logical expressions. When one of the conditions is met, the statements following the corresponding Then keyword are executed, and the remaining statements are ignored (that is, no further checks are performed and control is transferred to the statement following the End If). If none of the logical conditions is equal to true, then operators_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 an If statement is preferable if:
When a condition is met or not met, several statements are executed (the lowercase form in this case can also be used, but the line will be too long, and the program is less understandable);
Several conditions are checked sequentially, and when the next condition is met, it is inappropriate to check the subsequent conditions (this is what the ElseIf keyword is used for).
Example
The firm provides discounts to wholesale buyers.
Based on the known volume of the order, it is necessary to determine its value.
To calculate the cost of an order, use the function:
Public Function Order_Price (Quantity As Long) As Double
If Quantity<= 999 Then
Order_Price \u003d Quantity * 5
ElseIf Quantity<= 1999 Then
Order_value \u003d Quantity * 4.8
Order_Price \u003d Quantity * 4.75
In this case, it was possible to use the lowercase form of the IF statement:
Public Function Order_Price1 (Quantity As Long) As Double
If Quantity<= 999 Then Стоимость_заказа1 = Количество * 5
If Quantity\u003e \u003d 1000 And Quantity<= 1999 Then Стоимость_ заказа1 = Количество * 4.8
If Quantity\u003e \u003d 2000 Then Order_value1 \u003d Quantity * 4.75
Apart from the disadvantages that the lines are too long, and for any order volume, all checks are sequentially performed (the first procedure works faster with small order volumes), then the program is written correctly and, perhaps, even more visual.
However, examples can be given when, when one of the conditions is fulfilled (or not), it is simply impossible to check others.
for example, some operators must be executed when the conditions are met together: http://po-teme.com.ua/images/adIIIin/image014_e652e49476c6f0eb9cf40b68cc090828.gif "alt \u003d" "width \u003d" 56 "height \u003d" 27 src \u003d "\u003e. If for checks use operator
If x\u003e 0 and y then if x is negative, an error occurs when calling the sqr function (a negative number under the root). This error can be avoided by using the construction If x\u003e 0 Then If y< Sqr(x) Then In the last form of notation, if the keyword Else is present, then it refers to the last If statement. Selection operator 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 \u200b\u200bspecified by the parameter expression_list-n. Comparison values \u200b\u200bincluded in expressionlist-n, can be specified as: - values; - a range of values \u200b\u200bin the form start_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). An instruction can contain any number of Case blocks. If none of the conditions are true, then the statements in the Case Else block are executed. For example, if the condition is a score higher than three, then this condition can be written: Case 4, 5 or Case Is\u003e 3 or Case Is\u003e \u003d 4 or Case 4 To 5. Note. The Is keyword can be omitted, it will be added by itself. Example The above example with a discounted price using the Select Case construct can be solved like this: Public Function Order_Price2 (Quantity As Long) As Double Select Case Quantity Order_Price2 \u003d Quantity * 5 Case 1000 To 1999 Order_Price2 \u003d Quantity * 4.8 Case Is\u003e \u003d 2000 Order_Price2 \u003d Quantity * 4.75 When you write VBA code in Excel, a set of built-in operators are used at every step. These operators are divided into mathematical, string, comparison and logical operators. Next, we'll take a closer look at each group of operators. Basic VBA math operators are listed in the table below. The right column of the table shows the default operator precedence in the absence of parentheses. By adding parentheses to an expression, you can change the order of execution of VBA statements as desired. The main string operator in Excel VBA is the concatenation operator &
(merge): Comparison operators are used to compare two numbers or strings and return a boolean value like Boolean (True or False). The main Excel VBA comparison operators are listed in this table: Logical operators, like comparison operators, return a boolean value of the type Boolean (True or False). The basic Excel VBA logical operators are listed in the table below: The above table does not list all of the logical operators available in VBA. A complete list of logical operators can be found at the Visual Basic Developer Center. There are many built-in functions available in VBA that can be used when writing code. Some of the most commonly used are listed below: DateAdd ( interval
, number
, date
) Where is the argument interval defines the type of time interval added to the given date in the amount specified in the argument number
. Argument interval can take one of the following values: Example: Day (“29/01/2015”) returns the number 29. Example: Hour (“22:45:00”) returns the number 22. Note: The number argument can be omitted, in which case the search starts at the first character of the string specified in the second function argument. Example: Int (5.79) returns the result 5. Left ( line
, length
) where line Is the original string and length - the number of characters returned, counting from the beginning of the line. Example: Len ("abvgdezh") returns the number 7. Example: Month (“29/01/2015”) returns the value 1. Mid ( line
, start
, length
) where line Is the original string, start - the position of the beginning of the extracted string, length - the number of characters to be extracted. Right ( line
, length
) Where line Is the original string and length Is the number of characters to extract from the end of the given string. Example: Second (“22:45:15”) returns 15. Note: For multidimensional arrays, you can specify the index of which dimension to return as an optional argument. If not specified, the default is 1. VBA is an operator language. This means that its programs (procedures or functions) represent sequences of statements. In the VBA language, the following groups of operators can be distinguished: 1. declarative operators designed to describe the objects with which the program works (types of variables, constants and arrays, etc.), 2.operators-comments, 3. operators of assignment and change of values \u200b\u200bof objects, 4. operators that control the course of calculations (conditional, cyclic, transition). In this course, the basic operators will be considered, and some, for example, cyclic, will be presented in three types, but students should master only one, as the most understandable for use. Comment operator Comments do not affect the execution of the program, but are necessary to understand the algorithm. Since programs are being modernized repeatedly, it is extremely important to use comments to remember the algorithm and change it correctly. Any line of program text can end with a comment. A comment in VBA begins with an apostrophe (") and includes any text to the right of the line. For instance, Weight \u003d weight + z "Weight gain value \u003d weight * price" New cost Assignment operator Assignment operators are the main means of changing program state (variable values). It is a construction that connects a variable (left side) and an expression (right side) with the sign \u003d. An expression consists of operands (variable names, constants, standard function names) and operation signs (arithmetic, logical, string, comparison). The meaning of this operator is that the left side is assigned the value of the right side. Control Operators The VBA set of control statements conforms to a structured programming language. This set includes conditional and loop statements, which allows you to organize the computation process reliably and efficiently. Conditional statement If Then Else End If
This is a common computation control operator in programming languages \u200b\u200bthat allows you to select and perform actions based on the truth of a certain condition. VBA operators - concept and types. Classification and features of the "VBA Operators" category 2017, 2018. This category of Dial in TRIZ is presented as “principle No. 34, of discarding and regenerating parts: a) A part of an object that has fulfilled its purpose or has become unnecessary must be discarded (dissolved, evaporated, etc.) or modified directly in the course of work. b) .... Figure: 12.1 Objects located on the form 6. Activating each object on the form separately, set its property using the properties window (Properties Fig. 12.2). Thus, the interface is created (Figure 12.3). Figure: 12.3. Project interface (with .... Compound operator. Structured operators Procedure call operator Unconditional jump operator The unconditional jump operator provides the ability to change the execution order .... Sometimes, when solving problems, a situation arises when other conditional operators can, in turn, be the operators contained in the branches. They are called compound operators. Moreover, the number of investments, or, as they say, levels of investments can be .... Visual Basic For Application (VBA) is a combination of one of the simplest programming languages \u200b\u200band all the computational power of an Excel spreadsheet processor. With VBA, you can easily and quickly create a variety of applications, even if you are not an expert in the field ... VBA boolean operators are used to combine the results of individual Boolean expressions to create complex decision criteria in a procedure (Table B.3). Table B.3 - VBA logical operators Logical Syntax Name / Description operator El And E2 Conjunction. True if both E1 and E2 are True, otherwise False El Or E2 Disjunction. True if one expression living or both (E1 and E2) are equal to True; otherwise - False Not El Denial. True if E1 has a value the value is False; False if El is equal to True El Xor E2 An exception . True if E1 and E2 have different meanings; otherwise - El Eqv E2 Equivalence... True if E1 has has the same meaning as E2; otherwise - False El Imp E2 Implication. False when E1 is equal to True and E2 equal to False; otherwise True. The E operands in this table represent any valid Boolean expression, such as a comparison operation. Complex expression - it is any expression formed from two or more expressions. Table B.4 contains operations by groups in descending order of priority, that is, the lower the group is located, the later the operations specified in it are performed. Parentheses are used to change this order. Operations at the same level are performed from left to right in the order in which they are written in the expression. Table B.4 - VBA Operation Priorities Operator Comments Exponentiation, highest priority Unary minus - change the sign of a number Multiplication and division have equal priority; they you- Whole division Remainder of division Addition and subtraction have equal priority; they you- are counted as they appear in the expression from left to right Any string concatenation is done after any arithmetic operations in an expression and before any comparison or logical operations <, <=, >, >=, All comparison operators have equal precedence and are counted as they appear in the expression from left to right. Use parentheses to group operators comparison moat in expressions brain teaser Not And Or Xor Eqv Im p \u200b\u200b- have the lowest priority operators Appendix B VBA Math Functions VBA provides a standard set of mathematical functions shown in Table B.1. Table B.1 - VBA math functions Returns / action Returns the absolute value of N Cosine of angle N, where N is the angle measured in ra- Returns the sine of an angle; N is the angle measured in radians Returns the tangent of an angle; N - angle in radians Returns the arctangent of N as an angle in radians Returns the constant e raised to the power N (e - this is the base of natural logarithms and it (for- approximately) equal to 2.718282 Returns the integer part N. Fix does not round the number, but negative, Fix returns the nearest negative integer greater than or equal to N Returns the integer part of N. Int does not round the number, but discards any fractional part. If N is negative, Int returns the nearest negative integral integer less than or equal to N Returns the natural logarithm of N Returns a random number; argument is not- compulsory. Use Rnd function only after initializing the VBA generator of random numbers sat down with the Randomize operator Returns the sign of a number: –1 if N is negative; 1 if N is positive; 0 if N is 0 Returns the square root of N. VBA displays runtime error if N is negative Does the N operand mean any numeric expression? admissible in The argument for trigonometric functions (sine, cosine, and tangent) is in radians, not degrees. The inverse trigonometric function A t n (x) returns to the program the value of the angle in radians from -π / 2 to π / 2, the tangent of which is equal to the value of the arithmetic expression x. The relationship between the radian and the degree measures of the angle is expressed through the irrational number π ≈ 3.14159265358979323846. Radians \u003d Degrees × π Degrees \u003d Radians × 180 To convert degrees to radians, you can also use the built-in Excel function - Application.Radians (x), where x is the angle, given in radians. To obtain the numerical value of the constant π with an accuracy of 15 digits of the mantissa, you can use the built-in Excel Application.Pi () function, or the VBA function arctangent Pi \u003d Application.Pi () or Pi \u003d 4 * Atn (1) Examples of writing trigonometric functionsMath operators
String Operators
Comparison Operators
Logical operators
Built-in functions
Function
Act
Abs
Returns the absolute value of the specified number.
Chr
Returns the ANSI character corresponding to the numeric parameter value.
Date
Returns the current system date.
DateAdd
Adds a specified time interval to a specified date. Function syntax:
DateDiff
Calculates the number of specified time intervals between two specified dates.
Day
Returns an integer corresponding to the day of the month at the specified date.
Hour
Returns an integer representing the number of hours at a given time.
InStr
Accepts an integer and two strings as arguments. Returns the position of occurrence of the second string within the first, starting the search at the position specified by an integer.
Int
Returns the integer portion of the specified number.
Isdate
Returns Trueif the given value is a date, or False - if the date is not.
IsError
Returns Trueif the given value is an error, or False - if it is not an error.
IsMissing
The name of the optional procedure argument is passed as an argument to the function. IsMissing returns Trueif no value is passed for the procedure argument in question.
IsNumeric
Returns Trueif the given value can be considered a number, otherwise returns False.
Left
Returns the specified number of characters from the beginning of the passed string. The function syntax is as follows:
Len
Returns the number of characters in a string.
Month
Returns an integer corresponding to the month at the given date.
Mid
Returns the specified number of characters from the middle of the passed string. Function syntax:
Minute
Returns an integer representing the number of minutes in the specified time. Example: Minute (“22:45:15”) returns 45.
Now
Returns the current system date and time.
Right
Returns the specified number of characters from the end of the passed string. Function syntax:
Second
Returns an integer representing the number of seconds in a given time.
Sqr
Returns the square root of the numeric value passed in the argument.
Time
Returns the current system time.
Ubound
Returns the superscript dimension of the specified array.
Parameter name
Value
Topic of the article:
VBA statements
Category (thematic category)
Programming
Operational Priorities for Evaluating Complex Expressions
