There are several built-in VBA operators that can be used in your Excel VBA code. These are mathematical operators, string operators, comparison operators and logical operators. The different types of Operators are discussed individually below.
The main Mathematical VBA operators are listed in the table below.
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:
|^||The power operator||1|
|*||The multiplication operator||2|
|/||The division operator||2|
|\||The integer division operator - divides two numbers and returns the integer result (eg. 7\4 gives a result of 1)||3|
|Mod||The modulus 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|
The main string operator in Excel VBA is the concatenate operator, &:
|&||The concatenate operator (eg. "A" & "B" gives the result "AB")|
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:
|< >||Not Equal To|
|<=||Less Than or Equal To|
|>=||Greater Than or Equal To|
Logical operators also return a logical (True or False) result. The main Excel VBA logical operators are listed in the table below:
|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
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.
Returns the absolute value of a supplied number.
Returns the ansi character associated with a supplied numeric value.
|Date||Returns the current system date.|
The function has the format DateAdd(Interval, Number, Date),
|DateDiff||Calculates the number of specified intervals between now and a supplied date|
|Day||Returns the day of the month for a supplied date|
|Hour||Returns the Hour portion of a supplied time|
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.
Returns the integer part of a numeric value.
Returns True if a supplied value is a date, and False otherwise.
|IsError||Returns True if a supplied 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||Returns True if a supplied value can be evaluated as a number, or False otherwise.|
|Len||Returns the length of a supplied text string.|
|Now||Returns the current system date and time.|
|Second||Returns the Second portion of a supplied time.|
|Time||Returns the current system time.|
|Ubound||Returns the upper subscript of a supplied array.|
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.