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.


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 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.
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