Assignment operator (=)
The assignment operator is used to assign the result of an expression to a variable.
The assignment operation has two syntactic forms:
1 Let varname = expression
2 varname = expression
varname- any VBA variable
expression- any VBA expression
The first variant of the assignment operation was used in the early Basic programming languages. The second option is used in the modern version of VBA.
When an assignment operator is executed, VBA first evaluates the expression to the right of the assignment operator and then stores the result of the expression in a 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
Novice 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, first, the result of evaluating the expression on the right side of the assignment operator is 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).
Remember:
- You can assign any numeric variable (or expression) to any other numeric variable (or Variant variable);
- If you assign a numeric expression to a typed variable with a lower precision (for example, Double - Long), VBA rounds the value of the expression to match the precision of the variable that takes the new value;
- If you assign a String variable to a Variant containing a number, VBA automatically converts that 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 on Date data.
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:
- Adding a Single and a Long will result in a Double;
- Adding Date to any other data type will always result in 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 the addition expression is Null, then the result of the addition expression will also be Null.
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" mistakes when writing code.
Subtraction operator (-)
The subtraction operator does two things: it is used to subtract one number from another; denotes a 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 for determining 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 normal arithmetic division on its operands.
In division expressions, the first operand is divided by the second operand - the quotient is the result of division.
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 the division expression is of type Null, then the result of the division will also be Null.
The datatype of the floating point division is Double, except:
- Both operands in a division expression are of type Integer or Single, resulting in a 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 it always results in 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 result of an integer division is of the same type).
VBA discards (but does not round!) Any fractional remainder 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, as it were, 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 rest of the properties of modulo division are identical to integer division.
Exponentiation (^)
The exponentiation operator raises a number to a power.
Both operators in an 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 negative if the operand to the right is an integer.
The expression result is of type Double.
If at least one operand in the expression is of type Null, then the result of the exponentiation will also be Null.
Let's summarize the above.
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 an expression is one value of a particular data type. Operation signs (designations) are used to operate on specific values in expressions. To assign the result of an expression to a variable, the assignment operator (=) is used, which stores any value 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 the operations below, both operands must be numeric expressions or strings that VBA can convert to a number.
Addition
The (+) sign is used to perform an addition operation. The terms must be numerical expressions, strings that VBA can convert to numbers, or dates with which arithmetic is also possible.
Sub Slozhenie () "An example of an arithmetic expression with a sign" + "Dim A1, A2, A3 As Integer" declaration of variables A1 = 1 "assignment of value 1 to variable A1 A2 = 2" assignment of value 2 to variable A2 A3 = A1 + A2 "assignment of the result adding variable A3 MsgBox A3 "output 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 some exceptions. All exceptions to this rule are clearly shown 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 multiplication by -1). A minus sign that is placed in front of a number to indicate that the number is negative is called a unary minus.
VBA follows the same rules for determining the data type of the result of a subtraction expression as for expressions that use the addition operator, but there are two additional rules.
Rule 1. If one of the operands in the subtraction expression is of type Date, then the result of the expression will also 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 () "An example of a signed arithmetic expression" - "Dim D1, D2 As Date" declaration of variables D1 = Now "assigning the current date to variable D1 D2 = Now-5" assigning 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. VBA uses the same rules for determining the data type of the result of a multiplication expression as for expressions that use addition. In multiplication expressions, all Variant variables containing Date values are converted to numeric values.
"An example of signed arithmetic expressions" * "4 * 10" multiply 4 by 10 MyVar * 2 "multiply MyVar by 2 MyVar * OtherVar" multiply MyVar by OtherVar
Division
The sign (/) is used to perform a division operation, this sign is called the division sign of real numbers. In division expressions, one operand is divided by another, and the quotient is the result of division.
"Example of signed arithmetic expressions" / "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 evaluates to Null, then the expression evaluates to Null as well. The data type in real division expressions is usually Double, but there is an exception.
If both operands in a division expression are of type Integer or Single, then the result of the division expression is of type Single. If the result overflows the range for the Single type, then VBA converts it to the Double type.
Integer division
The character (\) 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 an 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 an expression.
"An example of arithmetic expressions with the sign" \ "10 \ 4" division 10 by 4; returns the value 2 MyVar \ 2 "dividing MyVar by 2 MyVar \ OtherVar" dividing MyVar by OtherVar
Modulo division
The (Mod) sign is used to perform modulo operation. When dividing modulo, the expression returns only the remainder of the division as an integer.
"An 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 will quote from there: "Imagine that there is a full 50 liter canister and a 3 liter jar. And you start to scoop out water from the canister (you can only fill a full jar). I scooped out 48 liters, there are 2 liters left. This is the remainder of dividing 50 by 3 modulo . " In other words, 50 Mod 3 returns 2.
The data type of the result of a modulo expression is Integer or Long. VBA uses the smallest type that is suitable for the result of an expression.
Exponentiation
The ^ sign is used to perform the exponentiation of a number or expression. The exponent indicates how many times a number or expression must be multiplied by itself.
"Signed arithmetic expression example" ^ "3 ^ 3" is the same as 3 * 3 * 3, returns 27
VBA Operators: Arithmetic, Boolean, Comparison, Assignment
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 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 ;
- division modulo (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. It can be written like this:
Let nVar = 10
and it can be even simpler:
nVar = 10
In the second case, do not confuse the equal sign with the equal operator.
Expression
nVar = 10
means "assign the variable nVar value 10", and if the line looks like this:
If (nVar = 10)
it means "if the value of the variable nVar is 10".
If an object needs to be assigned to a variable, it can be 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 and less than or equal (> = 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 against a pattern and determines if 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 case sensitive;
- spaces in string values are also counted;
- when comparing text strings by more / less, by default, simply binary character codes are compared - which ones are greater or less. If you need to use the order that goes in the alphabet, then 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 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 &, 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 Null value gives Null.
MsgBox "Message to user" & vUserName
The order in which operators are used can be controlled using parentheses.
Assignment operator (=)
The assignment operator is used to assign the result of an expression to a variable.
The assignment operation has two syntactic forms:
varname = expression
varname- any VBA variable
expression- any VBA expression
When an assignment operator is executed, VBA first evaluates the expression to the right of the assignment operator and then stores the result of the expression in a 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
Novice 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, first, the result of evaluating the expression on the right side of the assignment operator is 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).
Remember:
· You can assign any numeric variable (or expression) to any other numeric variable (or Variant variable);
· If you assign a numeric expression to a typed variable with a lower precision (for example, Double - Long), VBA rounds the value of the expression to match the precision of the variable that takes the new value;
· If you assign a String variable to a Variant containing a number, VBA automatically converts that 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 on Date data.
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 the addition of type Single and Long will be Double;
· The result of adding the Date type with any other data type will always be 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 the addition expression is Null, then the result of the addition expression will also be Null.
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" mistakes when writing code.
Subtraction operator (-)
The subtraction operator does two things: it is used to subtract one number from another; denotes a 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 for determining 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 the 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 normal arithmetic division on its operands.
In division expressions, the first operand is divided by the second operand - the quotient is the result of division.
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 the division expression is of type Null, then the result of the division will also be Null.
The datatype of the floating point division is Double, except:
Both operands in a division expression are of type Integer or Single, resulting in a 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 it always results in 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 result of an integer division is of the same type).
VBA discards (but does not round!) Any fractional remainder 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, as it were, 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 rest of the 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 an 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 negative if the operand to the right is an integer.
The expression result is of type Double.
If at least one operand in the expression is of type Null, then the result of the exponentiation will also be Null.
To summarize the above:
VBA LOGICAL OPERATORS
Any valid expression that has a Boolean result can be used as the operand for a logical operator, as well as a number that can be converted to a Boolean value.
The result logical operation is a Boolean (or Null if at least one of the operands is Null).
Logical AND operator
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 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 is True only if all operands of the expression are True. Otherwise, the result is 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 OR operator
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 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 a logical OR operation will always be True if at least one of the expression's operands evaluates to True. Otherwise, the result is False.
The AND and OR operators can be combined:
((5<7) AND (4>3)) OR (5 = 6) the result will be True
Logical NOT operator
SYNTAX:
NOT Operand
The NOT operator does logical negation.
The NOT operator uses only one operand.
Truth table
The AND OR NOT operators can be combined:
((5<7) AND (4>3)) OR NOT (5 = 6) the result will be True
Logical XOR operator
SYNTAX:
Operand_1 XOR Operand_2
The XOR operator does logical exclusion.
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) 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 value; otherwise, False.
Truth table
((5<7) AND (4>3)) OR NOT (5 = 6) EQV (5 = 5) the result will be True.
Comparison Operators
Comparison operations are usually used in looping statements to make some kind 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 variables like Variant, VBA tries to convert the Variant to some compatible type.
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 take a closer look at each group of operators.
Math operators
The basic math operators in VBA 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.
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). Basic Operators Excel comparisons VBA are listed in this table:
Logical operators
Logical operators, like comparison operators, return a boolean value of the type Boolean(True or False). Basic Excel VBA Boolean Operators are listed in the table below:
The above table does not list all the logical operators available in VBA. Full list logical 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. Some of the most commonly used are listed below:
| Function | Action |
|---|---|
| Abs | Returns the absolute value of the specified number.
|
| Chr | Returns the ANSI character corresponding to the numeric value of the parameter.
|
| Date | Returns the current system date. |
| DateAdd | Adds a specified time interval to a specified date. Function syntax: 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:
|
| 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. Example: Day (“29/01/2015”) returns number 29. |
| Hour | Returns an integer representing the number of hours at a given time. Example: Hour (“22:45:00”) returns the number 22. |
| 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.
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. |
| Int | Returns the integer portion of the specified number. Example: Int (5.79) returns the result 5. |
| Isdate | Returns True if the given value is a date, or False- if the date is not.
|
| IsError | Returns True if 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 True if no value is passed for the procedure argument in question. |
| IsNumeric | Returns True if the given value can be treated as a number, otherwise returns False. |
| Left | Returns the specified number of characters from the beginning of the passed string. The syntax for the function is as follows: Left ( line , length ) where line Is the original string and length- the number of characters returned, counting from the beginning of the line.
|
| Len | Returns the number of characters in a string. Example: Len ("abvgdezh") returns the number 7. |
| Month | Returns an integer corresponding to the month at the given date. Example: Month (“29/01/2015”) returns the value 1. |
| Mid | Returns the specified number of characters from the middle of the passed string. Function syntax: 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.
|
| Minute | Returns an integer representing the number of minutes in a given 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: 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.
|
| Second | Returns an integer representing the number of seconds in a given time. Example: Second (“22:45:15”) returns 15. |
| Sqr | Returns the square root of the numeric value passed in the argument.
|
| Time | Returns the current system time. |
| Ubound | Returns the superscript of the dimension of the specified array. Note: For multidimensional arrays, you can specify the index of which dimension to return as an optional argument. If not specified, it defaults to 1. |
