ExcelFunctions.net

Search Site:

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 |