VBA Operators and Built-in Functions

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.

Mathematical Operators

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.

OperatorActionpriority

(1 – highest; 5 – lowest)

^exponentiation operator1
*multiplication operator2
/division operator2
Division without remainder – returns the result of dividing two numbers without a remainder. For example, 74 will return the result 13
CourageModulo (remainder) operator – returns the remainder after dividing two numbers. For example, 8 Against 3 will return the result 2.4
+Addition operator5
—subtraction operator5

String Operators

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

OperatorAction
&concatenation operator. For example, the expression «A» & «B» will return the result AB.

Comparison Operators

Comparison operators are used to compare two numbers or strings and return a boolean value of type Boolean (True or False). The main Excel VBA comparison operators are listed in this table:

OperatorAction
=Equally
<>Not equal
<Less
>Больше
<=Less than or equal
>=Greater than or equal

Logical operators

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

OperatorAction
Andconjunction operation, logical operator И. For example, the expression A And B will return True, if A и B both are equal True, otherwise return False.
OrDisjunction operation, logical operator OR. For example, the expression A Or B will return True, if A or B are equal True, and will return False, if A и B both are equal False.
NotNegation operation, logical operator NOT. For example, the expression Not A will return True, if A equally False, or return False, if A equally True.

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:

FunctionAction
AbsReturns the absolute value of the given number.

Example:

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

Example:

  • Chr(10) returns a line break;
  • Chr(97) returns a character a.
DateReturns the current system date.
DateAddAdds a specified time interval to the given date. Function syntax:

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

Where is the argument interval determines 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:

IntervalValue
yyyyyear
qquarter
mmonth
yday of the year
dday
wday of the week
wwweek
hhour
nminute
ssecond

Example:

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

Example:

  • 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.
DayReturns an integer corresponding to the day of the month in the given date.

Example: Day(«29/01/2015») returns the number 29.

hourReturns an integer corresponding to the number of hours at the given time.

Example: 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.

Example:

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

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.

IntReturns the integer part of the given number.

Example: Int(5.79) returns result 5.

IsdateReturns Trueif the given value is a date, or False – if the date is not.

Example:

  • IsDate(«01/01/2015») returns True;
  • IsDate(100) returns False.
IsErrorReturns Trueif the given value is an error, or False – if it is not an error.
IsMissingThe name of an optional procedure argument is passed as an argument to the function. IsMissing returns Trueif no value was passed for the procedure argument in question.
IsNumericReturns Trueif the given value can be treated as a number, otherwise returns False.
LeftReturns the specified number of characters from the beginning of the given string. The function syntax is like this:

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

where line is the original string, and length is the number of characters to return, counting from the beginning of the string.

Example:

  • Left(“abvgdejziklmn”, 4) returns the string “abcg”;
  • Left(“abvgdejziklmn”, 1) returns the string “a”.
LenReturns the number of characters in a string.

Example: Len(“abcdej”) returns the number 7.

MonthReturns an integer corresponding to the month of the given date.

Example: Month(«29/01/2015») returns the value 1.

MidReturns the specified number of characters from the middle of the given string. Function syntax:

Mid(line, start, length)

where line is the original string start – the position of the beginning of the string to be extracted, length is the number of characters to be extracted.

Example:

  • Mid(“abvgdejziklmn”, 4, 5) returns the string “where”;
  • Mid(“abvgdejziklmn”, 10, 2) returns the string “cl”.
MinuteReturns an integer corresponding to the number of minutes in the given time. Example: Minute(«22:45:15») returns the value 45.
NowReturns the current system date and time.
RightReturns the specified number of characters from the end of the given string. Function syntax:

Right(line, length)

Where line is the original string, and length is the number of characters to extract, counting from the end of the given string.

Example:

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

Example: Second(«22:45:15») returns the value 15.

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

Example:

  • Sqr(4) returns the value 2;
  • Sqr(16) returns the value 4.
TimeReturns the current system time.
UboundReturns the superscript of the specified array dimension.

Note: For multidimensional arrays, an optional argument may be the index of which dimension to return. If not specified, the default is 1.

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

Leave a Reply