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.
Let’s say you have the following data in an Excel spreadsheet and you want to use standard Excel functions to analyze the data:
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
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
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
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.
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
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.
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
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 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.Follow