Home »
VBAFunctions
BuiltIn VBA Functions
VBA provides a large number of builtin functions that can be used in your code.
The most popular builtin 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 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.

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 sumofyears' 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
