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.
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:
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.
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).
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:
VBA displays a message box with the matching product description for our product number.