ינהאַלט
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.
מאַטאַמאַטיקאַל אָפּערייטערז
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.
אָפּעראַטאָר | קאַמף | בילכערקייַט (1 – highest; 5 – lowest) |
---|---|---|
^ | exponentiation operator | 1 |
* | קייפל אָפּעראַטאָר | 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 | |
מוט | Modulo (remainder) operator – returns the remainder after dividing two numbers. For example, 8 Against 3 will return the result 2. | 4 |
+ | Addition operator | 5 |
- | subtraction operator | 5 |
שטריקל אָפּעראַטאָרס
The basic string operator in Excel VBA is the concatenation operator & (merge):
אָפּעראַטאָר | קאַמף |
---|---|
& | concatenation operator. For example, the expression «A» & «B» will return the result AB. |
פאַרגלייַך אָפּערייטערז
Comparison operators are used to compare two numbers or strings and return a boolean value of type באָאָלעאַן (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 באָאָלעאַן (True or False). The main logical operators of Excel VBA are listed in the table below:
אָפּעראַטאָר | קאַמף |
---|---|
און | conjunction operation, logical operator И. For example, the expression A And B וועט צוריקקומען ריכטיק, אויב A и B both are equal ריכטיק, otherwise return פאַלש. |
Or | Disjunction operation, logical operator OR. For example, the expression A Or B וועט צוריקקומען ריכטיק, אויב A or B זענען גלייך ריכטיק, and will return פאַלש, אויב A и B both are equal פאַלש. |
ניט | Negation operation, logical operator נישט. For example, the expression Not A וועט צוריקקומען ריכטיק, אויב A גלייַך פאַלש, or return פאַלש, אויב A גלייַך ריכטיק. |
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:
פונקציע | קאַמף | ||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
אַבס | Returns the absolute value of the given number. בייַשפּיל:
| ||||||||||||||||||||||
בק | Returns the ANSI character corresponding to the numeric value of the parameter. בייַשפּיל:
| ||||||||||||||||||||||
דאַטע | Returns the current system date. | ||||||||||||||||||||||
DateAdd | Adds a specified time interval to the given date. Function syntax:
וואו איז דער טענה מעהאַלעך determines the type of time interval added to the given דאַטע in the amount specified in the argument נומער. אַרגומענט מעהאַלעך can take one of the following values:
בייַשפּיל:
| ||||||||||||||||||||||
DateDiff | Calculates the number of specified time intervals between two given dates. בייַשפּיל:
| ||||||||||||||||||||||
טאָג | Returns an integer corresponding to the day of the month in the given date. בייַשפּיל: Day(«29/01/2015») returns the number 29. | ||||||||||||||||||||||
שאָ | Returns an integer corresponding to the number of hours at the given time. בייַשפּיל: 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. בייַשפּיל:
נאטיץ: 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. | ||||||||||||||||||||||
ינט | Returns the integer part of the given number. בייַשפּיל: Int(5.79) returns result 5. | ||||||||||||||||||||||
Isdate | קערט ריכטיקif the given value is a date, or פאַלש – if the date is not. בייַשפּיל:
| ||||||||||||||||||||||
IsError | קערט ריכטיקif the given value is an error, or פאַלש – if it is not an error. | ||||||||||||||||||||||
IsMissing | The name of an optional procedure argument is passed as an argument to the function. IsMissing קערט ריכטיקif no value was passed for the procedure argument in question. | ||||||||||||||||||||||
IsNumeric | קערט ריכטיקif the given value can be treated as a number, otherwise returns פאַלש. | ||||||||||||||||||||||
לינקס | Returns the specified number of characters from the beginning of the given string. The function syntax is like this:
ווו ליניע is the original string, and לענג is the number of characters to return, counting from the beginning of the string. בייַשפּיל:
| ||||||||||||||||||||||
לען | Returns the number of characters in a string. בייַשפּיל: Len(“abcdej”) returns the number 7. | ||||||||||||||||||||||
מאָנאַט | Returns an integer corresponding to the month of the given date. בייַשפּיל: Month(«29/01/2015») returns the value 1. | ||||||||||||||||||||||
מיטן | Returns the specified number of characters from the middle of the given string. Function syntax: Mid(ליניע, אָנהייב, לענג) ווו ליניע is the original string אָנהייב – the position of the beginning of the string to be extracted, לענג is the number of characters to be extracted. בייַשפּיל:
| ||||||||||||||||||||||
מינוט | Returns an integer corresponding to the number of minutes in the given time. Example: Minute(«22:45:15») returns the value 45. | ||||||||||||||||||||||
איצט | Returns the current system date and time. | ||||||||||||||||||||||
רעכט | Returns the specified number of characters from the end of the given string. Function syntax: Right(ליניע, לענג) ווו ליניע is the original string, and לענג is the number of characters to extract, counting from the end of the given string. בייַשפּיל:
| ||||||||||||||||||||||
צווייט | Returns an integer corresponding to the number of seconds in the given time. בייַשפּיל: Second(«22:45:15») returns the value 15. | ||||||||||||||||||||||
Sqr | Returns the square root of the numeric value passed in the argument. בייַשפּיל:
| ||||||||||||||||||||||
צייַט | Returns the current system time. | ||||||||||||||||||||||
Ubound | Returns the superscript of the specified array dimension. נאטיץ: For multidimensional arrays, an optional argument may be the index of which dimension to return. If not specified, the default is 1. | ||||||||||||||||||||||
יאָר | Returns an integer corresponding to the year of the given date. Example: Year(«29/01/2015») returns the value 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.