VBA VLOOKUP:
How to Use Worksheet Functions in VBA

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Visual Basic for Applications (VBA) has many built-in functions that help you work with spreadsheet data.

But Excel is the undisputed king when it comes to useful functions.

Fortunately, you can use worksheet functions to take advantage of that power when you’re writing macros in VBA.

First, you learn the differences between VBA functions and worksheet functions.

Then I show you how to use a worksheet function in VBA – the VLOOKUP!

Let’s go 🙂

Get your FREE exercise file

In the second section of this post, we’ll be going through an example.

Download the free example workbook so you can follow along!

cloud-download

BONUS: Download the VBA VLOOKUP Exercise File to go along with this post.

VBA functions vs. worksheet functions

VBA functions are built into Visual Basic for Applications, the scripting language that you use to create macros.

You can use VBA functions in any program that supports VBA (including Microsoft Word and Access).

Worksheet functions are specific to Excel. They’re the functions that you’re used to using in spreadsheets already—things like CONCATENATE, COMBIN, and VLOOKUP.

You could get the same information from VBA without using worksheet functions—but in many cases, you’d have to write a lot of code that’s already been worked out in Excel.

Kasper Langmann, Co-founder of Spreadsheeto

Using worksheet functions saves you time over writing your own functions in VBA.

To call a worksheet function, you’ll need to use the following syntax:

Application.WorksheetFunction.[function name]

Let’s walk through an example of a very useful function that isn’t present in VBA: VLOOKUP.

Using VLOOKUP in VBA

We’ll use a simple VLOOKUP example to see how worksheet functions can be called in VBA.

VLOOKUP is a very powerful Excel function that’s great for finding information in big spreadsheets—but unfortunately, it’s not built into VBA. So we’ll have to call it with a worksheet function.

If you’re not familiar with the function, check out our full guide to VLOOKUP, which will walk you through it in detail.

Kasper Langmann, Co-founder of Spreadsheeto

Open the example workbook to follow along. It contains a list of product numbers and descriptions. We’ll use VBA to look up descriptions based on product numbers.

Here’s the VBA script we’ll use:

Sub findProduct()
 Dim prodNum As Integer, prodDesc As String
 prodNum = Range("F2").Value
 prodDesc = Application.WorksheetFunction.VLookup(prodNum, Range("A1:B51"), 2, FALSE)
 MsgBox prodDesc
End Sub

The first two lines of the script are simple; they declare prodNum as an integer variable and prodDesc as a String variable, then assign the value in cell F2 to prodNum.

F2 is the cell where we’ll ask users to input a product number.

Kasper Langmann, Co-founder of Spreadsheeto

The line that calls VLOOKUP is actually quite simple. To use an Excel function, type “Application.WorksheetFunction.” and start typing the name of the function.

You’ll see it come up in the resulting list (you can also just type the name of the function you’re looking for).

vba-worksheet-function-autocomplete

Then you’ll need to include the standard arguments for the function. In VLOOKUP, those are lookup_number, table_array, col_index_num, and range_lookup.

You enter them similarly to how you would in Excel, but there are a few differences. In our case, lookup_number is the variable prodNum, which is similar to selecting a cell in Excel.

The table_array, however, needs to be presented in a format that VBA can handle. Here we’ve used Range(“A1:B51”), which selects the cells in A1:B51. It’s important to remember that you can’t just type “A1:B51”, as VBA won’t recognize the range.

col_index_num and range_lookup are the same as in Excel. We’re looking in the second column and want an exact match for the product number, so we’ll use 2 and FALSE.

Make sure that there’s a product number in F2:

lookup-value-vba-vlookup

Then run the macro:

vba-vlookup-results

VBA displays a message box with the matching product description for our product number.

This would be a great place to link a button to run a macro! Users would just have to enter the product number and click a button.

Kasper Langmann, Co-founder of Spreadsheeto

Why use worksheet functions in VBA?

This is more complicated than just using the function in Excel, so why would you write a VBA macro for a VLOOKUP?

Any function that’s present in Excel is going to be slightly more complicated to use in VBA. But having access to them gives you the option of very powerful automation.

For example, if you wanted to run multiple VLOOKUPs and have the results put in a table, you might find that you need to manually create each VLOOKUP in Excel.

With VBA, you can write a script that automates the process and saves you time. Even if it is possible using functions in Excel, it will likely be much more efficient in VBA. You can also save the script and run it on other spreadsheets.

Remember that you can use almost any Excel function from within VBA. Just type “Application.WorksheetFunction.” and you’ll see options in the VBA window.

You can also call these functions with Application.[function name]. There are advantages to both, but they get into some complex programming concepts.

When you’re getting started, use the full line with “WorksheetFunction.”

Kasper Langmann, Co-founder of Spreadsheeto

Add power to Excel functions

Once you’ve learned to use Excel’s worksheet functions from within VBA, you’re ready to start creating some very powerful scripts.

Try to use a few worksheet functions in VBA scripts over the coming week. You might be surprised at how many great uses you come up with.

Just remember that while the functions are the same, they may need slightly different inputs so VBA can work with them.

2019-04-13T07:08:29+00:00