VBA Functions List
Written by
Reviewed by
Last updated on July 19, 2021
Below you will find a list of all of VBA’s built-in functions. The most commonly used functions contain links to pages discussing the functions in detail.
| Function | Description |
|---|---|
| String/Text Functions | |
| ASC | Returns ASCII value of a character |
| CHR | Returns the character based on the ASCII value |
| CONCATENATE with & | Used to join 2 or more strings together using the & operator |
| FORMAT STRINGS | Takes a string expression and returns it as a formatted string |
| INSTR | Returns the position of the first occurrence of a substring in a string |
| INSTRREV | Returns the position of the first occurrence of a string in another string, starting from the end of the string |
| LCASE | Converts a string to lowercase |
| LEFT | Extract a substring from a string, starting from the left-most character |
| LEN | Returns the length of the specified string |
| LTRIM | Removes leading spaces from a string |
| MID | Extracts a substring from a string (starting at any position) |
| REPLACE | Replaces a sequence of characters in a string with another set of characters |
| RIGHT | Extracts a substring from a string starting from the right-most character |
| RTRIM | Removes trailing spaces from a string |
| SPACE | Returns a string with a specified number of spaces |
| SPLIT | Used to split a string into substrings based on a delimiter |
| STR | Returns a string representation of a number |
| STRCOMP | Returns an integer value representing the result of a string comparison |
| STRCONV | Returns a string converted to uppercase, lowercase, proper case or Unicode |
| STRREVERSE | Returns a string whose characters are in reverse order |
| TRIM | Returns a text value with the leading and trailing spaces removed |
| UCASE | Converts a string to all uppercase |
| VAL | Returns the numbers found in a string |
| Format | Applies a format to an expression and returns the result as a string. |
| String | Creates a string consisting of a number of repeated characters. |
| Date/Time Functions | |
| DATE | Returns the current system date |
| DATEADD | Returns a date after which a certain time/date interval has been added |
| DATEDIFF | Returns the difference between two date values, based on the interval specified |
| DATEPART | Returns a specified part of a given date |
| DATESERIAL | Returns a date given a year, month, and day value |
| DATEVALUE | Returns the serial number of a date |
| DAY | Returns the day of the month (a number from 1 to 31) given a date value |
| FORMAT DATES | Takes a date expression and returns it as a formatted string |
| HOUR | Returns the hours (a number from 0 to 23) from a time value |
| MINUTE | Returns the minutes (a number from 0 to 59) from a time value |
| MONTH | Returns the month (a number from 1 to 12) given a date value |
| MONTHNAME | Returns a string representing the month given a number from 1 to 12 |
| NOW | Returns the current system date and time |
| TIMESERIAL | Returns a time given an hour, minute, and second value |
| TIMEVALUE | Returns the serial number of a time |
| WEEKDAY | Returns a number representing the day of the week, given a date value |
| WEEKDAYNAME | Returns a string representing the day of the week given a number from 1 to 7 |
| YEAR | Returns a four-digit year (a number from 1900 to 9999) given a date value |
| 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. |
| Math/Trig Functions | |
| ABS | Returns the absolute value of a number |
| ATN | Returns the arctangent of a number |
| COS | Returns the cosine of an angle |
| EXP | Returns e raised to the nth power |
| FIX | Returns the integer portion of a number |
| FORMAT NUMBERS | Takes a numeric expression and returns it as a formatted string |
| INT | Returns the integer portion of a number |
| LOG | Returns the natural logarithm of a number |
| RANDOMIZE | Used to change the seed value used by the random number generator for the RND function |
| RND | Used to generate a random number (integer value) |
| ROUND | Returns a number rounded to a specified number of digits |
| SGN | Returns the sign of a number |
| SIN | Returns the sine of an angle |
| SQR | Returns the square root of a number |
| TAN | Returns the tangent of an angle |
| MOD | Returns the remainder after division operator (Integer). |
| XOR | The bitwise exclusion operator. |
| Logical Functions | |
| AND | Returns TRUE if all conditions are TRUE |
| CASE | Has the functionality of an IF-THEN-ELSE statement |
| FOR...NEXT | Used to create a FOR LOOP |
| IF-THEN-ELSE | Returns a value if a specified condition evaluates to TRUE or another value if it evaluates to FALSE |
| OR | Returns TRUE if any of the conditions are TRUE |
| SWITCH | Evaluates a list of expressions and returns the corresponding value for the first expression in the list that is TRUE |
| WHILE...WEND | Used to create a WHILE LOOP |
| IS | Compares two object reference variables. |
| LIKE | The pattern matching operator. |
| Information Functions | |
| ENVIRON | Returns the value of an operating system environment variable |
| ISDATE | Returns TRUE if the expression is a valid date |
| ISEMPTY | Used to check for blank cells or uninitialized variables |
| ISERROR | Used to check for error values |
| ISNULL | Used to check for a NULL value |
| ISNUMERIC | Used to check for a numeric value |
| IsArray | Tests if a supplied variable is an array. |
| IsMissing | Tests if an optional argument to a procedure is missing. |
| IsObject | Tests if a supplied variable represents an object variable. |
| Financial Functions | |
| DDB | Returns the depreciation of an asset based on the double-declining balance method |
| FV | Returns the future value of an investment |
| IPMT | Returns the interest payment for an investment |
| IRR | Returns the internal rate of return for a series of cash flows |
| MIRR | Returns the modified internal rate of return for a series of cash flows |
| NPER | Returns the number of periods for an investment |
| NPV | Returns the net present value of an investment |
| PMT | Returns the payment amount for a loan |
| PPMT | Returns the payment on the principal for a particular payment |
| PV | Returns the present value of an investment |
| RATE | Returns the interest rate for an annuity |
| SLN | Returns the depreciation of an asset based on the straight-line depreciation method |
| SYD | Returns the depreciation of an asset based on the sum-of-years' digits depreciation method |
| File/Directory Functions | |
| CHDIR | Used to change the current directory or folder |
| CHDRIVE | Used to change the current drive |
| CURDIR | Returns the current path |
| DIR | Returns the first filename that matches the pathname and attributes specified |
| FILEDATETIME | Returns the date and time of when a file was created or last modified |
| FILELEN | Returns the size of a file in bytes |
| GETATTR | Returns an integer that represents the attributes of a file, folder, or directory |
| MKDIR | Used to create a new folder or directory |
| SETATTR | Used to set the attributes of a file |
| FileAttr | Returns the mode of a file that has been opened using the Open statement. |
| FILECOPY | Copies a file from one directory to another. |
| FREEFILE | Returns the next valid free file number (Integer). |
| GET | Reads data from a text file into a record. |
| INPUT | Returns the open stream of an Input or Binary file (String). |
| EOF | Returns the value indicating if the end of a file has been reached (Boolean). |
| WRITE | Writes data to a sequential file. |
| Writes display-formatted data to a sequential file. | |
| PUT | Writes data from a record into a text file. |
| KILL | Deletes an existing file. |
| SEEK - Function | Returns the current read/write position within a file opened using the Open statement (Long). |
| SEEK - Statement | Repositions where the next operation in a file will occur. |
| OPEN | Opens a text file or CSV file. |
| LINE INPUT | Reads a single line from an Open sequential file and assigns it to a string. |
| CLOSE | Closes a text file. |
| LOADPICTURE | Loads a picture from a file into a Picture or Image control (IPictureDisp). |
| LOC | Returns the current read/write position within an open file (Long). |
| LOCK | Locks access to parts of a file for other processes. |
| LOF | Returns the length or size of an open file, in bytes (Long). |
| NAME | Renames an existing file or directory. |
| SAVEPICTURE | Saves a graphic image from an objects Picture or Image property to a file. |
| Data Type Conv. Functions | |
| CBOOL | Converts a value to a boolean |
| CBYTE | Converts a value to a byte (ie: number between 0 and 255) |
| CCUR | Converts a value to currency |
| CDATE | Converts a value to a date |
| CDBL | Converts a value to a double |
| CDEC | Converts a value to a decimal number |
| CINT | Converts a value to an integer |
| CLNG | Converts a value to a long integer |
| CSNG | Converts a value to a single-precision number |
| CSTR | Converts a value to a string |
| CVAR | Converts a value to a variant |
| CLNGLNG | Returns the expression converted to a longlong (64 bit platform) data type. |
| CLNGPTR | Returns the expression converted to a longptr data type. |
| CVDATE | Returns the expression converted to a date variant-subtype (Variant). |
| 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. |
| MACID | Converts a four character constant to a value that can be used by Dir, Kill, Shell and AppActivate. |
| 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. |
| UBound | Returns the highest subscript for a dimension of an array. |
| REDIM | Initialises and resizes a dynamic array. |
| VBA Message Functions | |
| InputBox | Displays a dialog box prompting the user for input. |
| MsgBox | Displays a modal message box. |
| 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. |
| Lookup/Ref Functions | |
| CHOOSE | Returns a value from a list of values based on a given position |
| VBA Program Flow Functions | |
| IIf | Evaluates an expression and returns one of two values, depending on whether the expression evaluates to True or False. |
| Others | |
| Vlookup in VBA | Use the Excel spreadsheet Vlookup function from within VBA |
| APPACTIVATE | Activates an application or window currently running on Windows. |
| BEEP | Produces a single beep noise. |
| CALL | Transfers control to a subroutine or function. |
| CALLBYNAME | Returns, sets or executes a method or property of an object (Variant). |
| COMMAND | Returns the argument portion of the command line used to launch the application (Variant). |
| CREATEOBJECT | Returns a reference after creating a new ActiveX or OLE object (Variant). |
| DELETESETTING | Removes (or deletes) a key or section from the registry. |
| DOEVENTS | Pauses execution to let the system process other events. |
| EQV | The bitwise comparison operator. |
| ERASE | Reinitialises the elements of an array. |
| GETALLSETTINGS | Returns the list of key settings and their values from the registry (Variant). |
| GETOBJECT | Returns the reference to an object provided by an ActiveX component. |
| GETSETTING | Reads from the registry and returns the value or key from the registry (String). |
| GOTO | Transfers control to the subroutine indicated by the line label. |
| IMESTATUS | Returns the current Input Method Editor mode of Microsoft Windows (Integer). |
| IMP | The logical implication from two values (Variant). |
| IMPLEMENTS | Specifies an interface or class that can be implemented in a class module. |
| LET | Computes a value and assigns it to a new variable. |
| LOAD | Loads an object but doesn't display it. |
| LSET | Left aligns a string within a string variable. |
| NOT | The logical 'NOT' operator (Boolean). |
| OBJPTR | Returns a LongPtr on a 64 bit version and a Long on a 32 bit version. |
| PARTITION | Returns a string indicating which particular range it falls into (String). |
| QBCOLOR | Returns the RGB colour corresponding to the specified colour number (Long). |
| RAISEEVENT | Fires an event declared at module level within a class, form or document. |
| REM | Specifies a single line of comments. |
| RESET | Closes all files open with the Open statement. |
| RGB | Returns the number representing an RGB colour value (Long). |
| RMDIR | Removes an existing directory. |
| RSET | Right aligns a string within a string variable. |
| SAVESETTING | Writes to the registry and saves a section or key in the registry. |
| SENDKEYS | Sends keystrokes to an application. |
| SET | Assigns an object reference to an object variable. |
| SHELL | Returns the program's task id from running an executable programs (Double). |
| SPC | Inserts a specified number (n) of spaces when writing or displaying text. |
| STOP | Suspends execution. |
| STRPTR | Returns a LongPtr on a 64 bit version and a Long on a 32 bit version. |
| TAB | Used with the Print # statement or the Print method to position output. |
| TYPENAME | Returns the data type of the variable as a string (String). |
| TYPEOF | Returns the object data type. |
| UNLOAD | Removes an object from memory. |
| UNLOCK | Controls access to a file. |
| VARPTR | Returns a LongPtr on a 64 bit version and a Long on a 32 bit version. |
| VARTYPE | Returns the number indicating the data type of a variable (Integer). |
| WIDTH | Assigns an output line width (characters) for the open file. |
We hope you found this list useful!
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Learn More!
