Arithmetic operations in VBA: addition, subtraction, multiplication, division and exponentiation. Operators and built-in functions VBA Vba remainder mod

Assignment operator (=)

The assignment operator is used to assign the result of an expression to a variable.

The assignment operator has two syntactic forms:

varname = expression

varname- any VBA variable

expression- any VBA expression

When you execute an assignment statement, VBA first evaluates the expression to the right of the assignment statement, and then stores the result of the expression in the variable whose name is to the left of the equal sign.

X = 5 + 7; Y = X + 5 ; Z = X - Y; A=B; I = I + 1

Beginner users sometimes do not understand the meaning of the last assignment operation, when both the left and right parts of the operation contain the same variable. In this case, the result of evaluating the expression on the right side of the assignment operator is first placed in an intermediate memory location, and then this result is assigned to the variable on the left side.

For example, if in the assignment operator A = A +5, the variable A before the assignment operation contained the value 7, then after the operation it will contain the value 12 (7+5).

Things to remember:

· You can assign any numeric variable (or expression) to any other numeric variable (or Variant type);

· If you assign a numeric expression to a typed variable with less precision (for example, Double - Long), VBA rounds the value of the expression to match the precision of the variable receiving the new value;

· If a variable of type String is assigned type variable Variant containing a number, VBA automatically converts the number to a string.

Addition operator (+)

The addition operator performs simple addition. Both operands must be numeric expressions or strings that VBA can convert to a number. The addition operator can also be used to perform arithmetic operations with data type Date.

The data type of the result of an addition expression is usually the same as the most precise type in that expression. But, there are exceptions:

· The result of addition of type Single and Long will be Double;

· The result of adding a Date type to any other data type will always be a Date;

· If the result exceeds the range of type Integer, then VBA converts it to Long;

· If the result exceeds the types Long, Single, Date, then VBA converts it to Double;

· If any operand in an addition expression is Null, then the result of the addition expression will also be Null.

Let us recall the order of increasing precision for numeric data types: Byte, Integer, Long, Single, Double, Currency.

It should be said that you need to clearly understand how VBA converts data types as a result of arithmetic operations. This will help in the future to avoid many “unnecessary” errors when writing code.

Subtraction operator (-)

The subtraction operator does two things: it is used to subtract one number from another; stands for unary minus (this is a minus sign that is placed in front of a number to indicate that it is a negative number). Placing a unary minus in front of a variable or expression is the same as multiplying that number by -1.

Both operands in a subtraction expression must be numeric variables (expressions) or string expressions that VBA can convert to a number. You can use the subtraction operator to work with dates.

VBA uses the same rules to determine the data type of the result of a subtraction expression as it does for expressions that use the addition operator. But, there is an addition:

· If both operands in an expression are of type Date, then the result of the expression will be of type Double.

Multiplication operator (*)

The multiplication operator multiplies two numbers - the result of the multiplication expression is the product of the two operands. Both operands in a multiplication expression must be numeric expressions or strings that VBA can convert to a number.

VBA follows the same rules for determining the data type of the result of a multiplication expression as it does for expressions that use the addition operator. In multiplication expressions, all Variant variables that contain Date values are converted to numeric values.

Division operator (/)

The floating-point division operator performs ordinary arithmetic division of its operands.

In division expressions, the first operand is divided by the second operand—the result of the division is the quotient.

Both operands in a floating-point division expression must be numeric expressions or strings that VBA can convert to a number.

If at least one operand in a division expression is of type Null, then the result of the division will also be Null.

The data type of a floating-point division operation is Double, except:

Both operands in the division expression are of type Integer or Single - the result is Single;

If the result of the expression does not overflow the range of values for type Single.

Integer division (\)

Integer division differs from floating-point division in that its result is always an integer without a fractional part.

Both operands in an integer division expression must be numeric expressions or strings that VBA can convert to a number.

Before performing an integer division operation, VBA rounds each operand to a number of type Integer or Long (the same type as the result of integer division).

VBA discards (but does not round!) any fractional remainder of the result of an integer division expression. For example, the expressions 22\5 and 24\5 will have the same result = 4.

If at least one operand in an integer division expression is of type Null, then the result of the division will also be Null.

Modulo division (Mod)

Modulo division complements integer division. In modulo division, the expression returns only the remainder of the division operation as an integer.

22 Mod 5 = 2

24 Mod 5 = 4

25 Mod 5 = 0

The remaining properties of modulo division are identical to integer division.

Exponentiation (^)

The exponentiation operator raises a number to a power.

5 ^ 3 =125

Both operators in the exponentiation expression must be numeric expressions or strings that VBA can convert to numbers.

The operand to the left of the exponentiation operator can only be a negative number if the operand to the right is an integer.

The result of the expression is of type Double.

If at least one operand in an expression is of type Null, then the result of exponentiation will also be Null.

Let's summarize the above:

VBA LOGICAL OPERATORS

The operand for a Boolean operator can be 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 value True, only when both operands are True, otherwise False.

Truth table

The AND operator can be used for multiple operands:

(5<7) AND (4>3) AND (5=6) the result will be False

Regardless of the number of operands, the result of the logical AND operation will be True only if all the 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 parentheses, and then the entire expression.

Logical operator OR

SYNTAX:

Operand_1 OR Operand_2

The OR operator does 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 for multiple operands:

(5<7) OR (4>3) OR (5=6) the result will be 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 is True. Otherwise the result will be False.

The AND and OR operators can be combined:

((5<7) AND (4>3)) OR (5=6) the result will be True

Logical 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<7) AND (4>3)) OR NOT (5=6) the result will be True

Logical operator XOR

SYNTAX:

Operand_1 XOR Operand_2

The XOR operator does logical exception.

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

Truth table

((5<7) AND (4>3)) OR NOT (5=6) XOR (5=5) the result will be 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 values, otherwise False.

Truth table

((5<7) AND (4>3)) OR NOT (5=6) EQV (5=5) the result will be True.

Comparison Operators

Typically, comparison operations are used in looping operators to make some decision about the further course of operations.

The result of any comparison operation is a Boolean value: True, False.

If both operands in a comparison expression have the same data type, VBA performs a simple comparison for that type.

If both operands in a comparison expression have specific types and the types are not compatible, VBA issues a type mismatch error.

If one or both operands in a comparison expression are variables of type Variant, VBA attempts to convert the Variant type to some compatible type.

VBA operators: arithmetic, logical, comparisons, assignments

Operator 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 (without 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 equal sign. You can write it like this:

Let nVar = 10

or even simpler:

nVar = 10

In the second case, do not confuse the equals sign with the equals operator.

Expression

nVar = 10

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

If (nVar = 10)

then this means "if the value of nVar is 10".

If an object needs to be assigned to a variable, 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, e.g. If(obj1 is obj2);
similarity (Like). Compares a string object to a pattern and determines whether the pattern matches.

Comparison operators always return true or false - true if the statement 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 to greater/lesser, by default, simply binary character codes are compared - which is greater 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)

Table 3.1 Wildcards for operator LIKE

Very often, when checking 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 &, numeric values are automatically converted to string values - there is no danger of making a mistake;
When using the + operator, adding a string value to a value of type Null produces Null.

MsgBox "Message to user" & vUserName

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

When writing 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 will look at each group of operators in detail.

Mathematical operators

The 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 in which VBA statements are executed as desired.

String operators

The main string operator in Excel VBA is the concatenation operator & (merge):

Comparison Operators

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

Logical operators, like comparison operators, return a Boolean value of type Boolean(True or False). The basic Excel VBA logical operators are listed in the table below:

The table above does not list all the Boolean operators available in VBA. Full list Boolean operators can be found on the Visual Basic Developer Center.

Built-in functions

There are many built-in functions available in VBA that can be used when writing code. Below are some of the most commonly used:

Function	Action
Abs	Returns the absolute value of a given number. Abs(-20) returns the value 20; Abs(20) returns the value 20.
Chr	Returns the ANSI character corresponding to the numeric value of the parameter. Chr(10) returns line break; Chr(97) returns character a.
Date	Returns the current system date.
DateAdd	Adds a specific time interval to a given date. Function syntax: DateAdd( interval , number , date ) Where is the argument interval defines the type of time interval added to a given date in the amount specified in the argument number . Argument interval can take one of the following values: DateAdd(“d”, 32, “01/01/2015”) adds 32 days to the date 01/01/2015 and thus returns the date 02/02/2015. DateAdd(“ww”, 36, “01/01/2015”) adds 36 weeks to the date 01/01/2015 and returns the date 09/09/2015.
DateDiff	Calculates the number of specified time intervals between two given dates. DateDiff(“d”, “01/01/2015”, “02/02/2015”) calculates the number of days between the dates 01/01/2015 and 02/02/2015, returning the result 32. DateDiff(“ww”, “01/01/2015”, “03/03/2016”) calculates the number of weeks between the dates 01/01/2015 and 03/03/2016, returning the result 61.
Day	Returns an integer corresponding to the day of the month in the given date. Example: Day(“01/29/2015”) returns the number 29.
Hour	Returns an integer corresponding to the number of hours in the given time. Example: Hour(“22:45:00”) returns the number 22.
InStr	Takes an integer and two strings as arguments. Returns the position of the occurrence of the second string within the first, starting the search at the position specified by the integer. InStr(1, “Here is the word you are looking for”, “word”) returns the number 13. InStr(14, “Here is the search word, and here is another search word”, “word”) returns the number 38. Note: The number argument may not be specified, in which case the search starts from the first character of the string specified in the second argument of the function.
Int	Returns the integer part of the given number. Example: Int(5.79) returns the result 5.
Isdate	Returns True if the given value is a date, or False– if it is not a date. IsDate(“01/01/2015”) returns True; IsDate(100) returns False.
IsError	Returns True, if the given value is an error, or False– if it is not an error.
IsMissing	The name is passed as an argument to the function optional argument procedures. IsMissing returns True, if no value is passed for the procedure argument in question.
IsNumeric	Returns True if the given value can be considered as a number, otherwise returns False.
Left	Returns the specified number of characters from the beginning of the passed string. The syntax of the function is this: Left( line , length ) Where line is the original string, and length– the number of characters returned, counting from the beginning of the line. Left(“abvgdezziklmn”, 4) returns the string “abvg”; Left(“abvgdezziklmn”, 1) returns the string "a".
Len	Returns the number of characters in a string. Example: Len(“abvgdez”) returns the number 7.
Month	Returns an integer corresponding to the month in the given date. Example: Month(“01/29/2015”) returns value 1.
Mid	Returns a specified number of characters from the middle of the passed string. Function syntax: Mid( line , Start , length ) Where line is the original string Start– position of the beginning of the extracted string, length– the number of characters to be extracted. Mid(“abvgdezziklmn”, 4, 5) returns the string “where”; Mid(“abvgdezziklmn”, 10, 2) returns the string “cl”.
Minute	Returns an integer corresponding to the number of minutes in a given time. Example: Minute(“22:45:15”) returns the value 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: Right( line , length ) Where line is the original string, and length– this is the number of characters that need to be extracted, counting from the end of the given string. Right(“abvgdezziklmn”, 4) returns the string “clmn”; Right(“abvgdezziklmn”, 1) returns the string "n".
Second	Returns an integer corresponding to the number of seconds in the given time. Example: Second(“22:45:15”) returns the value 15.
Sqr	Returns the square root of the numeric value passed as an argument. Sqr(4) returns value 2; Sqr(16) returns the value 4.
Time	Returns the current system time.
Ubound	Returns the superscript of the dimension of the specified array. Note: For multidimensional arrays, an optional argument can specify the index of which dimension should be returned. If not specified, defaults to 1.

Let's take a closer look at how VBA performs arithmetic operations such as addition, subtraction, multiplication, division, and exponentiation, as well as special operations such as integer division and modulo division. The table below shows the operation signs used when writing arithmetic VBA expressions.

An expression is a value or a group of values expressing a single value. The result of the expression is one value certain type data. Signs (designations) of operations are used for actions on certain values in expressions. To assign the result of an expression to a variable, use the assignment operator (=), which stores whatever value is represented by the expression to the right of the assignment operator in the memory location referenced by the variable to the left of that operator.

In all operations below, both operands must be numeric expressions or strings that VBA can convert to a number.

Addition

The (+) sign is used to perform the addition operation. The terms must be numeric expressions, strings that VBA can convert to numbers, or dates that can also be used for arithmetic.

Sub Slozhenie() "Example of an arithmetic expression with a "+" sign Dim A1, A2, A3 As Integer "declaring variables A1=1 "assigning value 1 to variable A1 A2=2 "assigning value 2 to variable A2 A3=A1+A2 "assigning result addition of variable A3 MsgBox A3 "output of A3 in the End Sub dialog box

The data type of the result of an addition expression is usually the same as the most precise type in that expression, with a few exceptions. All exceptions to this rule are clearly presented in the examples.

Subtraction

The (-) sign is used to perform a subtraction operation, as well as to denote negative numbers (when placed in front of a variable or expression and means the same as multiplying by -1). A minus sign placed in front of a number to indicate that the number is negative is called a unary minus sign.

To determine the data type of the result of a subtraction expression, VBA follows the same rules as for expressions that use the addition operator, but there are two additional rules.

Rule 1. If in a subtraction expression one of the operands is of type Date, then the result of the expression will be of type Date.

Rule 2: If both operands in a subtraction expression are of type Date, then the result of the expression will be of type Double.

Sub Vychitanie() "Example of an arithmetic expression with the sign "-" Dim D1, D2 As Date "declaring variables D1=Now "assigning a value current date variable D1 D2=Now-5 "assigning a date value to variable D2 MsgBox TypeName(D2), vbOKOnly, "Now-5" MsgBox TypeName(D1-D2), vbOKOnly, "D1-D2" End Sub

Multiplication

The (*) sign is used to perform a multiplication operation, the result of this operation is the product of the operands. To determine the data type of the result of a multiplication expression, VBA uses the same rules as for expressions that use addition. In multiplication expressions, all Variant variables containing Date values are converted to numeric values.

"Example of arithmetic expressions with the sign "*" 4*10 "multiplying 4 by 10 MyVar*2 "multiplying MyVar by 2 MyVar*OtherVar "multiplying MyVar by OtherVar

Division

The sign (/) is used to perform the division operation, this sign is called the division sign for real numbers. In division expressions, one operand is divided by another, and the result of the division is the quotient.

"Example of arithmetic expressions with "/" sign 10/4 "dividing 10 by 4 MyVar/2 "dividing MyVar by 2 MyVar/OtherVar "dividing MyVar by OtherVar

If any operand in a division expression is Null, then the result of the expression is also Null. The data type in real number division expressions is usually Double, but there is an exception.

In a division expression, if both operands are of type Integer or Single, then the result of the division expression is of type Single. If the result overflows the range for type Single, then VBA converts it to type Double.

Integer division

The (\) sign is used to perform an integer division operation, in which the result of division is always an integer without a fractional part. VBA does not round the quotient of integer division, but simply truncates it to an integer, discarding the fractional part.

The data type of the result of an integer division expression is either Integer or Long. VBA uses the smallest data type that matches the result of the expression.

"Example of arithmetic expressions with the sign "\" 10\4 "dividing 10 by 4; returns 2 MyVar\2 "dividing MyVar by 2 MyVar\OtherVar" dividing MyVar by OtherVar

Modulo division

The (Mod) sign is used to perform modulo division. When dividing modulo, the expression returns only the remainder of the division as an integer.

"Example of signed arithmetic expressions "Mod" 8 Mod 2 "returns 0; 5.1 Mod 3 "returns 2; 6 Mod MyVar "if MyVar contains 3, returns 0

An easy-to-understand explanation of this mathematical operation is given on one of the programmers forums. I’ll quote from there: “imagine that there is a full 50 liter canister and a 3 liter jar. And you start scooping water out of the canister with a jar (you can only draw full jar). I scooped out 48 liters, 2 liters remained. This is the modulo of 50 divided by 3." In other words, 50 Mod 3 returns 2.

The data type of the result of a modulo division expression is Integer or Long. VBA uses the smallest type that is suitable for the result of the expression.

Exponentiation

The sign (^) is used to perform the operation of exponentiation of a number or expression. The exponent shows how many times a number or expression must be multiplied by itself.

"Example of arithmetic expression with "^" sign 3 ^ 3 "is the same as 3*3*3, returns the value 27

Assignment operator (=)

The assignment operator is used to assign the result of an expression to a variable.

The assignment operator has two syntactic forms:

1 Let varname = expression
2 varname = expression

varname- any VBA variable

expression- any VBA expression

The first version of the assignment operator was used in early languages Basic programming. The second option is used in the modern version of VBA.

X = 5 + 7; Y = X + 5 ; Z = X - Y; A=B; I = I + 1

For example, if in the assignment operator A = A +5, the variable A before the assignment operation contained the value 7, then after the operation it will contain the value 12 (7+5).

Things to remember:

You can assign any numeric variable (or expression) to any other numeric variable (or Variant);
If you assign a numeric expression to a typed variable with less precision (for example, Double - Long), VBA rounds the value of the expression to match the precision of the variable receiving the new value;
If a String variable is assigned to a Variant variable that contains a number, VBA automatically converts the number to a string.

Addition operator (+)

The data type of the result of an addition expression is usually the same as the most precise type in that expression. But, there are exceptions:

The result of adding Single and Long is Double;
Adding a Date type to any other data type will always result in a Date;
If the result exceeds the range of type Integer, then VBA converts it to Long;
If the result exceeds the types Long, Single, Date, then VBA converts it to Double;
If any operand in an addition expression is Null, then the result of the addition expression will also be Null.

Let us recall the order of increasing precision for numeric data types: Byte, Integer, Long, Single, Double, Currency.

Subtraction operator (-)

Both operands in a subtraction expression must be numeric variables (expressions) or string expressions that VBA can convert to a number. You can use the subtraction operator to work with dates.

VBA uses the same rules to determine the data type of the result of a subtraction expression as it does for expressions that use the addition operator. But, there is an addition:
If both operands in an expression are of type Date, then the result of the expression will be of type Double.

Multiplication operator (*)

Division operator (/)

The floating-point division operator performs ordinary arithmetic division of its operands.

In division expressions, the first operand is divided by the second operand—the result of the division is the quotient.

Both operands in a floating-point division expression must be numeric expressions or strings that VBA can convert to a number.

If at least one operand in a division expression is of type Null, then the result of the division will also be Null.

The data type of a floating-point division operation is Double, except:

Both operands in the division expression are of type Integer or Single - the result is Single;
If the result of the expression does not overflow the range of values for type Single.

Integer division (\)

Integer division differs from floating-point division in that its result is always an integer without a fractional part.

Both operands in an integer division expression must be numeric expressions or strings that VBA can convert to a number.

Before performing an integer division operation, VBA rounds each operand to a number of type Integer or Long (the same type as the result of integer division).

VBA discards (but does not round!) any fractional remainder of the result of an integer division expression. For example, the expressions 22\5 and 24\5 will have the same result = 4.

If at least one operand in an integer division expression is of type Null, then the result of the division will also be Null.

Modulo division (Mod)

Modulo division complements integer division. In modulo division, the expression returns only the remainder of the division operation as an integer.
22 Mod 5 = 2
24 Mod 5 = 4
25 Mod 5 = 0

The remaining properties of modulo division are identical to integer division.

Exponentiation (^)

The exponentiation operator raises a number to a power.

Both operators in the exponentiation expression must be numeric expressions or strings that VBA can convert to numbers.

The operand to the left of the exponentiation operator can only be a negative number if the operand to the right is an integer.

The result of the expression is of type Double.

If at least one operand in an expression is of type Null, then the result of exponentiation will also be Null.

Let's summarize the above.

Arithmetic operations in VBA: addition, subtraction, multiplication, division and exponentiation. Operators and built-in functions VBA Vba remainder mod

Mathematical operators

String operators

Comparison Operators

Logical operators

Built-in functions

Addition

Subtraction

Multiplication

Division

Integer division

Modulo division

Exponentiation

Assignment operator (=)

Addition operator (+)

Subtraction operator (-)

Multiplication operator (*)

Division operator (/)

Integer division (\)

Modulo division (Mod)

Exponentiation (^)

We are in social networks

Categories