Home » VBA-Functions
Built-In VBA Functions
VBA provides a large number of built-in functions that can be used in your code. The most popular built-in VBA functions are listed below. Select a function name to go to a full description of the function with examples of use.
|
InputBox | Displays a dialog box prompting the user for input. |
MsgBox | Displays a modal message box. |
|
Format | Applies a format to an expression and returns the result as a string. |
InStr | Returns the position of a substring within a string. |
InStrRev | Returns the position of a substring within a string, searching from right to left. |
Left | Returns a substring from the start of a supplied string. |
Len | Returns the length of a supplied string. |
LCase | Converts a supplied string to lower case text. |
LTrim | Removes leading spaces from a supplied string. |
Mid | Returns a substring from the middle of a supplied string. |
Replace | Replaces a substring within a supplied text string. |
Right | Returns a substring from the end of a supplied string. |
RTrim | Removes trailing spaces from a supplied string. |
Space | Creates a string consisting of a specified number of spaces. |
StrComp | Compares two strings and returns an integer representing the result of the comparison. |
StrConv | Converts a string into a specified format. |
String | Creates a string consisting of a number of repeated characters. |
StrReverse | Reverses a supplied string. |
Trim | Removes leading and trailing spaces from a supplied string. |
UCase | Converts a supplied string to upper case text. |
|
IsArray | Tests if a supplied variable is an array. |
IsDate | Tests if a supplied expression is a date. |
IsEmpty | Tests if a supplied variant is Empty. |
IsError | Tests if a supplied expression represents an error. |
IsMissing | Tests if an optional argument to a procedure is missing. |
IsNull | Tests if a supplied expression is Null. |
IsNumeric | Tests if a supplied expression is numeric. |
IsObject | Tests if a supplied variable represents an object variable. |
|
CVErr | Produces an Error data type for a supplied error code. |
Error | Returns the error message corresponding to a supplied error code. |
|
Choose | Selects a value from a list of arguments. |
IIf | Evaluates an expression and returns one of two values, depending on whether the expression evaluates to True or False. |
Switch | Evaluates a list of Boolean expressions and returns a value associated with the first true expression. |
|
Asc | Returns an integer representing the code for a supplied character. |
CBool | Converts an expression to a Boolean data type. |
CByte | Converts an expression to a Byte data type. |
CCur | Converts an expression to a Currency data type. |
CDate | Converts an expression to a Date data type. |
CDbl | Converts an expression to a Double data type. |
CDec | Converts an expression to a Decimal data type. |
Chr | Returns the character corresponding to a supplied character code. |
CInt | Converts an expression to an Integer data type. |
CLng | Converts an expression to a Long data type. |
CSng | Converts an expression to a Single data type. |
CStr | Converts an expression to a String data type. |
CVar | Converts an expression to a Variant data type. |
FormatCurrency | Applies a currency format to an expression and returns the result as a string. |
FormatDateTime | Applies a date/time format to an expression and returns the result as a string. |
FormatNumber | Applies a number format to an expression and returns the result as a string. |
FormatPercent | Applies a percentage format to an expression and returns the result as a string. |
Hex | Converts a numeric value to hexadecimal notation and returns the result as a string. |
Oct | Converts a numeric value to octal notation and returns the result as a string. |
Str | Converts a numeric value to a string. |
Val | Converts a string to a numeric value. |
|
Date | Returns the current date. |
DateAdd | Adds a time interval to a date and/or time. |
DateDiff | Returns the number of intervals between two dates and/or times. |
DatePart | Returns a part (day, month, year, etc.) of a supplied date/time. |
DateSerial | Returns a Date from a supplied year, month and day number. |
DateValue | Returns a Date from a String representation of a date/time. |
Day | Returns the day number (from 1 to 31) of a supplied date. |
Hour | Returns the hour component of a supplied time. |
Minute | Returns the minute component of a supplied time. |
Month | Returns the month number (from 1 to 12) of a supplied date. |
MonthName | Returns the month name for a supplied month number (from 1 to 12). |
Now | Returns the current date and time. |
Second | Returns the second component of a supplied time. |
Time | Returns the current time. |
Timer | Returns the number of seconds that have elapsed since midnight. |
TimeSerial | Returns a Time from a supplied hour, minute and second. |
TimeValue | Returns a Time from a String representation of a date/time. |
Weekday | Returns an integer (from 1 to 7), representing the weekday of a supplied date. |
WeekdayName | Returns the weekday name for a supplied integer (from 1 to 7). |
Year | Returns the year of a supplied date. |
|
Abs | Returns the absolute value of a number. |
Atn | Calculates the arctangent of a supplied number. |
Cos | Calculates the cosine of a supplied angle. |
Exp | Calculates the value of ex for a supplied value of x. |
Fix | Truncates a number to an integer (rounding negative numbers towards zero). |
Int | Returns the integer portion of a number (rounding negative numbers away from zero). |
Log | Calculates the natural logarithm of a supplied number. |
Rnd | Generates a random number between 0 and 1. |
Round | Rounds a number to a specified number of decimal places. |
Sgn | Returns an integer representing the arithmetic sign of a number. |
Sin | Calculates the sine of a supplied angle. |
Tan | Calculates the tangent of a supplied angle. |
Sqr | Returns the square root of a number. |
|
DDB | Calculates the depreciation of an asset during a specified period, using the Double Declining Balance Method. |
FV | Calculates the future value of a loan or investment. |
IPmt | Calculates the interest part of a payment, during a specific period, for a loan or investment. |
IRR | Calculates the internal rate of return for a series of periodic cash flows. |
MIRR | Calculates the modified internal rate of return for a series of periodic cash flows. |
NPer | Calculates the number of periods for a loan or investment. |
NPV | Calculates the net present value of an investment. |
Pmt | Calculates the constant periodic payments for a loan or investment. |
PPmt | Calculates the principal part of a payment, during a specific period, for a loan or investment. |
PV | Calculates the present value of a loan or investment. |
Rate | Calculates the interest rate per period for a loan or investment. |
SLN | Calculates the straight line depreciation of an asset for a single period. |
SYD | Calculates the sum-of-years' digits depreciation for a specified period in the lifetime of an asset. |
|
Array | Creates an array, containing a supplied set of values. |
Filter | Returns a subset of a supplied string array, based on supplied criteria. |
Join | Joins a number of substrings into a single string. |
LBound | Returns the lowest subscript for a dimension of an array. |
Split | Splits a Text String into a Number of Substrings. |
UBound | Returns the highest subscript for a dimension of an array. |
|
CurDir | Returns the current path, as a string. |
Dir | Returns the first file or directory name that matches a specified pattern and attributes. |
FileAttr | Returns the mode of a file that has been opened using the Open statement. |
FileDateTime | Returns the last modified date and time of a supplied file, directory or folder. |
FileLen | Returns the length of a supplied file, directory or folder. |
GetAttr | Returns an integer, representing the attributes of a supplied file, directory or folder. |
|
Vlookup in VBA | Use the Excel spreadsheet Vlookup function from within VBA |