ExcelFunctions.net

Excel VBA Tutorial Part 7 - VBA Operators & Built-In Functions

Home » Excel-VBA-Tutorial » VBA-Operators-And-Functions

Excel VBA Operators

There are a number of built-in VBA operators that can be used in Excel VBA code. These are mathematical operators, string operators, comparison operators and logical operators. These are discussed individually below.


Mathematical Operators

The main Mathematical VBA operators are listed in the table below. Note that, the precedences that are listed alongside the operators are the defaults, which are applied in the absence of brackets. However, the order in which the VBA operators are applied can be controlled by adding brackets to an expression:

Operator Action Precedence
(1=top; 5=bottom)
^ The power operator 1
* The multiplication operator 2
/ The division operator 2
\ The integer division operator - this operator divides two numbers and returns the integer result (eg. 7\4 gives a result of 1) 3
Mod The modulus operator - this operator divides two numbers and returns the remainder (eg. 8 Mod 3 gives a result of 2) 4
+ The addition operator 5
- The subtraction operator 5

String Operators

The main string operator in Excel VBA is the concatenate operator, &:

Operator Action
& The concatenate operator (eg. "A" & "B" gives the result "AB")


VBA Comparison Operators

Comparison operators compare two numbers or strings and return a logical (True or False) result. The main Excel VBA comparison operators are listed in the table below:

Operator Action
= Equal To
< > Not Equal To
< Less Than
> Greater Than
<= Less Than or Equal To
>= Greater Than or Equal To


VBA Logical Operators

Comparison operators also return a logical (True or False) result. The main Excel VBA logical operators are listed in the table below:

Operator Action
And Logical Operator And (eg. the expression 'A And B' returns True if BOTH A AND B are true and returns False otherwise)
Or Logical Operator Or (eg. the expression 'A Or B' returns True if EITHER A OR B is true and returns False otherwise)
Not Negates an evaluation (eg. the expression 'Not A' returns True if A is false and returns False if A is true)


It should be noted that the above tables are not an exhaustive list of VBA operators. A more complete list can be obtained from the Visual Basic Developer Center Website


Built-In Functions

VBA also has a large number of built-in functions that are available to be used in your VBA code. Some of the more commonly used VBA functions are listed below.

Function Action
Abs If supplied with a numeric value, the Abs function returns the absolute numeric value, with a positive sign
eg.Abs(-20) returns the value 20
Abs(20) returns the value 20
Chr If supplied with a numeric value, the Chr function returns ansi character associated with that value
eg.Chr(10) returns a line break
Chr(97) returns the character "a"
Date Returns the current system date
DateAdd

Adds a specified time period to a date.

The function has the format DateAdd(Interval, Number, Date),

where the Interval argument specifies the type of time interval to be added to the supplied date. This can takes on one of the following values:
Interval Represents
yyyyyear
qquarter
mmonth
yday of the year
dday
wweekday
wwweek
hhour
nminute
ssecond

eg. DateAdd("d", 32, "01/01/2009") adds 32 days to the date "01/01/2009" and so returns the date "02/02/2009"
DateAdd("ww", 8, "01/01/2009") adds 88 weeks to the date "01/01/2009" and so returns the date "09/09/2010"
DateDiff Calculates the number of specified intervals between now and a supplied date
eg. DateDiff("d", "01/01/2009", "02/02/2009") calculates the number of days between the dates "01/01/2009" and "02/02/2009", and so returns the value 32
DateDiff("d", "01/01/2009", "09/09/2010") calculates the number of weeks between the dates "01/01/2009" and "09/09/2010", and so returns the value 88
Day Returns the day of the month for a supplied date
eg. Day("01/29/2009") returns the value 29
Hour Returns the Hour portion of a supplied time
eg. Hour("22:45:00") returns the value 22
InStr If supplied with an integer, n, and 2 strings, the InStr function returns the position of the second string within the first string, starting the search from the n'th position in the first string
eg. InStr(1, "Here is the searchword", "searchword") returns the value 13
InStr(14, "Here is the searchword but here is another searchword", "searchword") returns the value 44
Int If supplied with a numeric value, the Int function returns the integer part of the value
eg. Int(5.79) returns the value 5
Isdate If supplied with a value, the IsDate function returns True if the value is a date, and False otherwise
eg. IsDate("01/01/2009") returns the value True
IsDate(100) returns the value False
IsError If supplied with a value, the IsError function returns True if the value is an error, and False otherwise
IsMissing If supplied with the name of an optional argument to a function, the IsMissing function returns True if no argument value was passed to the function
IsNumeric If supplied with a value, the IsNumeric function returns True if the value can be evaluated as a number
Left

Returns a specified number of characters from the start of a supplied string

The format of the function is Left(String, Length),

where String is the original text string and Length is the number of characters to be returned from the start of the supplied String
eg. Left("abcdefghijklm", 4) returns the string "abcd"
Left("abcdefghijklm", 1) returns the string "a"
Len If supplied with a text string, the Len function returns the length of the string
eg. Len("abcdefg") returns the value 7
Month Returns the month number for a supplied date
eg. Month("01/29/2009") returns the value 1
Mid

Returns a specified number of characters from the middle of a supplied string

The format of the function is Mid(String, Start, Length),

where String is the original text string, Start is the start position of the section of String that is to be returned and Length is the length of the returned string
eg. Mid("abcdefghijklm", 4, 5) returns the string "defgh"
Mid("abcdefghijklm", 10, 2) returns the string "jk"
Minute Returns the Minute portion of a supplied time
eg. Minute("22:45:15") returns the value 45
Now Returns the current system date and time
Right

Returns a specified number of characters from the end of a supplied string

The format of the function is Right(String, Length),

where String is the original text string and Length is the number of characters to be returned from the end of the supplied String
eg. Right("abcdefghijklm", 4) returns the string "jklm"
Right("abcdefghijklm", 1) returns the string "m"
Second Returns the Second portion of a supplied time
eg. Second("22:45:15") returns the value 15
Sqr If supplied with numeric value, the Sqr function returns the square root of that value
eg. Sqr(4) returns the value 2
Sqr(16) returns the value 4
Time Returns the current system time
Ubound If supplied with an array, the UBound function returns the upper subscript of the array
Year Returns the year of a supplied date
eg. Year("01/29/2009") returns the value 2009


Note that the above list only provides a selection of the more commonly used Excel Visual Basic built-in functions. If you want a more comprehensive list of the VBA functions available for use in your Excel Macros, this can be found on the Visual Basic Developer Center website or the Tech on the Net website.