VBA Worksheet Functions: 5 Ways To Easily Use Excel Functions In VBA

If you've used Excel in the past, you're likely familiar with worksheet functions. After all, most Excel users work with Excel worksheet functions (for ex., when creating worksheet formulas).

In this particular Excel tutorial, I focus on how you can use your knowledge of Excel worksheet functions when working with VBA.

Yes! You can use worksheet functions when writing VBA code.

In fact, the use of worksheet functions allows your VBA applications to achieve amazing results while sparing you some coding.

Additionally, as also explained at ozgrid.com, Excel worksheet functions are generally faster than a piece of VBA code written to achieve the same results. Therefore, having a good knowledge of how to use worksheet functions in VBA can help you create faster VBA applications than you'd be able to craft without them.

Therefore, my purpose with this guide is to cover all the aspects you need to understand to start using Excel worksheet functions in VBA now. The following table of contents shows the specific topics that I cover in this blog post:

Let's dive right into the topic of Excel worksheet functions in VBA by understanding…

What Is A Function And What Is An Excel Worksheet Function

As mentioned above, you can write VBA statements that use functions. You can use 3 types of functions:

1. Excel worksheet functions. This is the main topic of this Excel tutorial.
2. VBA built-in functions. I write about this topic briefly below, but it's not the main focus of this tutorial.
3. Function procedures. I explain how you can create and work with VBA Function procedures in this tutorial.

By the end of this blog post, you'll have a very clear idea of what are worksheet functions, why you should use them when creating VBA applications, and how you can work with them within Visual Basic for Applications.

However, regardless of which of type of function you use, they all share the basic same fundamental purpose:

1. The user (you) feeds data to the function.
2. The function:
   • Carries out calculations.
   • Returns values.
3. The user (you) can use the function's output in the VBA code.

In other words, functions (generally):

• Carry out a calculation; and
• Return a value.

Excel worksheet functions, which are the focus of this blog post, are provided by Excel. In other words, worksheet functions are the ones you generally use when writing formulas. You can explore and find most of these Excel worksheet functions by using the different commands and menus that appear within the Function Library in the Formulas tab of the Ribbon.

The source of worksheet functions (Excel) distinguishes this type of functions from both of the other types. More precisely:

• VBA built-in functions are provided from within the Visual Basic for Applications environment.
• Function procedures are custom or user-defined functions that you can create using VBA.

The ability to use worksheet functions in VBA gives you immediate access to hundreds of different functions that you can call from within your VBA code. You can use most, but not all, Excel worksheet functions in VBA. As a general rule, the worksheet functions not available in VBA are those with an equivalent:

• VBA built-in function; or
• VBA operator.

You can find a complete list of Excel VBA worksheet functions in Microsoft's Official Documentation.

How To Use Excel Worksheet Functions In VBA

In order to call Excel worksheet functions using VBA, you use the WorksheetFunction object. The WorksheetFunction object is contained within the Application object.

The worksheet functions themselves are methods of the WorksheetFunction object. You can find a complete list of all the members (including methods) of the WorksheetFunction object in Microsoft's Official Documentation.

Let's take a look at a real example in order to understand how this works in practice. Imagine that you're in the process of closing the yearly account of your company. Your accountant sends you the following table listing the monthly revenue:

You want to know what the lowest monthly revenue for that particular year was. For these purposes, you can generally use the MIN worksheet function. When applied to a particular set of values (such as the monthly revenues displayed in the second column above) MIN returns the smallest number.

However, let's assume that you don't want to use a regular worksheet formula. You'd rather have a message box display what the lowest monthly revenue was. You can do this by using the following macro (called Find_Minimum) which calls the Min worksheet function in VBA for purposes of finding the smallest monthly revenue number.

This Excel VBA Worksheet Functions Tutorial is accompanied by an Excel workbook containing the data and macros I use (including the table and macro displayed above). You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter.

When I execute the Find_Minimum Sub procedure, Excel displays the following message box:

This message box, indeed, displays the smallest monthly revenue from within the table above. You can go back to the table listing the monthly revenue and find that the month of April had lowest monthly revenue of $21,749.00. This is the value displayed in the message box.

For purposes of this Excel tutorial, we'll focus on the VBA statement that calls the Min worksheet function. This statement is the following:

Application.WorksheetFunction.Min(my_Range)

The following screenshot shows its location within the VBA code:

Let's take a closer look at the structure of this statement:

• Item #1: Application.WorksheetFunction returns the WorksheetFunction object. As explained above, this is the object you must use in order to use Excel worksheet functions in VBA.
  • More precisely, this statement uses the Application.WorksheetFunction property to return the WorksheetFunction object. This section of the statement uses the basic structure to create fully qualified object references in Visual Basic for Applications.
• Item #2: The WorksheetFunction.Min method returns the smallest value in a set of values. In other words, this is the equivalent of the MIN worksheet function.
  • This part of the statement follows the basic structure you must use to work with object methods in VBA.

You can actually simplify the first item above in either of the 2 following ways. Note that these simplifications are exclusive: you can only use one or the other. You can't use both at the same time.

Simplification #1: Delete The Reference To The Application Object

In other words, replace “Application.WorksheetFunction.Min(my_Range)” with the following:

WorksheetFunction.Min(my_Range)

This simplification relies on the fact that the Application object is always assumed, as I explain here.

Simplification #2: Delete The Reference To The Application.WorksheetFunction Property

You can:

• Omit the explicit reference to the WorksheetFunction property/object; and
• Call an Excel worksheet function directly through the Application object.

If you choose this simplification method, the statement “Application.WorksheetFunction.Min(my_Range)” becomes:

Application.Min(my_Range)

Should You Simplify Your References To Excel Worksheet Functions In VBA?

As a general matter, the first simplification above (omit the Application object) is clearer. As I explain in this Excel tutorial, referencing VBA properties explicitly is a good programming practice.

Explicitly referring to the WorksheetFunction property/object:

• Tends to result in VBA code that's easier to:
  • Read;
  • Follow;
  • Understand; and
  • Work with.
• Helps make clear that you're working with a worksheet function.

An additional advantage of using WorksheetFunction is the possibility of relying on the Auto List Members option of the Visual Basic Editor, as I explain below. If you omit the reference to WorksheetFunction, the VBE doesn't auto list members.

However, omitting the explicit reference to the WorksheetFunction property/object (and referring explicitly to the Application object) has important advantages in terms of error handling.

More precisely: When omitting the WorksheetFunction property/object and declaring the result variable as of the Variant data type, an error is held by the variable. Therefore:

• There is no run-time error; and
• The code doesn't stop to go into debugging mode (as it usually does in such situations).

A second (but less meaningful) advantage of calling Excel worksheet functions directly through the Application object has to do with code length and readability. More precisely, since WorksheetFunction is a relatively long word, omitting it makes the lines of code shorter.

How To Enter An Excel Worksheet Function In VBA

If you've read other posts in Power Spreadsheets, you probably have a good idea of how to enter worksheet functions in VBA. Even if you don't, the general rule for inserting Excel worksheet functions is quite simple:

Type the worksheet function following the syntax rules explained in the section above.

You:

• Can't use Excel's Insert Function dialog to enter worksheet functions into VBA.
• Can (however) take advantage of the following strategies to get some help when setting up the code to call a worksheet function in VBA:

Strategy #1: Use The Insert Function Dialog Box To Identify Excel Worksheet Functions And The Function Arguments Dialog Box To Obtain Argument Descriptions

Excel has hundreds of worksheet functions and, in some cases, you may not be absolutely sure about which one to use in your VBA code. In such cases, you can use the Insert Function dialog box for purposes of finding out which function can help you.

In fact, you can also use this process for purposes of understanding the arguments of a particular worksheet function.

In order to get some help from the Insert Function dialog box to identify Excel worksheet functions and learn about their arguments, follow these 4 easy steps:

Step #1: Open The Insert Function Dialog Box

You can get Excel to display the Insert Function dialog box in any of the following 2 ways:

• Click on “Insert Function” within the Formulas tab of the Ribbon.
  
• Use the keyboard shortcut “Shift + F3”.

Step #2: Search For Excel Worksheet Function Or Select Appropriate Function Category

Once Excel displays the Insert Function dialog box, you can find the worksheet function you need in either of the following ways:

• Enter a search term in the Search for a function box and click the Go button on the upper-right side of the dialog box.
  • For example, if your purpose of is to create a macro that returns the smallest number within a set of values (just like the sample Find_Minimum macro), you may type the term “smallest” and click “Go”.
    
• If you know the category under which the function is classified, select the relevant category by using the Or select a category drop-down list.
  

Step #3: Select Excel Worksheet Function

Excel displays the actual worksheet functions within the Select a function box on the lower part of the Insert Function dialog box. Once you select a function, Excel describes what the function does on the lower section of the Insert Function dialog box.

For example, if I search for the term “smallest” (as suggested above), the Insert Function dialog looks as follows. Notice that the MIN function is third in the Select a function box. Also, check out the description that appears below the Select a function box.

These first 3 steps are usually enough to identify an Excel worksheet function you want to use in VBA. The fourth step below is useful if you want to learn more about the arguments of that particular function.

Step #4: Open The Function Arguments Dialog Box

You can open the Function Arguments dialog box for a particular function by either (i) double-clicking on it while in the Insert Function dialog box, or (ii) selecting it and clicking on the OK button on the lower-right corner of the Insert Function dialog box.

Once you've completed this step, Excel displays the Function Arguments dialog box. You can find a description of a particular argument by placing the cursor in the relevant cell. The actual description is displayed on the lower-right side of the dialog box. For example, the following screenshot shows the description of the Number1 argument of the MIN function:

The reason why this may be useful is because the argument descriptions provided by the Function Arguments dialog box are much more helpful than what is displayed by the Visual Basic Editor. As an example, compare the image above with the screenshot of the VBE shown below.

Notice how:

• Arguments within the Visual Basic Editor don't have very meaningful names. For example, in the case above you go from Number1, Number2, … (in the Function Arguments dialog box) to Arg1, Arg2, … (in the Visual Basic Editor).
• The VBE doesn't show any description for the arguments of the Min function.

Therefore, the Excel worksheet function itself provides valuable information about the syntax you should use when calling that particular function in VBA. The syntax in both cases (the worksheet function itself and the call within VBA) is substantially similar. In particular, both the number of arguments and their meanings is generally the same in both cases.

The following strategy also takes advantage of the similarities in the syntax of functions in Excel and VBA:

Strategy #2: Copy The Syntax Of A Worksheet Formula

An alternative to using the Function Arguments dialog box to get a better idea about the syntax and arguments of a particular worksheet function is to proceed as follows:

Step #1: Start Typing A Worksheet Formula Using The Excel Worksheet Function You Want To Explore

Worksheet formulas within Excel are preceded by an equal sign (=). If you're using this method, make sure that you:

• Type the full name of the relevant Excel worksheet function.
• Place the insertion point/cursor to the right of the function name.

For example, in the case of the MIN worksheet function you'd type “=MIN”. This is shown in the image below. Notice the location of the cursor.

Step #2: Press “Ctrl + Shift + A”

Once you're standing to the right of the worksheet function's name, use the keyboard shortcut “Ctrl + Shift + A” to insert the function arguments.

After using this shortcut, the MIN function looks as follows:

Step #3: Copy The Worksheet Formula

Select the whole worksheet formula and copy it by, for example, using the “Ctrl + C” keyboard shortcut. You can find a comprehensive list of keyboard shortcuts here.

When copying the formula, you can exclude (not copy) the equal (=) sign. What's more relevant is the rest of the formula which shows the syntax of the worksheet function you're using in VBA.

Step #4: Paste The Worksheet Formula As A Comment In The Visual Basic Editor

Once you've copied the worksheet formula you'll be using as guidance, go back to the VBE.

Once you're back in the Visual Basic Editor environment, paste the formula you've copied as a comment. Comments must be preceded by an apostrophe (‘).

You can consider pasting the formula at the top of the relevant procedure. This is shown in the image below. However, this particular location isn't mandatory. You can place the comment on another row, depending on your commenting practices.

Notice how, now, you have some clearer guidance about the arguments of that particular function. You can now simply follow that syntax when typing in the worksheet function in the VBA code.

An Alternative To Strategy #2

An alternative to the strategy #2 described above involves typing and copying the full formula from the Excel interface. In this case, you just need to follow these 2 simple steps:

Step #1: Type The Full Worksheet Formula You Want To Work With

In the case of the sample Find_Minimum macro, the worksheet formula that achieves the same result as the Min function is “=MIN(C5:C16)”.

The reason for this is that, in the macro, Min's only argument is the variable my_Range which was previously defined as the range of cells from C5 to C16 of the worksheet named “Excel VBA Worksheet Function”.

Step #2: Repeat Steps #3 And #4 Of Strategy #2

Repeat the steps #3 and #4 that I explain above. In other words:

• Step #1: Copy the formula.
• Step #2: Paste the formula as a comment within the Visual Basic Editor.

The image below shows how this looks like in the case of the Find_Minimum sample macro. Notice how this syntax is substantially the same as that used when calling the Excel worksheet function in VBA.

Therefore, once more, you have very clear guidance in connection with the appropriate arguments and syntax to call the worksheet function.

Strategy #3: Use Auto List Members To Obtain A List Of Excel Worksheet Functions And Auto Quick Info To Get The Syntax Of A Function

When the Auto List Members option of the Visual Basic Editor is enabled, the VBE help you complete your VBA statements by displaying a list of items you can use. When the Auto Quick Info option is enabled, the Visual Basic Editor displays information about the arguments of (among others) Excel worksheet functions as you type them.

I provide a thorough explanation of how you can enable (in case they're not enabled) Auto List Members and Auto Quick Info in The Beginner's Guide To Excel's Visual Basic Editor.

To see how the Auto List Members option works in practice, let's go back to the VBA code of the sample Find_Minimum macro. Assume you're typing the statement where the Min worksheet function is used (“minimum_Answer = Application.WorksheetFunction.Min (my_Range)”) and you're standing just before the dot that separates the WorksheetFunction object from the Min method:

Once you type that dot, the Visual Basic Editor displays a list of items that may complete the statement. This includes Excel worksheet functions.

For example, in the case of the Find_Minimum macro, notice how the list displayed by the VBE after I type the dot (.) includes the Min function. In fact, you'll notice that it includes several other familiar Excel worksheet functions such as:

• Match;
• Max; and
• Median.

As I explain above, Auto List Members only works as shown above when you include the reference to the WorksheetFunction property. In other words, in the case above:

• The Visual Basic Editor displays the list of members when you use the syntax (i) “Application.WorksheetFunction.” (as shown in the previous image) or (ii) “WorksheetFunction.” but…
• The VBE doesn't show the list of members that includes Excel worksheet functions if you simply type “Application.”. In such a case, the Visual Basic Editor also lists members automatically. But these are the members of the Application object and, therefore, worksheet functions don't appear.

Auto Quick Info works in a similar way to Auto List Members and you've seen a screenshot of it in action above. One of the main differences between Auto Quick Info and Auto List Members is the way in which they're activated. Auto Quick Info displays the expected syntax for a particular Excel worksheet function once you've typed the first parenthesis after the function name.

For example, in the case of the Min worksheet function within the Find_Minimum sample macro, Auto Quick Info looks as follows.

Strategy #4: Use The Object Browser To Browse Through The Methods Of The WorksheetFunction Object

You can use the Object Browser to see available:

• Objects.
• Methods.
• Properties.

When working with Excel worksheet functions within VBA, you can use the Object Browser to obtain all the members of the WorksheetFunction object. Follow these 3 simple steps:

Step #1: Open The Object Browser

From within the Visual Basic Editor, open the Object Browser by doing any of the following:

• Option #1: Pressing the “F2” keyboard shortcut.
• Option #2: Clicking on the Object Browser button in the Standard toolbar.
  
• Option #3: Using the Object Browser command within the View menu.
  

Step #2: Select The Excel Library

At the top-left corner of the Object Browser, you find the Project/Library drop-down list. This list displays the libraries that are available.

Click on it and select the Excel library.

Step #3: Select The WorksheetFunction Object

The Classes box displayed on the left side of the Object Browser lists all the Excel objects. When working with Excel worksheet functions, the object that you're interested in is WorksheetFunction.

Therefore, search and select “WorksheetFunction” in the Classes box.

The box on the right side of the Object Browser displays all the members of WorksheetFunction. These are, for the most part, all the Excel worksheet functions you can call with VBA by using the WorksheetFunction object.

For example, the Min function is one of the members that the Object Browser lists. Notice how, once I select “Min”, the Object Browser displays the syntax of the function (and its arguments) just below the list of members.

Strategy #5: Use Microsoft's Online Help System To Get Information About A Particular Excel Worksheet Function

Microsoft's Official Documentation includes a very thorough description of all the Excel worksheet functions that you can use in VBA.

If you're working in the Visual Basic Editor and want to go directly to the relevant webpage for a particular worksheet function, follow the 3 easy steps below:

Step #1: Enter The Name Of An Excel Worksheet Function

For example, in the case of the sample Find_Minimum macro, the relevant Excel worksheet function is Min. Immediately after this function and its arguments are entered, the VBA code looks as follows:

Step #2: Place The Cursor On The Name Of The Function

In the case of the Find_Minimum macro and the Min function that I use as an example, this looks as follows:

Step #3: Press The F1 Key

F1 is the keyboard shortcut to access the help system. Once you press it, you're generally taken to the relevant webpage within Microsoft's Official Documentation.

For example, in the case above, I'm correctly taken to the page that covers the WorksheetFunction.Min method.

Among other useful information you can use while working with Excel worksheet functions within VBA, Microsoft's Official Documentation usually explains:

• The syntax of the relevant worksheet function.
• The parameters of the function.
• The return value.
• Additional remarks that may be of interest or come in handy.

Notice how these 4 elements are all present in the page that corresponds to the Min worksheet function:

What Happens When An Excel Worksheet Function Can't Be Used In VBA

Most Excel worksheet functions are available for use with Visual Basic for Applications.

However, there are a few cases where you can't use an Excel worksheet function in VBA because it isn't a method of the WorksheetFunction object. In those cases you'll generally find an equivalent within Visual Basic for Applications.

In other words: Whenever there's a particular VBA element (such as a built-in function or operator) that achieves a particular purpose, you can't use the equivalent Excel worksheet function.

The following are some examples of such a situation:

• The MOD worksheet function. In such a case, you can rely on the (equivalent) Mod operator.
• The MONTH worksheet function. When using VBA, you can use the Month function.
• The SQRT function, whose functions are carried out within Visual Basic for Applications by the Sqr function.
• The TAN worksheet function. In place of TAN, you can use the Tan function.
• The LOWER and UPPER worksheet functions. When using VBA, you can obtain the equivalent results by using the LCase and UCase VBA functions.

As a consequence of this, consider proceeding as follows whenever you need to use a function within VBA:

• Step #1: Determine whether what you need exists within the Visual Basic for Applications framework.
• Step #2: If you're not able to find something suitable within VBA, check out whether an Excel worksheet function could be of help. For these purposes, you can use the Insert Function dialog box to identify potentially useful functions, as I explain above.
• Step #3: If neither of the above steps works, you may be able to create a Function procedure to carry out the calculations you need.

You can use some of the strategies that I describe above for purposes of determining whether Visual Basic for Applications has a particular built-in function that you can use. Let's check them out in the sections below.

Note, however, that sometimes the equivalent of an Excel worksheet function isn't necessarily a VBA built-in function. One such a case is the MOD worksheet function which, as explained above, is replaced within VBA by the Mod operator. In such cases, you'll need to adjust the strategies described below accordingly.

Strategy #1: Use Auto List Members To Obtain a List Of Built-In VBA Functions

This process is similar to that explained above for purposes of finding available Excel worksheet functions.

Note, however, that built-in VBA functions are not accessed through the WorksheetFunction object. This makes sense since, after all, they're not worksheet functions.

The general syntax for referring to a VBA built-in function is as follows:

VBA.Function_Name

Where “Function_Name” is the relevant name of the built-in function. Usually, you'd omit the reference to VBA and simply type the Function_Name. But not when you want to use Auto List Members as described here:

In order to take advantage of Auto List Members when working with VBA built-in functions, you type VBA and a dot (.). After you've typed the dot, Excel displays the relevant list.

The image below shows how this looks like:

Strategy #2: Use The Object Browser To Browse Members Of The VBA Library

Once again, this strategy is substantially similar to the one I explain above for browsing through the methods of the WorksheetFunction Object.

Note, however, that built-in VBA functions are within the VBA (not the Excel) library. Therefore, instead of selecting the Excel library in the Project/Library list box, select “VBA”.

Once the VBA library is selected, Excel displays all of its classes and members. This includes the VBA built-in functions, which appear on the right side of the screen.

Strategy #3: Use Microsoft's Official Documentation To Find VBA Built-In Functions

As you learned in a previous Section (strategy #5 to enter an Excel worksheet function in VBA), you can use the “F1” keyboard shortcut to access Microsoft's Official Documentation.

Microsoft's Official Documentation (also) contains a complete list of VBA built-in functions.

Conclusion

Excel worksheet functions are a great way to increase the power and flexibility of your macros.

After reading this Excel tutorial, you have all the basic tools you need to start using Excel worksheet functions in your macros. In particular, you now know:

• What an Excel worksheet function is.
• How you can call worksheet functions in VBA, including several strategies that can help you get some guidance when working with these functions.
• Which are the cases in which you can't use Excel worksheet functions in VBA, how you can identify those cases and what to do in order to achieve your purposes.

This Excel VBA Worksheet Functions Tutorial is accompanied by an Excel workbook containing the data and macros I use above. You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter.

If you had a decent knowledge of Excel worksheet functions before reading this blog post, you have a great head start into this topic. As you've seen above, there are important similarities between using worksheet functions in VBA and using worksheet functions in Excel itself.

But even if you're not that familiar with worksheet functions, don't worry! Remember that you can always use the strategies listed above to find a function that is suitable for your purposes and understand its syntax.

So, if you haven't yet, start using Excel worksheet functions in your VBA code to improve the power and flexibility of your macros.