Top VBA Functions

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:

NameDescription
VBA FORMAT FunctionIt helps you to apply a specific format and then return that string in the result.
VBA INSTR FunctionIt looks for a sub-string from a string and returns the starting position of the sub-string in the result.
VBA INSTRREV FunctionIt 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 FunctionIt converts a string into a lowercase string where you have all the characters small.
VBA LEFT FunctionReturn a substring from a string using the count of characters supplied (starting from the left side of the string).
VBA LEN FunctionIt counts the number of characters from the supplied value and returns a number as the count of those characters.
VBA LTRIM FunctionIt removes the leading spaces (from the left side) from a string and returns that string without those spaces in the result.
VBA MID FunctionReturn a substring from a string using the starting position and count of characters supplied.
VBA REPLACE FunctionIt finds a substring from a string and replaces it with another substring and returns the new string in the result.
VBA RIGHT FunctionReturn a substring from a string using the count of characters supplied (starting from the right side of the string).
VBA RTRIM FunctionIt removes the trailing spaces (from the right side) from a string and returns that string without those spaces in the result.
VBA SPACE FunctionIt creates a string with a specified number of spaces by the user and returns it in the result.
VBA STRCOMP FunctionIt compares two strings and returns the result as an integer.
VBA STRCONV FunctionIt converts a string into a specific format and returns a new string in the result.
VBA STRING FunctionIt takes a character and repeats it a number of times and returns it in the result as a string.
VBA STRREVERSE FunctionIt reverses a string and returns it in the result.
VBA TRIM FunctionIt removes the spaces from the starting and ending of a string and returns a new string in the result.
VBA UCASE FunctionIt 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:

NameDescription
VBA DATE FunctionIt returns the current date value in the result using the system’s setting.
VBA DATEADD FunctionIt adds an interval to the date (and time).
VBA DATEPART FunctionIt returns part (day, month, and years) from a date.
VBA DATESERIAL FunctionIt creates a date by using the day, month, and year supplied.
VBA DATEVALUE FunctionIt converts a date that is stored as a string or a string that represents a date into an actual date.
VBA DAY FunctionIt 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 FunctionIt returns the last modified date and time (as a string) of a file (workbook).
VBA MONTH FunctionIt 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 FunctionIt returns the name of the month using the month number (ranging from 1 to 12).
VBA WEEKDAY FunctionIt returns the weekday (ranging from 1 to 7) of a date supplied.
VBA WEEKDAYNAME FunctionIt returns the name of the day using the day number (ranging from 0 to 7).
VBA YEAR FunctionIt 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:

NameDescription
VBA HOUR FunctionIt 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 FunctionIt 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 FunctionIt returns the current date and time value in the result using the system’s setting.
VBA SECOND FunctionIt 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 FunctionIt returns the current time value in the result using the system’s setting.
VBA TIMER FunctionIt returns the number of seconds that have elapsed since midnight (12:00 AM).
VBA TIMESERIAL FunctionIt returns a valid time value (as per VBA) by using hours, minutes, and seconds specified by the user.
VBA TIMEVALUE FunctionIt 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:

NameDescription
VBA ABS FunctionIt converts a numeric value into an absolute number and returns it in the result.
VBA ATN FunctionIt calculates the arctangent of the number supplied and returns it in the result.
VBA COS FunctionIt calculates the cosine of the angle supplied and returns it in the result.
VBA EXP FunctionIt calculates the value of ex for the value of x supplied and returns it in the result.
VBA FIX FunctionIt returns a number in the result after truncating a number to an integer.
VBA INT FunctionIt takes a number and returns the integer part of it in the result as a number.
VBA LOG FunctionIt calculates the natural logarithm for the natural logarithm by the user and returns it in the result.
VBA RND FunctionIt returns a random number in the result ranging from 0 to 1.
VBA ROUND FunctionIt rounds a number to a specific number of decimals and returns the new rounded number in the result.
VBA SGN FunctionIt returns an integer representing the arithmetic sign of the number supplied.
VBA SIN FunctionIt calculates the sine of the angle supplied and returns it in the result.
VBA SQR FunctionIt calculates the square root of the number supplied and returns it in the result.
VBA TAN FunctionIt 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:

NameDescription
VBA IIF FunctionIt 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:

NameDescription
VBA ISARRAY FunctionIt tests if the supplied expression is an array and returns the result as TRUE or FALSE.
VBA ISDATE FunctionIt tests if the supplied expression is a date and returns the result as TRUE or FALSE.
VBA ISEMPTY FunctionIt tests if the supplied expression is empty and returns the result as TRUE or FALSE.
VBA ISERROR FunctionIt tests if the supplied expression is an error and returns the result as TRUE or FALSE.
VBA ISMISSING FunctionIt tests if an argument for a procedure is missing and returns the result as TRUE or FALSE.
VBA ISNULL FunctionIt tests if the supplied expression is NULL and returns the result as TRUE or FALSE.
VBA ISNUMERIC FunctionIt tests if the supplied expression is a number and returns the result as TRUE or FALSE.
VBA ISOBJECT FunctionIt 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:

NameDescription
VBA DDB FunctionIt calculates depreciation on an asset for a specific period using the Double Declining Balance Method.
VBA FV FunctionIt calculates the future value of a loan or investment.
VBA IPMT FunctionIt calculates the interest amount of a loan or investment for a specific period.
VBA IRR FunctionIt calculates the internal rate of return for a series of periodic cash flows.
VBA MIRR FunctionIt calculates the modified internal rate of return for a series of periodic cash flows.
VBA NPER FunctionIt calculates the number of periods for an investment or a loan.
VBA NPV FunctionIt calculates the net present value of an investment.
VBA PMT FunctionIt calculates the principal amount of a loan or investment for a specific period.
VBA PV FunctionIt calculates the present value of a loan or investment.
VBA RATE FunctionIt calculates the interest rate for a loan or investment.
VBA SLN FunctionIt calculates the straight-line depreciation of an asset for a single period.
VBA SYD FunctionIt 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:

NameDescription
VBA ARRAY FunctionIt creates an array with a set of values supplied by you.
VBA FILTER FunctionIt returns a subset from an array of strings using criteria.
VBA JOIN FunctionIt joins multiple strings into one string and returns it as the result.
VBA LBOUND FunctionIt returns the lowest subscript for a dimension of an array.
VBA SPLIT FunctionIt splits a string into multiple strings using.
VBA UBOUND FunctionIt 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 FunctionIt converts the supplied expression into the boolean data value and returns it in the result.  
VBA CBYTE FunctionIt converts the supplied expression into the byte data value and returns it in the result.
VBA CCUR FunctionIt converts the supplied expression into the current data value and returns it in the result.
VBA CDATE FunctionIt converts the supplied expression into the date data value and returns it in the result.
VBA CDBL FunctionIt converts the supplied expression into the double data value and returns it in the result.
VBA CDEC FunctionIt converts the supplied expression into the decimal data value and returns it in the result.
VBA CHR FunctionIt returns the character using a character code supplied.
VBA CINT FunctionIt converts the supplied expression into the integer data value and returns it in the result.
VBA CLNG FunctionIt converts the supplied expression into the long data value and returns it in the result.
VBA CSNG FunctionIt converts the supplied expression into a single data value and returns it in the result.
VBA CSTR FunctionIt converts the supplied expression into the string data value and returns it in the result.
VBA CVAR FunctionIt converts the supplied expression into the variant data value and returns it in the result.
VBA FORMATCURRENCY FunctionIt applies the currency format to the supplied expression and returns it in the result.
VBA FORMATDATETIME FunctionIt applies the date format to the supplied expression and returns it in the result.
VBA FORMATNUMBER FunctionIt applies the number format to the supplied expression and returns it in the result.
VBA FORMATPERCENT FunctionIt applies the percentage format to the supplied expression and returns it in the result.
VBA HEX FunctionIt returns a string by converting a numeric value to hexadecimal notation.
VBA OCT FunctionIt returns a string by converting a numeric value into an octal notation
VBA STR FunctionIt converts a numeric value into a string and returns the result.
VBA VAL FunctionIt 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:

NameDescription
VBA CVERR FunctionIt returns an error data type by using the error data number supplied.