Introduction | VLookup | Max | Min | Closing Remarks

Introduction

Have you ever wanted to apply an Excel function in your VBA code? Have you ever wanted to write a macro but spent hours trying to find a built-in VBA VLookup Function?

There are many built-in Excel functions that aren’t available in VBA, but don’t give up. Use Application.WorksheetFunction to call VLookup, Max, Min and many more Excel functions directly from your VBA macro. This tutorial will show you how.


VBA VLookup

Let’s say you have the following data in an Excel spreadsheet and you want to use standard Excel functions to analyze the data:

WorksheetFunction VBA Demo

In this VBA example, we want to use VLookup to tell us the total salary of Chicago Cubs pitcher, Jake Arrieta. Here’s how you would do it with a macro:

Sub VLookupFunctionDemo()
Dim d1 As Double
d1 = Application.WorksheetFunction.VLookup("Jake Arrieta", Range("A2:E36"), 5)
MsgBox Format(d1, "Currency"), , "VBA VLookup Function"
End Sub

The macro produces the following results:

VBA VLookup Results
VBA VLookup Results

You’ll notice that when you start typing VLookup(, a Quick Info box will appear, but all it shows is Arg1, Arg2, Arg3, [Arg4].

VBA VLookup Arguments
VBA VLookup Arguments

This isn’t very useful, but just know that the arguments of WorksheetFunction functions are identical to the arguments of the equivalent standard Excel functions. The syntax, however, may be different, since you’ll be programming in VBA instead of Excel.

Excel VLookup Arguments
Excel VLOOKUP Arguments

Rest assured, if you know how to use the formula in Excel, you’ll know how to use the formula using the VBA WorksheetFunction method. If you don’t know how to use the VLookup formula in Excel, I encourage you to check out this awesome tutorial from my friend at Spreadsheeto.


VBA Max

Believe it or not, there’s no exclusive VBA Max function! This shocked me when I was first learning VBA because I came from a FORTRAN background. The nuclear industry is old, so don’t make fun of my FORTRAN background! People argue FORTRAN is a primitive programming language, but even it has a native maximum function.

All is not lost. By using the WorksheetFunction method, you can still call the standard Excel Max function within your VBA macro. Here’s an example:

Sub MaxFunctionDemo()
Dim d1 As Double
d1 = Application.WorksheetFunction.Max(Range("E1:E36"))
MsgBox d1, , "VBA Max Function"
End Sub

VBA Min

Another example, but this time with the Excel Min function.

Sub MinFunctionDemo()
Dim d1 As Double
d1 = Application.WorksheetFunction.Min(Range("E2:A36"))
MsgBox d1, , "VBA Min Function"
End Sub

If you dig back in the archives, you’ll notice I use both the Excel Max and Min functions in my comparing two columns article. They truly are some of the most useful worksheet functions.


Closing Remarks

I’ve only touched on a few of the dozens of functions you can call using Application.WorksheetFunction. For a complete list, open your VBA Editor and begin typing Application.WorksheetFunction.. If you have “Complete Word” enabled (Ctrl-Space), you’ll be able to scroll through dozens of built-in Excel functions that you can use in your VBA macros!

VBA WorksheetFunction Functions
VBA WorksheetFunction Functions

Let me know what your favorite native Excel function is by leaving me a comment. Remember to share this article with your friends and follow me on Google+ for more great VBA content.

Discover how this Nuclear Engineer Mastered Excel VBA

And why you should, too


Your time is valuable. It's time to become a VBA expert.

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.