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
InputBoxDisplays a dialog box prompting the user for input.
MsgBoxDisplays a modal message box.
VBA Text Functions
FormatApplies a format to an expression and returns the result as a string.
InStrReturns the position of a substring within a string.
InStrRevReturns the position of a substring within a string, searching from right to left.
LeftReturns a substring from the start of a supplied string.
LenReturns the length of a supplied string.
LCaseConverts a supplied string to lower case text.
LTrimRemoves leading spaces from a supplied string.
MidReturns a substring from the middle of a supplied string.
ReplaceReplaces a substring within a supplied text string.
RightReturns a substring from the end of a supplied string.
RTrimRemoves trailing spaces from a supplied string.
SpaceCreates a string consisting of a specified number of spaces.
StrCompCompares two strings and returns an integer representing the result of the comparison.
StrConvConverts a string into a specified format.
StringCreates a string consisting of a number of repeated characters.
StrReverseReverses a supplied string.
TrimRemoves leading and trailing spaces from a supplied string.
UCaseConverts a supplied string to upper case text.
VBA Information Functions
IsArrayTests if a supplied variable is an array.
IsDateTests if a supplied expression is a date.
IsEmptyTests if a supplied variant is Empty.
IsErrorTests if a supplied expression represents an error.
IsMissingTests if an optional argument to a procedure is missing.
IsNullTests if a supplied expression is Null.
IsNumericTests if a supplied expression is numeric.
IsObjectTests if a supplied variable represents an object variable.
VBA Error Handling Functions
CVErrProduces an Error data type for a supplied error code.
ErrorReturns the error message corresponding to a supplied error code.
VBA Program Flow Functions
ChooseSelects a value from a list of arguments.
IIfEvaluates an expression and returns one of two values, depending on whether the expression evaluates to True or False.
SwitchEvaluates a list of Boolean expressions and returns a value associated with the first true expression.
VBA Conversion Functions
AscReturns an integer representing the code for a supplied character.
CBoolConverts an expression to a Boolean data type.
CByteConverts an expression to a Byte data type.
CCurConverts an expression to a Currency data type.
CDateConverts an expression to a Date data type.
CDblConverts an expression to a Double data type.
CDecConverts an expression to a Decimal data type.
ChrReturns the character corresponding to a supplied character code.
CIntConverts an expression to an Integer data type.
CLngConverts an expression to a Long data type.
CSngConverts an expression to a Single data type.
CStrConverts an expression to a String data type.
CVarConverts an expression to a Variant data type.
FormatCurrencyApplies a currency format to an expression and returns the result as a string.
FormatDateTimeApplies a date/time format to an expression and returns the result as a string.
FormatNumberApplies a number format to an expression and returns the result as a string.
FormatPercentApplies a percentage format to an expression and returns the result as a string.
HexConverts a numeric value to hexadecimal notation and returns the result as a string.
OctConverts a numeric value to octal notation and returns the result as a string.
StrConverts a numeric value to a string.
ValConverts a string to a numeric value.
VBA Date & Time Functions
DateReturns the current date.
DateAddAdds a time interval to a date and/or time.
DateDiffReturns the number of intervals between two dates and/or times.
DatePartReturns a part (day, month, year, etc.) of a supplied date/time.
DateSerialReturns a Date from a supplied year, month and day number.
DateValueReturns a Date from a String representation of a date/time.
DayReturns the day number (from 1 to 31) of a supplied date.
HourReturns the hour component of a supplied time.
MinuteReturns the minute component of a supplied time.
MonthReturns the month number (from 1 to 12) of a supplied date.
MonthNameReturns the month name for a supplied month number (from 1 to 12).
NowReturns the current date and time.
SecondReturns the second component of a supplied time.
TimeReturns the current time.
TimerReturns the number of seconds that have elapsed since midnight.
TimeSerialReturns a Time from a supplied hour, minute and second.
TimeValueReturns a Time from a String representation of a date/time.
WeekdayReturns an integer (from 1 to 7), representing the weekday of a supplied date.
WeekdayNameReturns the weekday name for a supplied integer (from 1 to 7).
YearReturns the year of a supplied date.
VBA Math & Trig Functions
AbsReturns the absolute value of a number.
AtnCalculates the arctangent of a supplied number.
CosCalculates the cosine of a supplied angle.
ExpCalculates the value of ex for a supplied value of x.
FixTruncates a number to an integer (rounding negative numbers towards zero).
IntReturns the integer portion of a number (rounding negative numbers away from zero).
LogCalculates the natural logarithm of a supplied number.
RndGenerates a random number between 0 and 1.
RoundRounds a number to a specified number of decimal places.
SgnReturns an integer representing the arithmetic sign of a number.
SinCalculates the sine of a supplied angle.
TanCalculates the tangent of a supplied angle.
SqrReturns the square root of a number.
VBA Financial Functions
DDBCalculates the depreciation of an asset during a specified period, using the Double Declining Balance Method.
FVCalculates the future value of a loan or investment.
IPmtCalculates the interest part of a payment, during a specific period, for a loan or investment.
IRRCalculates the internal rate of return for a series of periodic cash flows.
MIRRCalculates the modified internal rate of return for a series of periodic cash flows.
NPerCalculates the number of periods for a loan or investment.
NPVCalculates the net present value of an investment.
PmtCalculates the constant periodic payments for a loan or investment.
PPmtCalculates the principal part of a payment, during a specific period, for a loan or investment.
PVCalculates the present value of a loan or investment.
RateCalculates the interest rate per period for a loan or investment.
SLNCalculates the straight line depreciation of an asset for a single period.
SYDCalculates the sum-of-years' digits depreciation for a specified period in the lifetime of an asset.
VBA Array Functions
ArrayCreates an array, containing a supplied set of values.
FilterReturns a subset of a supplied string array, based on supplied criteria.
JoinJoins a number of substrings into a single string.
LBoundReturns the lowest subscript for a dimension of an array.
SplitSplits a Text String into a Number of Substrings.
UBoundReturns the highest subscript for a dimension of an array.
VBA File Management Functions
CurDirReturns the current path, as a string.
DirReturns the first file or directory name that matches a specified pattern and attributes.
FileAttrReturns the mode of a file that has been opened using the Open statement.
FileDateTimeReturns the last modified date and time of a supplied file, directory or folder.
FileLenReturns the length of a supplied file, directory or folder.
GetAttrReturns an integer, representing the attributes of a supplied file, directory or folder.
Related Page
Vlookup in VBAUse the Excel spreadsheet Vlookup function from within VBA