VBA Vlookup - Use the Excel Vlookup Function in VBA


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:

Result = Application.WorksheetFunction.Function Name ( function arguments )

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

Result = Application.WorksheetFunction.Vlookup ( lookup_value, table_array, col_index_num, [range_lookup] )

This is illustrated in the VBA Vlookup example below.


VBA Vlookup Example

Spreadsheet of Employee Hourly Pay Rates Used in 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.


' Function to look up the rate of pay for a supplied employee name and a supplied range of cells
' containing hourly pay rates

Function Pay_Rate( Employee_Name As String, Hourly_Rates As Range ) As Currency

    ' Call the Vlookup function to look up the rate of pay for the supplied employee name

    Pay_Rate = Application.WorksheetFunction.VLookup( Employee_Name, Hourly_Rates, 2, False )

End Function
Return to the Excel Vlookup Tutorial Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net