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.
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.
' 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 )