Awọn akoonu
Excel VBA statements
When writing VBA code in Excel, a set of built-in operators is 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.
Awọn oniṣẹ Mathematiki
The main 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 you wish.
| onišẹ | Action | ni ayo (1 – highest; 5 – lowest) |
|---|---|---|
| ^ | exponentiation operator | 1 |
| * | onišẹ isodipupo | 2 |
| / | division operator | 2 |
| Division without remainder – returns the result of dividing two numbers without a remainder. For example, 74 will return the result 1 | 3 | |
| ìgboyà | Modulo (remainder) operator – returns the remainder after dividing two numbers. For example, 8 Against 3 will return the result 2. | 4 |
| + | Oṣiṣẹ afikun | 5 |
| - | subtraction operator | 5 |
Awọn oniṣẹ okun
The basic string operator in Excel VBA is the concatenation operator & (merge):
| onišẹ | Action |
|---|---|
| & | concatenation operator. For example, the expression «A» & «B» will return the result AB. |
Awọn oniṣẹ Ifiwera
Comparison operators are used to compare two numbers or strings and return a boolean value of type Bolianu (True or False). The main Excel VBA comparison operators are listed in this table:
| onišẹ | Action |
|---|---|
| = | Bakanna |
| <> | Ko dogba |
| < | Ti o kere |
| > | Alaye diẹ |
| <= | Kere ju tabi dọgba |
| >= | Ti o tobi ju tabi dọgba |
Logical operators
Logical operators, like comparison operators, return a boolean value of type Bolianu (True or False). The main logical operators of Excel VBA are listed in the table below:
| onišẹ | Action |
|---|---|
| ati | conjunction operation, logical operator И. For example, the expression A And B yoo pada otitọ, ti o ba A и B both are equal otitọ, otherwise return eke. |
| Or | Disjunction operation, logical operator OR. For example, the expression A Or B yoo pada otitọ, ti o ba A or B dogba otitọ, and will return eke, ti o ba A и B both are equal eke. |
| ko | Negation operation, logical operator NOT. For example, the expression Not A yoo pada otitọ, ti o ba A bakanna eke, or return eke, ti o ba A bakanna otitọ. |
The table above does not list all the logical operators available in VBA. A complete list of logical operators can be found at the Visual Basic Developer Center.
Built-in Functions
There are many built-in functions available in VBA that can be used when writing code. Listed below are some of the most commonly used:
| iṣẹ | Action | ||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| abs | Returns the absolute value of the given number. apere:
| ||||||||||||||||||||||
| BẸN | Returns the ANSI character corresponding to the numeric value of the parameter. apere:
| ||||||||||||||||||||||
| ọjọ | Returns the current system date. | ||||||||||||||||||||||
| DateAdd | Adds a specified time interval to the given date. Function syntax:
Where is the argument aarin determines the type of time interval added to the given ọjọ in the amount specified in the argument nọmba. Ọrọ ariyanjiyan aarin can take one of the following values:
apere:
| ||||||||||||||||||||||
| DateDiff | Calculates the number of specified time intervals between two given dates. apere:
| ||||||||||||||||||||||
| Day | Returns an integer corresponding to the day of the month in the given date. apere: Day(«29/01/2015») returns the number 29. | ||||||||||||||||||||||
| wakati | Returns an integer corresponding to the number of hours at the given time. apere: Hour(«22:45:00») returns the number 22. | ||||||||||||||||||||||
| InStr | It takes 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 given by an integer. apere:
akiyesi: 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. apere: Int(5.79) returns result 5. | ||||||||||||||||||||||
| Isdate | padà otitọif the given value is a date, or eke – if the date is not. apere:
| ||||||||||||||||||||||
| IsError | padà otitọif the given value is an error, or eke – if it is not an error. | ||||||||||||||||||||||
| IsMissing | The name of an optional procedure argument is passed as an argument to the function. IsMissing padà otitọif no value was passed for the procedure argument in question. | ||||||||||||||||||||||
| IsNumeric | padà otitọif the given value can be treated as a number, otherwise returns eke. | ||||||||||||||||||||||
| osi | Returns the specified number of characters from the beginning of the given string. The function syntax is like this:
ibi ti ila is the original string, and ipari is the number of characters to return, counting from the beginning of the string. apere:
| ||||||||||||||||||||||
| Len | Returns the number of characters in a string. apere: Len(“abcdej”) returns the number 7. | ||||||||||||||||||||||
| osù | Returns an integer corresponding to the month of the given date. apere: Month(«29/01/2015») pada iye 1. | ||||||||||||||||||||||
| Aarin | Returns the specified number of characters from the middle of the given string. Function syntax: Mid(ila, ibere, ipari) ibi ti ila is the original string ibere – the position of the beginning of the string to be extracted, ipari is the number of characters to be extracted. apere:
| ||||||||||||||||||||||
| Minute | Returns an integer corresponding to the number of minutes in the given time. Example: Minute(«22:45:15») pada iye 45. | ||||||||||||||||||||||
| bayi | Returns the current system date and time. | ||||||||||||||||||||||
| ọtun | Returns the specified number of characters from the end of the given string. Function syntax: Right(ila, ipari) ibi ti ila is the original string, and ipari is the number of characters to extract, counting from the end of the given string. apere:
| ||||||||||||||||||||||
| keji | Returns an integer corresponding to the number of seconds in the given time. apere: Second(«22:45:15») pada iye 15. | ||||||||||||||||||||||
| Sqr | Returns the square root of the numeric value passed in the argument. apere:
| ||||||||||||||||||||||
| Time | Returns the current system time. | ||||||||||||||||||||||
| Ubound | Returns the superscript of the specified array dimension. akiyesi: For multidimensional arrays, an optional argument may be the index of which dimension to return. If not specified, the default is 1. | ||||||||||||||||||||||
| odun | Returns an integer corresponding to the year of the given date. Example: Year(«29/01/2015») pada iye 2015. |
This list includes only a selection of the most commonly used built-in Excel Visual Basic functions. An exhaustive list of VBA functions available for use in Excel macros can be found on the Visual Basic Developer Center.