Call VBA’s built-in functions by name in your code, with their arguments in brackets: x = Len(Range("A1").Value) returns the number of characters in A1. They work on the values you pass them, not on cells directly, so read the cell’s value first.
The ones used most, by group:
<ul>
<li>Text: Left, Right, Mid, Len, InStr, Replace, Trim, UCase, Split.</li>
<li>Dates: Date, Now, DateAdd, DateDiff, DateSerial, Format.</li>
<li>Numbers: Round, Int, Abs.</li>
<li>Checks: IsEmpty, IsNumeric, IsDate, IsError.</li>
<li>Arrays: UBound, LBound, Join, Filter.</li>
</ul>
To convert between types, use the C functions: CStr for text, CLng for whole numbers, CDbl for decimals, CDate for dates and CBool for True/False. Val("12abc") returns 12, because it reads digits until the first character that can’t be part of a number.
Worksheet functions that VBA doesn’t have, such as VLookup, SumIfs and CountIf, are called through WorksheetFunction, as in WorksheetFunction.SumIfs(...). To write your own function, create a Function procedure in a standard module.
VBA’s Round rounds halves to the nearest even number, so Round(2.5, 0) returns 2 and Round(3.5, 0) returns 4. For the rounding Excel uses in cells, call WorksheetFunction.Round(2.5, 0), which returns 3.
Once you get started with VBA, learning how to use the built-in functions while writing VBA code is next important.
In VBA, built-in functions are predefined procedures or methods that can be used to perform specific tasks. These functions are part of the VBA library and can simplify programming by providing standard functionality without requiring you to write the code from scratch.
Here I have listed the Top 100 VBA Functions (Category Wise) in detail with examples and sample codes.
These functions are listed under specific categories (10) to help 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 manipulate text value(s) from a cell, a range of cells, or an entire worksheet or workbook.
In simple words, these functions are specifically built to change text values. Below, we have a list of some of the most important functions that you can learn to use:
VBA DATE & Time Functions
VBA DATE & Time functions are specifically built to deal with dates and the time 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 and 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 date functions that you can learn to use:
VBA MATHS Functions
VBA MATHS functions are designed to perform specific mathematical calculations. In simple words, these functions perform complex calculations to perform otherwise.
Below, we have a list of maths functions that you can learn to use:
VBA Information Functions
VBA information functions can return information about a variable or expression’s data type or value. Here are some commonly used information functions in VBA:
VBA ARRAY Functions
You can use VBA Array functions to work with arrays, which are collections of elements of the same type. These functions help you to manipulate arrays efficiently. Here are some commonly used VBA array functions:
VBA Data Type Conversion Functions
In VBA, Data type conversion functions can convert data from one type to another. These functions are helpful when you need to ensure that your variables are of the correct type for your operations. Here are some commonly used data type conversion functions in VBA:
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.