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.
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.
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.
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 " &amp; curBal
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
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.
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
'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 &amp; " " &amp; strSurname
'loop to the next cell
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!
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.