Once you get started with VBA, the next important thing is to learn how to use in-built VBA functions while writing a VBA code.
So here I have listed the Top 100 VBA Functions (Category Wise) in detail with examples and sample codes, and these functions are listed under specific categories (10) to make you understand the purpose of each function easily.
Below is the list of categories of the major VBA functions, all the functions are further listed under these categories:
VBA TEXT (String) Functions
VBA TEXT functions help you to manipulate text value(s) from a cell, range of cells, or from an entire worksheet or workbook. In simple words, these functions are specifically built for changing text values.
Below we have a list of 18 TEXT functions that you can learn to use:
| Name | Description |
|---|---|
| VBA FORMAT Function | It helps you to apply a specific format and then return that string in the result. |
| VBA INSTR Function | It looks for a sub-string from a string and returns the starting position of the sub-string in the result. |
| VBA INSTRREV Function | It also looks for a substring from a string and returns the starting position of the sub-string in the result (looking from right to left.) |
| VBA LCASE Function | It converts a string into a lowercase string where you have all the characters small. |
| VBA LEFT Function | Return a substring from a string using the count of characters supplied (starting from the left side of the string). |
| VBA LEN Function | It counts the number of characters from the supplied value and returns a number as the count of those characters. |
| VBA LTRIM Function | It removes the leading spaces (from the left side) from a string and returns that string without those spaces in the result. |
| VBA MID Function | Return a substring from a string using the starting position and count of characters supplied. |
| VBA REPLACE Function | It finds a substring from a string and replaces it with another substring and returns the new string in the result. |
| VBA RIGHT Function | Return a substring from a string using the count of characters supplied (starting from the right side of the string). |
| VBA RTRIM Function | It removes the trailing spaces (from the right side) from a string and returns that string without those spaces in the result. |
| VBA SPACE Function | It creates a string with a specified number of spaces by the user and returns it in the result. |
| VBA STRCOMP Function | It compares two strings and returns the result as an integer. |
| VBA STRCONV Function | It converts a string into a specific format and returns a new string in the result. |
| VBA STRING Function | It takes a character and repeats it a number of times and returns it in the result as a string. |
| VBA STRREVERSE Function | It reverses a string and returns it in the result. |
| VBA TRIM Function | It removes the spaces from the starting and ending of a string and returns a new string in the result. |
| VBA UCASE Function | It converts a string into an upper case string where you have all the characters are in the capital. |
VBA DATE Functions
VBA DATE functions are specifically built to deal with dates from data. With these functions, you can extract part of a date, add or subtract days from a date, and the difference between two dates.
Below we have a list of 12 DATE functions that you can learn to use:
| Name | Description |
|---|---|
| VBA DATE Function | It returns the current date value in the result using the system’s setting. |
| VBA DATEADD Function | It adds an interval to the date (and time). |
| VBA DATEPART Function | It returns part (day, month, and years) from a date. |
| VBA DATESERIAL Function | It creates a date by using the day, month, and year supplied. |
| VBA DATEVALUE Function | It converts a date that is stored as a string or a string that represents a date into an actual date. |
| VBA DAY Function | It takes the day value from a valid date and ignores the rest of the components (month and year) and returns it in the result. |
| VBA FileDateTime Function | It returns the last modified date and time (as a string) of a file (workbook). |
| VBA MONTH Function | It takes the month value from a valid date and ignores the rest of the components (day and year) and returns it in the result. |
| VBA MONTHNAME Function | It returns the name of the month using the month number (ranging from 1 to 12). |
| VBA WEEKDAY Function | It returns the weekday (ranging from 1 to 7) of a date supplied. |
| VBA WEEKDAYNAME Function | It returns the name of the day using the day number (ranging from 0 to 7). |
| VBA YEAR Function | It takes the year value from a valid date and ignores the rest of the components (day and month) and returns it in the result. |
VBA TIME Functions
VBA Time functions help you to work with time values and manipulate them if you want. You can extract a part from a time value, add hours, minutes, and hours to a time, and calculate the difference between two-time values.
Below we have a list of 8 TIME functions that you can learn to use:
| Name | Description |
|---|---|
| VBA HOUR Function | It takes the hour value from a valid time and ignores the rest of the components (minutes and seconds) and returns it in the result. |
| VBA MINUTE Function | It takes the minutes value from a valid time and ignores the rest of the components (hours and seconds) and returns it in the result. |
| VBA NOW Function | It returns the current date and time value in the result using the system’s setting. |
| VBA SECOND Function | It takes the seconds value from a valid time and ignores the rest of the components (hours and minutes) and returns it in the result. |
| VBA TIME Function | It returns the current time value in the result using the system’s setting. |
| VBA TIMER Function | It returns the number of seconds that have elapsed since midnight (12:00 AM). |
| VBA TIMESERIAL Function | It returns a valid time value (as per VBA) by using hours, minutes, and seconds specified by the user. |
| VBA TIMEVALUE Function | It takes time value from a value that has date and time and returns it in the result. |
VBA MATHS Functions
VBA MATHS functions are designed to perform specific mathematical calculations. In simple words, these functions perform calculations that are complex to perform otherwise.
Below we have a list of 13 MATHS functions that you can learn to use:
| Name | Description |
|---|---|
| VBA ABS Function | It converts a numeric value into an absolute number and returns it in the result. |
| VBA ATN Function | It calculates the arctangent of the number supplied and returns it in the result. |
| VBA COS Function | It calculates the cosine of the angle supplied and returns it in the result. |
| VBA EXP Function | It calculates the value of ex for the value of x supplied and returns it in the result. |
| VBA FIX Function | It returns a number in the result after truncating a number to an integer. |
| VBA INT Function | It takes a number and returns the integer part of it in the result as a number. |
| VBA LOG Function | It calculates the natural logarithm for the natural logarithm by the user and returns it in the result. |
| VBA RND Function | It returns a random number in the result ranging from 0 to 1. |
| VBA ROUND Function | It rounds a number to a specific number of decimals and returns the new rounded number in the result. |
| VBA SGN Function | It returns an integer representing the arithmetic sign of the number supplied. |
| VBA SIN Function | It calculates the sine of the angle supplied and returns it in the result. |
| VBA SQR Function | It calculates the square root of the number supplied and returns it in the result. |
| VBA TAN Function | It calculates the tangent of the angle value supplied. |
VBA Logical Functions
VBA LOGICAL functions help you to test conditions and return a value if that condition and other if that value is FALSE.
Below we have 1 LOGICAL function which you can learn to use and you can also use VBA IF in your codes:
| Name | Description |
|---|---|
| VBA IIF Function | It tests a condition and returns the specified value if the condition is TRUE and some other value (specified) if that condition is FALSE. |
VBA Information Functions
VBA INFORMATION functions help you to get specific information from the Excel application environment. You can check if a value is a date if there’s an error or if you have an object.
Below we have a list of 8 INFORMATION functions that you can learn to use:
| Name | Description |
|---|---|
| VBA ISARRAY Function | It tests if the supplied expression is an array and returns the result as TRUE or FALSE. |
| VBA ISDATE Function | It tests if the supplied expression is a date and returns the result as TRUE or FALSE. |
| VBA ISEMPTY Function | It tests if the supplied expression is empty and returns the result as TRUE or FALSE. |
| VBA ISERROR Function | It tests if the supplied expression is an error and returns the result as TRUE or FALSE. |
| VBA ISMISSING Function | It tests if an argument for a procedure is missing and returns the result as TRUE or FALSE. |
| VBA ISNULL Function | It tests if the supplied expression is NULL and returns the result as TRUE or FALSE. |
| VBA ISNUMERIC Function | It tests if the supplied expression is a number and returns the result as TRUE or FALSE. |
| VBA ISOBJECT Function | It tests if the supplied expression is an object and returns the result as TRUE or FALSE. |
VBA Financial Functions
VBA Financial functions help you to perform financial calculations with the values you input. You can calculate depreciation, future values, or rate of interest for investments and loans.
Below we have a list of 12 FINANCIAL functions which you can learn to use:
| Name | Description |
|---|---|
| VBA DDB Function | It calculates depreciation on an asset for a specific period using the Double Declining Balance Method. |
| VBA FV Function | It calculates the future value of a loan or investment. |
| VBA IPMT Function | It calculates the interest amount of a loan or investment for a specific period. |
| VBA IRR Function | It calculates the internal rate of return for a series of periodic cash flows. |
| VBA MIRR Function | It calculates the modified internal rate of return for a series of periodic cash flows. |
| VBA NPER Function | It calculates the number of periods for an investment or a loan. |
| VBA NPV Function | It calculates the net present value of an investment. |
| VBA PMT Function | It calculates the principal amount of a loan or investment for a specific period. |
| VBA PV Function | It calculates the present value of a loan or investment. |
| VBA RATE Function | It calculates the interest rate for a loan or investment. |
| VBA SLN Function | It calculates the straight-line depreciation of an asset for a single period. |
| VBA SYD Function | It calculates the sum-of-years’ digits depreciation for a specified period in the lifetime of an asset. |
VBA ARRAY Functions
VBA ARRAYS functions help you deal with arrays. You can filter values from an array, and join and split a string.
Below we have a list of 6 ARRAY functions that you can learn to use:
| Name | Description |
|---|---|
| VBA ARRAY Function | It creates an array with a set of values supplied by you. |
| VBA FILTER Function | It returns a subset from an array of strings using criteria. |
| VBA JOIN Function | It joins multiple strings into one string and returns it as the result. |
| VBA LBOUND Function | It returns the lowest subscript for a dimension of an array. |
| VBA SPLIT Function | It splits a string into multiple strings using. |
| VBA UBOUND Function | It returns the upper subscript for a dimension of an array. |
VBA Data Type Conversion Functions
VBA Data Type Conv. functions help you convert a value from one data type to another data type, like converting a number which is stored as text into a long data type.
Below we have a list of 20 Data Type Conv. functions which you can learn to use:
Name | Description |
|---|---|
| VBA CBOOL Function | It converts the supplied expression into the boolean data value and returns it in the result. |
| VBA CBYTE Function | It converts the supplied expression into the byte data value and returns it in the result. |
| VBA CCUR Function | It converts the supplied expression into the current data value and returns it in the result. |
| VBA CDATE Function | It converts the supplied expression into the date data value and returns it in the result. |
| VBA CDBL Function | It converts the supplied expression into the double data value and returns it in the result. |
| VBA CDEC Function | It converts the supplied expression into the decimal data value and returns it in the result. |
| VBA CHR Function | It returns the character using a character code supplied. |
| VBA CINT Function | It converts the supplied expression into the integer data value and returns it in the result. |
| VBA CLNG Function | It converts the supplied expression into the long data value and returns it in the result. |
| VBA CSNG Function | It converts the supplied expression into a single data value and returns it in the result. |
| VBA CSTR Function | It converts the supplied expression into the string data value and returns it in the result. |
| VBA CVAR Function | It converts the supplied expression into the variant data value and returns it in the result. |
| VBA FORMATCURRENCY Function | It applies the currency format to the supplied expression and returns it in the result. |
| VBA FORMATDATETIME Function | It applies the date format to the supplied expression and returns it in the result. |
| VBA FORMATNUMBER Function | It applies the number format to the supplied expression and returns it in the result. |
| VBA FORMATPERCENT Function | It applies the percentage format to the supplied expression and returns it in the result. |
| VBA HEX Function | It returns a string by converting a numeric value to hexadecimal notation. |
| VBA OCT Function | It returns a string by converting a numeric value into an octal notation |
| VBA STR Function | It converts a numeric value into a string and returns the result. |
| VBA VAL Function | It converts a string into a numeric value and it returns it in the result. |
VBA Error Handling Functions
VBA Error Handling functions help you to get information about the error that occurs in a VBA code. Below we have 1 Error Handling function which you can learn to use and you can also use error handling techniques in your codes:
| Name | Description |
|---|---|
| VBA CVERR Function | It returns an error data type by using the error data number supplied. |