Although there is no built-in VBA Vlookup function, you can still call the Excel Vlookup function from within your VBA code.
In fact, you can call any of the built-in Excel worksheet functions from within your VBA code, through the VBA Application object, which represents the Microsoft Excel application.
One of the object members of the 'Application' object is the 'WorksheetFunction' object, which stores all of the built-in Excel Worksheet functions.
Therefore, any of the built-in Excel functions can be accessed in VBA, using the general command:
The syntax for the VBA Vlookup function call is therefore:
This is illustrated in the following VBA Vlookup example.
| A | B | |
|---|---|---|
| 1 | Employee Name | Hourly Rate |
| 2 | Atkins, James | $35.50 |
| 3 | Bamber, Carol | $39.00 |
| 4 | Benson, Paul | $32.50 |
| 5 | Cooper, David | $29.75 |
| 6 | Daniels, Tom | $44.00 |
| 7 | Taylor, Lisa | $27.90 |
| 8 | Wells, Gail | $35.00 |
The simple VBA code shown below uses the example spreadsheet aboveon the right, which lists seven employee names alongside their individual hourly rates of pay.
The VBA code below calls the Worksheet Vlookup function, and uses this to look up the rate of pay for a specific employee name.
In the example, the specified employee name is "Benson, Paul", and so the VBA Vlookup function returns the value $32.50.
|
' Code to look up the rate of pay for a supplied employee name, from a supplied range of
' cells containing hourly pay rates
Dim Employee_Name As String
Dim Lookup_Range As Range Dim Pay_Rate As Single
Employee_Name = "Benson, Paul"
Set Lookup_Range = Range("A1:B8")
' Call the Vlookup function to look up the rate of pay for the supplied employee name. This
' returns the value from the second column of the range A1:B8 within the current worksheet.
Pay_Rate = Application.WorksheetFunction.VLookup( Employee_Name, Lookup_Range, 2, False )
|