Skip to main content

Excel Vlookup Function in Visual Basic

Joshua earned an MBA from USF and writes mostly about software and technology.

How to write the function in VBA (visual basic applications) to run a macro in Excel.

How to write the function in VBA (visual basic applications) to run a macro in Excel.

Usually, when a Vlookup is run as a macro there are many complexities involved that results in the automation of a process. In this tutorial, I only cover the basics of how to write the function in VBA (visual basic applications) to run a macro in Excel. There is an assumption here that you have some knowledge of how a Vlookup works.

A segment o the Vlookup function used in the Visual Basic editor.

A segment o the Vlookup function used in the Visual Basic editor.

Employee Vlookup example

Employee Vlookup example

With the above data, I would like to return the employee in cell F4, which relates to the employee number that appears in cell E4. This task proves to be simple when using the Vlookup as a worksheet function. When running the function in VBA, there are a few additional steps to completing the task.

To start, go to the view tab and click the view macros button.

View Macros Button

The view macros button located under the view tab is used to show existing macros, edit macros, create macros, and for deleting macros.

The view macros button located under the view tab is used to show existing macros, edit macros, create macros, and for deleting macros.

Next, create a name for the macro and click the plus button. On a Windows system, the create button will need to be clicked.

Name and Create a Macro

excel-vlookup-function-in-visual-basic

A new VBA module will appear like the one below. Each macro must start with Sub followed by the name of the macro with the ending End Sub.

VBA Module

When using VBA on a Mac computer take caution. While your formatting may appear perfect, you will find that you may need to manipulate your code to work in some instances.

When using VBA on a Mac computer take caution. While your formatting may appear perfect, you will find that you may need to manipulate your code to work in some instances.

In the example code that I created below, the first section identified is the range where I want the result to appear. In this case, I set this value as employee but any variable will do. Notice the syntax used to identify a cell. The cell needs an added identifier to let the back end of Excel know what a cell or range of cells is.

Set employee = Range("F4")

VBA Code Example 1

This is the simplest example created to run a Vlookup in VBA to mimic what the spreadsheet function accomplishes.

This is the simplest example created to run a Vlookup in VBA to mimic what the spreadsheet function accomplishes.

On the next line of code, we want to establish that employee (or cell F4) is a value and will be equal to whatever the function returns. Without the ".Value" after the employee variable, the macro will run with no result.

Employee.Value =

Next, for the function section, "Application.WorksheetFunction." needs to be chosen before using available Excel functions in VBA. Following, the function arguments are the same as they are when using Vlookup in a spreadsheet formula with the exception of formatting cell locations in VBA.

Application.WorksheetFunction.VlookupRange("E4"), Range("B3:C7), 2, True)

See the below arguments of the spreadsheet Vlookup function in the illustration.

Vlookup Formula Arguments

The functional arguments that are used in the VBA version of Vlookup mimic the arguments used in the spreadsheet version.

The functional arguments that are used in the VBA version of Vlookup mimic the arguments used in the spreadsheet version.

Running this function from the view macros option will allow the macro to execute and populate cell F4 of the example with the result.

Results

excel-vlookup-function-in-visual-basic

Below is another simple Vlookup VBA example with a few differences. First, instead of using a specific range of cells, I used column ranges to specify the lookup array. More importantly, I set variables to represent the cells for the lookup value and array and used those variables directly in the function arguments.

VBA Code Example 2

While using variables in this second example may seem a lot more complex to some people, it will be a reality once you start using VBA code more.

While using variables in this second example may seem a lot more complex to some people, it will be a reality once you start using VBA code more.

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2022 Joshua Crowder