Using the VLOOKUP function in VBA
We’ll use a simple VLOOKUP formula example to see how worksheet functions can be called in VBA.
VLOOKUP is a very powerful function that’s great for finding information in big spreadsheets.
If you don’t remember, here’s what it does 🧠
VLOOKUP searches for a lookup value in a specified dataset. If it finds it, it returns a corresponding value from the same row.
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.
Open the example workbook to follow along. It contains a list of product numbers and descriptions. We’ll use VBA code to look up descriptions based on product numbers.
Here’s the VBA code we’ll use:
Dim prodNum As Integer, prodDesc As String
prodNum = Range("F2").Value
prodDesc = Application.WorksheetFunction.VLookup(prodNum,
Range("A1:B51"), 2, FALSE)
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.
The VBA VLOOKUP code is actually quite simple.
To use any Excel function in VBA, type “Application.WorksheetFunction.” and start typing the name of the function.
In this case, it’s “VLOOKUP”.
You’ll see it come up in the resulting list (you can also just type the name of the function you’re looking for).
Then you’ll need to include the standard arguments. In the VLOOKUP function, those are:
- and range_lookup
You enter them similarly to how you would in Excel (if you’ve forgotten how to do that, read my guide here).
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.
So, use the below code to create a VBA VLOOKUP.
Application.WorksheetFunction.VLookup(prodNum, Range("A1:B51"), 2, FALSE)
Obviously, in those rare cases where you’ll actually need it, you can use approximate match in the 4th VBA VLOOKUP argument.
Make sure that there’s a product number (the lookup value) in F2:
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.
Why use worksheet functions in VBA?
This is more complicated than just using the function in Excel, so why would you write an Excel VBA macro for a VLOOKUP function?
Any function that’s present in Excel is going to be slightly more complicated to use in VBA code. 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 ⚙️
Or you could write Excel VBA code with the VBA VLOOKUP function that automates the process and saves you time. Even if it is possible to use 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].
But when you’re getting started, use the full line with “WorksheetFunction.”