Built-in Operators and Functions
The following tables lists the operators and functions supported by the VBAEngine and ExcelEngine:
VBAEngine expression items
Constants
| Name | Syntax | Description |
|---|---|---|
| True | True | Returns True. |
| False | False | Returns False. |
| Nothing | Nothing | Returns Null. |
Operators
| Name | Syntax | Description |
|---|---|---|
| Plus | + | Adds two numbers |
| Concat | & | Generates a string concatenation of two expressions |
| Minus | - | Finds the difference between two numbers or indicates the negative value of a numeric expression |
| Multiply | * | Multiplies two numbers |
| Divide | / | Divides two numbers and returns a floating-point result |
| Modulus | Mod | Divides two numbers and returns only the remainder |
| Equal | = | Returns a Boolean value that indicates whether the left and right expressions are equal |
| GreaterThan | > | Returns a Boolean value that indicates whether the left expression is greater than right expression |
| LessThan | < | Returns a Boolean value that indicates whether the left expression is less than right expression |
| NotEqual | <> | Returns a Boolean value that indicates whether the left and right expressions are not equal |
| GreaterOrEqual | >= | Returns a Boolean value that indicates whether the left expression is greater than right expression or equal |
| LessOrEqual | <= | Returns a Boolean value that indicates whether the left expression is less than right expression or equal |
| And | And | Performs a logical conjunction on two expressions |
| Or | Or | Performs a logical disjunction on two expressions |
| Not | Not | Performs logical negation on an expression |
Aggregate Functions
| Name | Syntax | Description |
|---|---|---|
| Average | Average(value...valueN) | Computes the average of a specified sequence of numbers (or enumerable) |
| Count | Count(value...valueN) | Gets the number of elements actually contained in a specified sequence |
| First | First(value...valueN) | Returns the first element of a specified sequence |
| Last | Last(value...valueN) | Returns the last element of a specified sequence |
| Max | Max(value...valueN) | Returns the maximum value in a specified sequence of numbers (or enumerable) |
| Min | Min(value...valueN) | Returns the minimum value in a specified sequence of numbers (or enumerable) |
| Sum | Sum(value...valueN) | Computes the sum of a specified sequence of numbers (or enumerable) |
Text Functions
| Name | Syntax | Description |
|---|---|---|
| Replace | Replace(string, oldValue, newValue) | Replaces all occurrences of a specified string value with another string value |
| RSet | RSet(string, length) | Returns a new string of a specified length in which the end of the current string is padded with spaces |
| RSet | RSet(string, length, char) | Returns a new string of a specified length in which the end of the current string is padded with a specified character |
| Remove | Remove(string, start) | Deletes all specified characters specified string |
| Remove | Remove(string, start, count) | Deletes all specified characters specified string, beginning at a specified position |
| LSet | LSet(string, length) | Returns a new string of a specified length in which the beginning of the current string is padded with spaces |
| LSet | LSet(string, length, char) | Returns a new string of a specified length in which the beginning of the current string is padded with a specified character |
| UCase | UCase(string) | Returns a character expression with lowercase character data converted to uppercase |
| LCase | LCase(string) | Returns a character expression after converting uppercase character data to lowercase |
| Insert | Insert(string, index, value) | Returns a new string in which a specified string is inserted at a specified index position in this instance |
| Len | Len(string) | Returns the number of characters of the specified string expression |
| Trim | Trim(string) | Removes spaces from text |
| StartsWith | StartsWith(string, value) | Determines whether the beginning of this string instance matches a specified string |
| StrReverse | StrReverse(string) | Returns the reverse order of a string value |
| EndsWith | EndsWith(string, value) | Determines whether the end of this string instance matches a specified string |
| Contains | Contains(string, value) | Returns a value indicating whether a specified substring occurs within this string |
| InStr | InStr(string, value) | Searches an expression for another expression and returns its starting position if found |
| InStr | InStr(string, value, start) | Searches an expression for another expression and returns its starting position if found, starting from the specified position |
| Mid | Mid(string, start) | Returns a string that contains all the characters starting from a specified position in a string |
| Mid | Mid(string, start, length) | Returns a string that contains a specified number of characters starting from a specified position in a string |
| Chr | Chr(string) | Converts an int ASCII code to a character |
| Asc | Asc(string) | Returns the ASCII code value of the leftmost character of a character expression |
| Concat | Concat(string, value...valueN) | Returns a string that is the result of concatenating two or more string values |
DateTime Functions
| Name | Syntax | Description |
|---|---|---|
| Now | Now() | Gets a System.DateTime object that is set to the current date and time on this computer, expressed as the local time |
| Today | Today() | Gets the current date |
| AddDays | AddDays(date, days) | Returns a new System.DateTime that adds the specified number of days to the specified System.DateTime value |
| AddHours | AddHours(date, hours) | Returns a new System.DateTime that adds the specified number of hours to the specified System.DateTime value |
| AddMilliSeconds | AddMilliSeconds(date, milliseconds) | Returns a new System.DateTime that adds the specified number of milliseconds to the specified System.DateTime value |
| AddMinutes | AddMinutes(date, minutes) | Returns a new System.DateTime that adds the specified number of minutes to the specified System.DateTime value |
| AddMonths | AddMonths(date, months) | Returns a new System.DateTime that adds the specified number of months to the specified System.DateTime value |
| AddSeconds | AddSeconds date, seconds) | Returns a new System.DateTime that adds the specified number of seconds to the specified System.DateTime value |
| AddTicks | AddTicks(date, ticks) | Returns a new System.DateTime that adds the specified number of ticks to the specified System.DateTime value |
| AddTimeSpan | AddTimeSpan(date, timespan) | Returns a new System.DateTime that adds the specified number of System.TimeSpan to the specified System.DateTime value |
| AddYears | AddYears(date, years) | Returns a new System.DateTime that adds the specified number of years to the specified System.DateTime value |
| DateDiffDay | DateDiffDay(date1, date2) | Counts the number of day boundaries between two non-nullable dates |
| DateDiffHour | DateDiffHour(date1, date2) | Counts the number of hour boundaries between two non-nullable dates |
| DateDiffMilliSecond | DateDiffMilliSecond(date1, date2) | Counts the number of milliseconds boundaries between two non-nullable dates |
| DateDiffMinute | DateDiffMinute(date1, date2) | Counts the number of minutes boundaries between two non-nullable dates |
| DateDiffSecond | DateDiffSecond(date1, date2) | Counts the number of seconds boundaries between two non-nullable dates |
| DateDiffTick | DateDiffTick(date1, date2) | Counts the number of ticks boundaries between two non-nullable dates |
| GetDate | GetDate (date) | Gets the date component of specified System.DateTime value |
| GetDay | GetDay(date) | Gets the day of the month represented by specified System.DateTime value |
| GetDayOfWeek | GetDayOfWeek(date) | Gets the day of the week represented by the specified System.DateTime value |
| GetDayOfYear | GetDayOfYear(date) | Gets the day of the year represented by the specified System.DateTime value |
| GetHour | GetHour(date) | Gets the hour component of the specified System.DateTime value |
| GetMilliSecond | GetMilliSecond(date) | Gets the milliSecond component of the specified System.DateTime value |
| GetMinute | GetMinute(date) | Gets the minute component of the specified System.DateTime value |
| GetMonth | GetMonth(date) | Gets the month component of the specified System.DateTime value |
| GetSecond | GetSecond(date) | Gets the seconds component of the specified System.DateTime value |
| GetTimeOfDay | GetTimeOfDay(date) | Gets he time of day for the specified System.DateTime value |
| GetYear | GetYear(date) | Gets the year component of the specified System.DateTime value |
| UtcNow | UtcNow() | Gets a System.DateTime object that is set to the current date and time on this computer, expressed as the Coordinated Universal Time (UTC) |
Logical Functions
| Name | Syntax | Description |
|---|---|---|
| IsNull | IsNull(value) | Returns True if the specified Value is NULL |
| Iif | Iif(condition, resultTrue, resultFalse) | Returns the evaluation of one of two expressions, depending on the condition |
Math Functions
| Name | Syntax | Description |
|---|---|---|
| Abs | Abs(number) | Returns the absolute value of a number |
| Round | Round(value, digits) | Rounds a double-precision floating-point value to a specified number of fractional digits |
| Acos | Acos(number) | Returns he angle whose cosine is the specified number |
| Asin | Asin(number) | Returns the angle whose sine is the specified number |
| Atan | Atan(number) | Returns the angle whose tangent is the specified number |
| Atan2 | Atan2(x,y) | Returns the angle whose tangent is the quotient of two specified numbers |
| Ceiling | Ceiling(number) | Returns the smallest integral value that's greater than or equal to the specified decimal or double |
| Cos | Cos(number) | Returns the cosine of the specified angle |
| Cosh | Cosh(angle) | Returns the hyperbolic cosine of the specified angle |
| Exp | Exp(power) | Returns e (the base of natural logarithms) raised to the specified power |
| Floor | Floor(number) | Returns the largest integer that's less than or equal to the specified decimal or double number |
| Log10 | Log10(number) | Returns the base 10 logarithm of a specified number |
| Log | Log(number) | Returns the natural (base e) logarithm of a specified number |
| Log | Log(number, base) | Returns the logarithm of a specified number in a specified base |
| Pow | Pow(number, power) | Returns a specified number raised to the specified power |
| Rand | Rand() | Returns a nonnegative random number |
| RandBetween | RandBetween (min, max) | Returns a random number within a specified range |
| Sign | Sign(number) | Returns an integer value indicating the sign of a number |
| Sin | Sin(angle) | Returns the sine of the specified angle |
| Sinh | Sinh(angle) | Returns the hyperbolic sine of the specified angle |
| Sqrt | Sqrt(number) | Returns the square root of a specified number |
| Tan | Tan(angle) | Returns the tangent of the specified angle |
| Tanh | Tanh(angle) | Returns the hyperbolic tangent of the specified angle |
Convert Functions
| Name | Syntax | Description |
|---|---|---|
| CBool | CBool(string) | Converts the specified string representation of a logical value to its System.Boolean equivalent, or throws an exception if the string is not equivalent to the value of System.Boolean.TrueString or System.Boolean.FalseString |
| CByte | CByte(string) | Converts the string representation of a number to its System.Byte equivalent |
| CChar | CChar(string) | Converts the value of the specified string to its equivalent Unicode character |
| CDate | CDate(string) | Converts the specified string representation of a date and time to its System.DateTime equivalent |
| CDbl | CDbl(string) | Converts the string representation of a number to its double-precision floating-point number equivalent |
| CDec | CDec(string) | Converts the string representation of a number to its System.Decimal equivalent |
| CInt | CInt(string) | Converts the string representation of a number to its 32-bit signed integer equivalent |
| CLng | CLng(string) | Converts the string representation of a number to its 64-bit signed integer equivalent |
| CObj | CObj(value) | Returns the specified element as System.Object |
| CSByte | CSByte(string) | Converts the string representation of a number to its 8-bit signed integer equivalent |
| CShort | CShort(string) | Converts the string representation of a number to its 16-bit signed integer equivalent |
| CSng | CSng(string) | Converts the string representation of a number to its single-precision floating-point number equivalent |
| CStr | CStr(value) | Tries to evaluate the specified expression and return result as a string |
| CType | CType(value,type) | Returns an object of the specified type and whose value is equivalent to the specified object |
| CUint | CUint(string) | Converts the string representation of a number to its 32-bit unsigned integer equivalent |
| CULong | CULong(string) | Converts the string representation of a number to its 64-bit unsigned integer equivalent |
| CUShort | CUShort(string) | Converts the string representation of a number to its 16-bit unsigned integer equivalent |
ExcelEngine expression items
Operator
| Name | Syntax | Description |
|---|---|---|
| Plus | + | Adds two numbers |
| Concat | & | Generates a string concatenation of two expressions |
| Minus | - | Finds the difference between two numbers or indicates the negative value of a numeric expression |
| Multiply | * | Multiplies two numbers |
| Divide | / | Divides two numbers and returns a floating-point result |
| Equal | = | Returns a Boolean value that indicates whether the left and right expressions are equal |
| GreaterThan | > | Returns a Boolean value that indicates whether the left expression is greater than right expression |
| LessThan | < | Returns a Boolean value that indicates whether the left expression is less than right expression |
| NotEqual | <> | Returns a Boolean value that indicates whether the left and right expressions are not equal |
| GreaterOrEqual | >= | Returns a Boolean value that indicates whether the left expression is greater than right expression or equal |
| LessOrEqual | <= | Returns a Boolean value that indicates whether the left expression is less than right expression or equal |
Aggregate Functions
| Name | Syntax | Description |
|---|---|---|
| Average | Average(value...valueN) | Computes the average of a specified sequence of numbers (or enumerable) |
| Count | Count(value...valueN) | Gets the number of elements actually contained in a specified sequence |
| Max | Max(value...valueN) | Returns the maximum value in a specified sequence of numbers (or enumerable) |
| Min | Min(value...valueN) | Returns the minimum value in a specified sequence of numbers (or enumerable) |
| Sum | Sum(value...valueN) | Computes the sum of a specified sequence of numbers (or enumerable) |
Text Functions
| Name | Syntax | Description |
|---|---|---|
| Char | Char(string) | Returns the character specified by the code number |
| Code | Code(char) | Returns the character specified by the code number |
| Concat | Concat(string, value...valueN) | Returns a string that is the result of concatenating two or more string values |
| Find | Find(value, string) | Finds one text value within another (case-sensitive) |
| Find | Find(value, string, start) | Finds one text value within another (case-sensitive). The search starts at a specified position. |
| Left | Left(string) | Returns the left character from a text value |
| Left | Left(string, Length) | Returns the leftmost characters from a text value |
| Len | Len(string) | Returns the number of characters of the specified string expression |
| Lower | Lower(string) | Converts text to lowercase |
| Mid | Mid(string, start) | Returns a string that contains all the characters starting from a specified position in a string |
| Mid | Mid(string, start, Length) | Returns a string that contains a specified number of characters starting from a specified position in a string |
| Proper | Proper(string) | Capitalizes the first letter in each word of a text value |
| Replace | Replace(string, oldValue, newValue) | Replaces all occurrences of a specified string value with another string value |
| Rept | Rept(string, count) | Repeats text a given number of times |
| Right | Right(string) | Returns the right character from a text value |
| Right | Right(string, Length) | Returns the rightmost characters from a text value |
| Search | Search(value, string) | Finds one text value within another (not case-sensitive) |
| Search | Search(value, string, start) | Finds one text value within another (not case-sensitive) The search starts at a specified position. |
| Substitute | Substitute(string, oldValue, newValu) | Substitutes new text for old text in a text string |
| Substitute | Substitute(string, oldValue, newValu, index) | Substitutes new text for old text in a text string for specified position of old text instance |
| T | T(string) | Converts its arguments to text |
| Trim | Trim(string) | Removes spaces from text |
DateTime Functions
| Name | Syntax | Description |
|---|---|---|
| Now | Now() | Gets a System.DateTime object that is set to the current date and time on this computer, expressed as the local time |
| Today | Today() | Gets the current date |
Logical Functions
| Name | Syntax | Description |
|---|---|---|
| If | If(condition, resultTrue, resultFalse) | Specifies a logical test to perform |
| And | And(value...valueN) | Returns TRUE if all of its arguments are TRUE |
| Or | Or(value...valueN) | Returns TRUE if any argument is TRUE |
| Not | Not(value) | Reverses the logic of its argument |
| True | True() | Returns the logical value TRUE |
| False | False() | Returns the logical value FALSE |
Math Functions
| Name | Syntax | Description |
|---|---|---|
| Abs | Abs(number) | Returns the absolute value of a number |
| Acos | Acos(number) | Returns he angle whose cosine is the specified number |
| Acosh | Acosh(number) | Returns the arccosine of a number |
| Acot | Acot(number) | Returns the arccotangent of a number |
| Acoth | Acoth(number) | Returns he hyperbolic arccotangent of a number |
| Asin | Asin(number) | Returns the angle whose sine is the specified number |
| Asinh | Asinh(number) | Returns the inverse hyperbolic sine of a number |
| Atan | Atan(number) | Returns the angle whose tangent is the specified number |
| Atanh | Atanh(number) | Returns the inverse hyperbolic tangent of a number. |
| Atan2 | Atan2(x,y) | Returns the angle whose tangent is the quotient of two specified numbers |
| Ceiling | Ceiling(number) | Returns the smallest integral value that's greater than or equal to the specified decimal or double |
| Cos | Cos(number) | Returns the cosine of the specified angle |
| Cosh | Cosh(angle) | Returns the hyperbolic cosine of the specified angle |
| Cot | Cot(angle) | Returns the cotangent of an angle |
| Coth | Coth(angle) | Returns the hyperbolic cotangent of a number |
| Csc | Csc(angle) | Returns the cosecant of an angle |
| Csch | Csch(angle) | Returns the hyperbolic cosecant of an angle |
| Degrees | Degrees(angle) | 'Converts radians to degrees |
| Exp | Exp(power) | Returns e (the base of natural logarithms) raised to the specified power |
| Fact | Fact(number) | Returns the factorial of a number |
| Factdouble | Factdouble(number) | Returns the double factorial of a number |
| Floor | Floor(number) | Returns the largest integer that's less than or equal to the specified decimal or double number |
| Log10 | Log10(number) | Returns the base 10 logarithm of a specified number |
| Log | Log(number) | Returns the natural (base e) logarithm of a specified number |
| Log | Log(number, base) | Returns the logarithm of a specified number in a specified base |
| Int | Int(number) | Rounds a number down to the nearest integer |
| Ln | Ln(number, base) | Returns the natural logarithm of a number |
| Mod | Mod(number, number) | Returns the remainder from division |
| Pi | Pi() | Returns the value of pi |
| Pow | Pow(number, power) | Returns a specified number raised to the specified power |
| Quotient | Quotient(number, number) | Returns the integer portion of a division |
| Rand | Rand() | Returns a nonnegative random number |
| RandBetween | RandBetween (min, max) | Returns a random number within a specified range |
| Radians | Radians(number) | Converts degrees to radians |
| RoundDown | RoundDown(number, number) | Rounds a number down, toward zero |
| RoundUp | RoundUp(number, number) | Rounds a number up, away from zero |
| Sign | Sign(number) | Returns an integer value indicating the sign of a number |
| Sin | Sin(angle) | Returns the sine of the specified angle |
| Sinh | Sinh(angle) | Returns the hyperbolic sine of the specified angle |
| Sqrt | Sqrt(number) | Returns the square root of a specified number |
| Tan | Tan(angle) | Returns the tangent of the specified angle |
| Tanh | Tanh(angle) | Returns the hyperbolic tangent of the specified angle |
| Trunc | Trunc(number, number) | Truncates a number to an integer |