VBA – How to Use Worksheet Functions (and a Full List)
In this Article
There are many ways to use functions in VBA. VBA comes loaded with many built-in functions. You can even create your own functions (UDFs). However, you can also utilize many of Excel’s functions in VBA by using Application.WorksheetFunction.
How to Use Worksheet Functions in VBA
To access an Excel function in VBA add Application.WorksheetFunction in front of the function that you wish to call. In the example below, we’ll call Excel’s Max Function:
Dim maxvalue as long
maxvalue = Application.WorksheetFunction.Max(Range("a1").Value, Range("a2").Value)
The syntax of the functions are the same, however you will enter the function arguments just like you would any other VBA function.
Notice that the syntax of the Max Function appears when you type (similar to with VBA Functions):
WorksheetFunction Method
WorksheetFunction is a method of Application object. It allows you access to many (not all) of the standard Excel worksheet functions. Generally, you won’t gain access to any worksheet functions that have a corresponding VBA version.
You can see a list of many of the most common Worksheet Functions below.
Application.WorksheetFunction vs. Application
There are actually two ways to access these functions:
Application.WorksheetFunction (as seen above):
maxvalue = Application.WorksheetFunction.Max(Range("a1").Value, Range("a2").Value)
or you can omit the WorksheetFunction
maxvalue = Application.Max(Range("a1").Value, Range("a2").Value)
Unfortunately, omitting the WorksheetFunction will eliminate the Intellisense that displays the syntax (see image above). However, it has one big potential advantage: Error Handling.
If you use Application, and your function generates an error it will return the error value. If you use the WorksheetFunction method, VBA will throw a run time error. Of course, you can handle the VBA error, but it’s usually better to avoid the error in the first place.
Let’s look at an example to see the difference:
Vlookup WorksheetFunction Error Handling
We will attempt to perform a Vlookup that will not result in a match. So the Vlookup function will return an error.
First, we will use the WorksheetFunction method. Notice how VBA throws an error:
Next we omit the WorksheetFunction. Notice how the
Next we will omit the WorksheetFunction. Notice how no error is thrown and instead the ‘value’ function contains the error value from the Vlookup.
VBA Worksheet Functions List
Below you will find a list of most of the common VBA WorksheetFunctions.
Function | Description |
---|---|
Logical | |
AND | Checks whether all conditions are met. TRUE/FALSE |
IF | If condition is met, do something, if not, do something else. |
IFERROR | If result is an error then do something else. |
OR | Checks whether any conditions are met. TRUE/FALSE |
Lookup & Reference | |
CHOOSE | Chooses a value from a list based on it's position number. |
HLOOKUP | Lookup a value in the first row and return a value. |
INDEX | Returns a value based on it's column and row numbers. |
LOOKUP | Looks up values either horizontally or vertically. |
MATCH | Searches for a value in a list and returns its position. |
TRANSPOSE | Flips the oriention of a range of cells. |
VLOOKUP | Lookup a value in the first column and return a value. |
Date & Time | |
DATE | Returns a date from year, month, and day. |
DATEVALUE | Converts a date stored as text into a valid date |
DAY | Returns the day as a number (1-31). |
DAYS360 | Returns days between 2 dates in a 360 day year. |
EDATE | Returns a date, n months away from a start date. |
EOMONTH | Returns the last day of the month, n months away date. |
HOUR | Returns the hour as a number (0-23). |
MINUTE | Returns the minute as a number (0-59). |
MONTH | Returns the month as a number (1-12). |
NETWORKDAYS | Number of working days between 2 dates. |
NETWORKDAYS.INTL | Working days between 2 dates, custom weekends. |
NOW | Returns the current date and time. |
SECOND | Returns the second as a number (0-59) |
TIME | Returns the time from a hour, minute, and second. |
TIMEVALUE | Converts a time stored as text into a valid time. |
WEEKDAY | Returns the day of the week as a number (1-7). |
WEEKNUM | Returns the week number in a year (1-52). |
WORKDAY | The date n working days from a date. |
YEAR | Returns the year. |
YEARFRAC | Returns the fraction of a year between 2 dates. |
Engineering | |
CONVERT | Convert number from one unit to another. |
Financial | |
FV | Calculates the future value. |
PV | Calculates the present value. |
NPER | Calculates the total number of payment periods. |
PMT | Calculates the payment amount. |
RATE | Calculates the interest Rate. |
NPV | Calculates the net present value. |
IRR | The internal rate of return for a set of periodic CFs. |
XIRR | The internal rate of return for a set of non-periodic CFs. |
PRICE | Calculates the price of a bond. |
INTRATE | The interest rate of a fully invested security. |
Information | |
ISERR | Test if cell value is an error, ignores #N/A. TRUE/FALSE |
ISERROR | Test if cell value is an error. TRUE/FALSE |
ISEVEN | Test if cell value is even. TRUE/FALSE |
ISLOGICAL | Test if cell is logical (TRUE or FALSE). TRUE/FALSE |
ISNA | Test if cell value is #N/A. TRUE/FALSE |
ISNONTEXT | Test if cell is not text (blank cells are not text). TRUE/FALSE |
ISNUMBER | Test if cell is a number. TRUE/FALSE |
ISODD | Test if cell value is odd. TRUE/FALSE |
ISTEXT | Test if cell is text. TRUE/FALSE |
TYPE | Returns the type of value in a cell. |
Math | |
ABS | Calculates the absolute value of a number. |
AGGREGATE | Define and perform calculations for a database or a list. |
CEILING | Rounds a number up, to the nearest specified multiple. |
COS | Returns the cosine of an angle. |
DEGREES | Converts radians to degrees. |
DSUM | Sums database records that meet certain criteria. |
EVEN | Rounds to the nearest even integer. |
EXP | Calculates the exponential value for a given number. |
FACT | Returns the factorial. |
FLOOR | Rounds a number down, to the nearest specified multiple. |
GCD | Returns the greatest common divisor. |
INT | Rounds a number down to the nearest integer. |
LCM | Returns the least common multiple. |
LN | Returns the natural logarithm of a number. |
LOG | Returns the logarithm of a number to a specified base. |
LOG10 | Returns the base-10 logarithm of a number. |
MROUND | Rounds a number to a specified multiple. |
ODD | Rounds to the nearest odd integer. |
PI | The value of PI. |
POWER | Calculates a number raised to a power. |
PRODUCT | Multiplies an array of numbers. |
QUOTIENT | Returns the integer result of division. |
RADIANS | Converts an angle into radians. |
RANDBETWEEN | Calculates a random number between two numbers. |
ROUND | Rounds a number to a specified number of digits. |
ROUNDDOWN | Rounds a number down (towards zero). |
ROUNDUP | Rounds a number up (away from zero). |
SIN | Returns the sine of an angle. |
SUBTOTAL | Returns a summary statistic for a series of data. |
SUM | Adds numbers together. |
SUMIF | Sums numbers that meet a criteria. |
SUMIFS | Sums numbers that meet multiple criteria. |
SUMPRODUCT | Multiplies arrays of numbers and sums the resultant array. |
TAN | Returns the tangent of an angle. |
Stats | |
AVERAGE | Averages numbers. |
AVERAGEIF | Averages numbers that meet a criteria. |
AVERAGEIFS | Averages numbers that meet multiple criteria. |
CORREL | Calculates the correlation of two series. |
COUNT | Counts cells that contain a number. |
COUNTA | Count cells that are non-blank. |
COUNTBLANK | Counts cells that are blank. |
COUNTIF | Counts cells that meet a criteria. |
COUNTIFS | Counts cells that meet multiple criteria. |
FORECAST | Predict future y-values from linear trend line. |
FREQUENCY | Counts values that fall within specified ranges. |
GROWTH | Calculates Y values based on exponential growth. |
INTERCEPT | Calculates the Y intercept for a best-fit line. |
LARGE | Returns the kth largest value. |
LINEST | Returns statistics about a trendline. |
MAX | Returns the largest number. |
MEDIAN | Returns the median number. |
MIN | Returns the smallest number. |
MODE | Returns the most common number. |
PERCENTILE | Returns the kth percentile. |
PERCENTILE.INC | Returns the kth percentile. Where k is inclusive. |
PERCENTILE.EXC | Returns the kth percentile. Where k is exclusive. |
QUARTILE | Returns the specified quartile value. |
QUARTILE.INC | Returns the specified quartile value. Inclusive. |
QUARTILE.EXC | Returns the specified quartile value. Exclusive. |
RANK | Rank of a number within a series. |
RANK.AVG | Rank of a number within a series. Averages. |
RANK.EQ | Rank of a number within a series. Top Rank. |
SLOPE | Calculates the slope from linear regression. |
SMALL | Returns the kth smallest value. |
STDEV | Calculates the standard deviation. |
STDEV.P | Calculates the SD of an entire population. |
STDEV.S | Calculates the SD of a sample. |
STDEVP | Calculates the SD of an entire population |
TREND | Calculates Y values based on a trendline. |
Text | |
CLEAN | Removes all non-printable characters. |
DOLLAR | Converts a number to text in currency format. |
FIND | Locates position of text within a cell.Case-sensitive. |
LEFT | Truncates text a number of characters from the left. |
LEN | Counts number of characters in text. |
MID | Extracts text from the middle of a cell. |
PROPER | Converts text to proper case. |
REPLACE | Replaces text based on it's location. |
REPT | Repeats text a number of times. |
RIGHT | Truncates text a number of characters from the right. |
SEARCH | Locates position of text within a cell.Not Case-sensitive. |
SUBSTITUTE | Finds and replaces text. Case-sensitive. |
TEXT | Converts a value into text with a specific number format. |
TRIM | Removes all extra spaces from text. |