VBA Vlookup - Use the Excel Vlookup Function in VBA

Home » Excel-Vlookup-Tutorial » VBA-Vlookup

Search this site: Custom Search

Although there is no VBA Vlookup function, you can still call the Excel Vlookup function from VBA.

In fact, you can call any of the built-in Excel functions from within a VBA document, but as the Vlookup function is one of the most popular built-in Excel functions, this article focuses on the Vlookup.

When calling any of the built-in Excel functions from within a VBA document, you use the VBA Application property, which represents the Microsoft Excel application. One of the object members of the Application, is the WorksheetFunction object, which acts as a container for all of the built-in Excel Worksheet functions.

Therefore, any of the built-in Excel functions can be accessed in VBA, using the following command:

The syntax for calling the VBA Vlookup function call is therefore:

This is illustrated in the VBA Vlookup example below.

VBA Vlookup Example

The following simple VBA function receives an employee name and a range of cells that contain rates of pay, as shown in the example spreadsheet on the right.

The VBA code calls the Worksheet Vlookup function, and uses this the look up the rate of pay for the supplied employee name.

For example, if the supplied employee name was "Benson, Paul" and the supplied Hourly_Rates range was columns A-B of the spreadsheet on the right, the VBA Vlookup would return the value $32.00.