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.

VBA Message Functions
InputBox	Displays a dialog box prompting the user for input.
MsgBox	Displays a modal message box.

VBA Text Functions
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.

VBA Information Functions
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.

VBA Error Handling Functions
CVErr	Produces an Error data type for a supplied error code.
Error	Returns the error message corresponding to a supplied error code.

VBA Program Flow Functions
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.

VBA Conversion Functions
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.

VBA Date & Time Functions
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.

VBA Math & Trig Functions
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 e^x 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.

VBA Financial Functions
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.

VBA Array Functions
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.

VBA File Management Functions
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.

Related Page
Vlookup in VBA	Use the Excel spreadsheet Vlookup function from within VBA

Return to the ExcelFunctions.net Home Page