VBA Functions

This section shows the list of available VBA built-in functions organized by category. VBA functions are used directly in the code in order to perform different actions, i.e. calculations, conversions, etc. Many of the VBA functions have an equivalent Excel built-in or worksheet function. We have covered the VBA WorksheetFunction object and its applicability in this other page.

 

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

 

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 contained in an array 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.

 

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.

 

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.

 

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.

 

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.

 

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.

 

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.

 

Other Functions

Choose	Selects a value from a list of arguments.
CVErr	Produces an Error data type for a supplied error code.
Error	Returns the error message corresponding to a supplied error code.
IIf	Evaluates an expression and returns one of two values, depending on whether the expression evaluates to True or False.
InputBox	Displays a dialog box prompting the user for input.
MsgBox	Displays a modal message box.
Switch	Evaluates a list of Boolean expressions and returns a value associated with the first true expression.