VBA – How to Use Worksheet Functions (and a Full List)

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):

vba worksheetfunction

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

vba worksheet function error

 

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 worksheetfunction error

 

VBA Worksheet Functions List

Below you will find a list of most of the common VBA WorksheetFunctions.