The VLookup function is an Excel function.  It can be used directly on your worksheet without having to use any VBA code.  However, there may be instances in VBA where you wish to also use VLookup functionality.

You would then need to call the VLookup function from within your VBA code. Excel functions can be used within VBA code, but have to be used in a specific way.

To use an Excel function in VBA you have to use the Application.WorksheetFunction method.

This method gives you access to all the built-in functions that are available in Excel – VLOOKUP being one of them.

VLookup Syntax

The syntax is as follows:

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

Using VLookup to look up a value

Consider the following sheet:

Using the example above, we want to look up the Average Monthly Balance for a certain account number.

Sub TestLookup()
    Dim strN As String
    strN = Application.WorksheetFunction.VLookup(568, Range("A5:H30"), 8, False)

The procedure above would look up account number 568 and return the Average Monthly Balance for that account.

Using VLookup with an InputBox

This type of lookup could be incorporated into a macro that interacts with the user.   Instead of hard-coding the account number to lookup, the macro could ask the user to input the account number, and then return the available balance.

Sub LookupBalance()
    Dim curBal As Currency
    Dim intA As Integer
    intA = InputBox("Please enter the account number")
    curBal = Application.WorksheetFunction.VLookup(intA, Range("A5:H30"), 8, False)
    MsgBox "The current balance is " & curBal
End Sub 

When you run the procedure, the following input box will appear

Type in 646 and click OK.

Populating a list in a separate sheet using VLookup

There may be an occasion when you need Excel to loop through your code and look up values and then pass those values to a separate sheet.

For example, using our list of bank accounts, we may have another Excel sheet with the account numbers listed in them, and we wish to look up the names of the customers against each account number.

Sub FindNames()
On Error GoTo eh
'declare the variables
    Dim intAccount As Integer
    Dim strFirstName As String
    Dim strSurname As String
    Dim i As Integer
'select the first account in sheet 2
    Sheet2.Range("A2").Select
'loop for 26 rows
    For i = 2 To 27
        intAccount = ActiveCell
        'lookup the first name
        strFirstName = Application.WorksheetFunction.VLookup(intAccount, Sheet1.Range("A5:H30"), 3, False)
        'lookup the surname
        strSurname = Application.WorksheetFunction.VLookup(intAccount, Sheet1.Range("A5:H30"), 4, False)
        'populate the active cell
        ActiveCell.Offset(0, 1).Value = strFirstName & " " & strSurname
        'loop to the next cell
        ActiveCell.Offset(1, 0).Select
    Next i
    Exit Sub
eh:
    MsgBox Err.Description
End Sub

When you run the code, Sheet 2 will be populated as follows:

Things to look out for

You have to pass the correct data type to the VLookup if you are passing a variable.

The code above does not like me passing a string variable to the lookup when the data in Excel is numeric. It throws a Run-time error 1004.

If the lookup cannot find the value, the same error will occur!

A simple error trap will solve the problem:

In this article we have learnt how to use VLookup in VBA.  This is a Worksheet function as we have discussed. You are able to use all the built-in Excel functions in VBA by adding the Application.WorksheetFunction method to your VBA code.