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 |
|---|---|
It helps you to apply a specific format and then return that string in the result. | |
It looks for a sub-string from a string and returns the starting position of the sub-string in the result. | |
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.) | |
It converts a string into a lowercase string where you have all the characters small. | |
Return a substring from a string using the count of characters supplied (starting from the left side of the string). | |
It counts the number of characters from the supplied value and returns a number as the count of those characters. | |
It removes the leading spaces (from the left side) from a string and returns that string without those spaces in the result. | |
Return a substring from a string using the starting position and count of characters supplied. | |
It finds a substring from a string and replaces it with another substring and returns the new string in the result. | |
Return a substring from a string using the count of characters supplied (starting from the right side of the string). | |
It removes the trailing spaces (from the right side) from a string and returns that string without those spaces in the result. | |
It creates a string with a specified number of spaces by the user and returns it in the result. | |
It compares two strings and returns the result as an integer. | |
It converts a string into a specific format and returns a new string in the result. | |
It takes a character and repeats it a number of times and returns it in the result as a string. | |
It reverses a string and returns it in the result. | |
It removes the spaces from the starting and ending of a string and returns a new string in the result. | |
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 |
|---|---|
It returns the current date value in the result using the system’s setting. | |
It adds an interval to the date (and time). | |
It returns part (day, month, and years) from a date. | |
It creates a date by using the day, month, and year supplied. | |
It converts a date that is stored as a string or a string that represents a date into an actual date. | |
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. | |
It returns the last modified date and time (as a string) of a file (workbook). | |
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. | |
It returns the name of the month using the month number (ranging from 1 to 12). | |
It returns the weekday (ranging from 1 to 7) of a date supplied. | |
It returns the name of the day using the day number (ranging from 0 to 7). | |
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 |
|---|---|
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. | |
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. | |
It returns the current date and time value in the result using the system’s setting. | |
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. | |
It returns the current time value in the result using the system’s setting. | |
It returns the number of seconds that have elapsed since midnight (12:00 AM). | |
It returns a valid time value (as per VBA) by using hours, minutes, and seconds specified by the user. | |
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 |
|---|---|
It converts a numeric value into an absolute number and returns it in the result. | |
It calculates the arctangent of the number supplied and returns it in the result. | |
It calculates the cosine of the angle supplied and returns it in the result. | |
It calculates the value of ex for the value of x supplied and returns it in the result. | |
It returns a number in the result after truncating a number to an integer. | |
It takes a number and returns the integer part of it in the result as a number. | |
It calculates the natural logarithm for the natural logarithm by the user and returns it in the result. | |
It returns a random number in the result ranging from 0 to 1. | |
It rounds a number to a specific number of decimals and returns the new rounded number in the result. | |
It returns an integer representing the arithmetic sign of the number supplied. | |
It calculates the sine of the angle supplied and returns it in the result. | |
It calculates the square root of the number supplied and returns it in the result. | |
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 |
|---|---|
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 |
|---|---|
It tests if the supplied expression is an array and returns the result as TRUE or FALSE. | |
It tests if the supplied expression is a date and returns the result as TRUE or FALSE. | |
It tests if the supplied expression is empty and returns the result as TRUE or FALSE. | |
It tests if the supplied expression is an error and returns the result as TRUE or FALSE. | |
It tests if an argument for a procedure is missing and returns the result as TRUE or FALSE. | |
It tests if the supplied expression is NULL and returns the result as TRUE or FALSE. | |
It tests if the supplied expression is a number and returns the result as TRUE or FALSE. | |
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 |
|---|---|
It calculates depreciation on an asset for a specific period using the Double Declining Balance Method. | |
It calculates the future value of a loan or investment. | |
It calculates the interest amount of a loan or investment for a specific period. | |
It calculates the internal rate of return for a series of periodic cash flows. | |
It calculates the modified internal rate of return for a series of periodic cash flows. | |
It calculates the number of periods for an investment or a loan. | |
It calculates the net present value of an investment. | |
It calculates the principal amount of a loan or investment for a specific period. | |
It calculates the present value of a loan or investment. | |
It calculates the interest rate for a loan or investment. | |
It calculates the straight-line depreciation of an asset for a single period. | |
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 |
|---|---|
It creates an array with a set of values supplied by you. | |
It returns a subset from an array of strings using criteria. | |
It joins multiple strings into one string and returns it as the result. | |
It returns the lowest subscript for a dimension of an array. | |
It splits a string into multiple strings using. | |
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 |
|---|---|
It converts the supplied expression into the boolean data value and returns it in the result. | |
It converts the supplied expression into the byte data value and returns it in the result. | |
It converts the supplied expression into the current data value and returns it in the result. | |
It converts the supplied expression into the date data value and returns it in the result. | |
It converts the supplied expression into the double data value and returns it in the result. | |
It converts the supplied expression into the decimal data value and returns it in the result. | |
It returns the character using a character code supplied. | |
It converts the supplied expression into the integer data value and returns it in the result. | |
It converts the supplied expression into the long data value and returns it in the result. | |
It converts the supplied expression into a single data value and returns it in the result. | |
It converts the supplied expression into the string data value and returns it in the result. | |
It converts the supplied expression into the variant data value and returns it in the result. | |
It applies the currency format to the supplied expression and returns it in the result. | |
It applies the date format to the supplied expression and returns it in the result. | |
It applies the number format to the supplied expression and returns it in the result. | |
It applies the percentage format to the supplied expression and returns it in the result. | |
It returns a string by converting a numeric value to hexadecimal notation. | |
It returns a string by converting a numeric value into an octal notation | |
It converts a numeric value into a string and returns the result. | |
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 |
|---|---|
It returns an error data type by using the error data number supplied. |