וובאַ אָפּערייטערז און געבויט-אין פאַנגקשאַנז

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 operator1
*קייפל אָפּעראַטאָר2
/division operator2
Division without remainder – returns the result of dividing two numbers without a remainder. For example, 74 will return the result 13
מוטModulo (remainder) operator – returns the remainder after dividing two numbers. For example, 8 Against 3 will return the result 2.4
+Addition operator5
-subtraction operator5

שטריקל אָפּעראַטאָרס

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 פאַלש.
OrDisjunction 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.

בייַשפּיל:

  • Abs(-20) returns the value 20;
  • Abs(20) returns the value 20.
בקReturns the ANSI character corresponding to the numeric value of the parameter.

בייַשפּיל:

  • Chr(10) returns a line break;
  • Chr(97) returns a character a.
דאַטעReturns the current system date.
DateAddAdds a specified time interval to the given date. Function syntax:

DateAdd(интервал, число, дата)

וואו איז דער טענה מעהאַלעך determines the type of time interval added to the given דאַטע in the amount specified in the argument נומער.

אַרגומענט מעהאַלעך can take one of the following values:

מעהאַלעךווערט
yyyyיאָר
qפערטל
mמאָנאַט
yday of the year
dטאָג
wטאָג פון דער וואָך
wwוואָך
hשאָ
nמינוט
sצווייט

בייַשפּיל:

  • 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.
DateDiffCalculates the number of specified time intervals between two given dates.

בייַשפּיל:

  • DateDiff(«d», «01/01/2015», «02/02/2015») calculates the number of days between 01/01/2015 and 02/02/2015, returns 32.
  • DateDiff(«ww», «01/01/2015», «03/03/2016») calculates the number of weeks between 01/01/2015 and 03/03/2016, returns 61.
טאָג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.

InStrIt 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.

בייַשפּיל:

  • InStr(1, “Here is the search word”, “word”) returns the number 13.
  • InStr(14, “Here is the search word, and here is another search word”, “word”) returns the number 38.

נאטיץ: 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.

בייַשפּיל:

  • IsDate(«01/01/2015») קערט ריכטיק;
  • IsDate(100) קערט פאַלש.
IsErrorקערט ריכטיקif the given value is an error, or פאַלש – if it is not an error.
IsMissingThe 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:

Left(строка, длина)

ווו ליניע is the original string, and לענג is the number of characters to return, counting from the beginning of the string.

בייַשפּיל:

  • Left(“abvgdejziklmn”, 4) returns the string “abcg”;
  • Left(“abvgdejziklmn”, 1) returns the string “a”.
לען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.

בייַשפּיל:

  • Mid(“abvgdejziklmn”, 4, 5) returns the string “where”;
  • Mid(“abvgdejziklmn”, 10, 2) returns the string “cl”.
מינוט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.

בייַשפּיל:

  • Right(«abvgdezhziklmn», 4) returns the string “clmn”;
  • Right(«abvgdezhziklmn», 1) returns the string “n”.
צווייטReturns an integer corresponding to the number of seconds in the given time.

בייַשפּיל: Second(«22:45:15») returns the value 15.

SqrReturns the square root of the numeric value passed in the argument.

בייַשפּיל:

  • Sqr(4) returns the value 2;
  • Sqr(16) returns the value 4.
צייַטReturns the current system time.
UboundReturns 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.

לאָזן אַ ענטפֿערן