Vlookup VBA - An Excel Tutorial on how to use Vlookup in vba

Udemy Editor

The real power of Excel lies not in the built-in functions and formulas that are available in Excel, but in the fact that Excel also allows users to create small code snippets based on

Visual Basic using the Excel VBA functions. Excel VBA is an acronym for Visual Basic for Applications and the VBA turns Excel from an ordinary spreadsheet into a fully-fledged application development environment. This tutorial will show you how to access VBA in Excel and how to use the Vlookup formula in VBA. To learn to harness the power of Excel, enroll in Excel 2013 The Basics now. This course includes just under four hours of videos to teach you all the Excel skills you will need to become an Excel master. You will learn how to get started in Excel 2013. The course will teach you how to perform calculations, how to modify worksheets and how to save and print your worksheet. You will learn to manage large workbooks and how to customize your Excel working environment. You will also learn advanced Excel functions and formulas and you will learn about the developer tab which is essential for VBA.

This tutorial will show you how to create a VBA routine that uses the Vlookup function to lookup and return an employee’s salary. The tutorial is based on the following fictional data:

Start a VBA Routine in Excel

To access VBA within Excel, you need to add an activeX control to your worksheet. This control will then be linked to a VBA subroutine and you can edit the routine using the VBA editor. To add a control to your worksheet, select the “developer” tab from the Excel ribbon and then select “Insert” to insert the type of control you want to insert. For the purposes of this tutorial we will add a button that the user can push to run our function.

Select “ActiveX” and “Button” from the menu to add a button to your worksheet and then draw the button by selecting an area with your mouse:

We will change the name of the button in the VBA editor.

By adding a button, we have added a sub-routine to our excel worksheet. Now we need to add VBA code to our worksheet to look up an employee’s salary using the Vlookup function.

Type in the name of an employee in the name field on the form:

Now double click the command button to enter the VBA editor.

Add Code to the VBA Button you Created

When you double click the button, Excel will open the VBA editor. You can also open the editor by selecting the Visual Basic button found under the developer tab.

To learn how to add buttons to your forms and how to create advanced functions and formulas, sign up for the Excel 2013 course now and take advantage of over 112 lessons and 12 hours of video content designed to help you get the most out of Excel 2013. This course is designed for beginners and will teach you all of the skills you need to become an advanced Excel user.

Add Variables to the Code

The first thing we need to do for our code is to define the variables that the code will use. Variables are “placeholders” that the program uses to store information. The code we will be creating will be getting the name of the employee from the data the user types into the name field we created. We therefore need to create a variable called “name” to store the user input so that we can use it in our VBA code. Since the user name consists of characters, we will define the name variable as a “string”. To define the name as a variable we therefore add:

We also need a variable to keep the value of the salary we look up using the Vlookup formula so that we can send that number to the worksheet user when they click the button. Since salary will be a number value, the variable will be defined as an integer.

Now that we have defined the variables we need, we need to read the name the user types into the worksheet. To read the name, we need to add the following code:

The user enters the name into cell C13:

To refer to a cell, we need to refer to the row and column index in VBA so C13 is expressed as 13,3.

Add the Vlookup Formula

Now that we have the name stored as a variable, we can use it to lookup the salary of the employee using the Vlookup formula. The formula will look like this:

Note that we need to specify that the formula must use this worksheet for the function. The Vlookup formula uses the same function arguments as the Excel Vlookup formula. So if the formula looks up the value to be found – in this case the name, it looks for that name in the range, in this case A1:D11 and it returns the value in column 3 if an exact match is found.

Now all we need to do is add a message box that will display the employee’s salary when the user clicks the command button:

When the user types in the name George and then clicks the button, the following message box is displayed:

The above example was an extremely simple example of how to use the Vlookup function in Excel VBA. The power of VBA makes Excel almost unlimited.

Sign up for the Excel 2013 for Dummies Video Training, Deluxe Edition and take advantage of over 19 hours of video content to help you learn top tips and tricks in Excel. This course is based on the Excel All-in-One eBook for Dummies. It includes Office 2013 cheat sheets and interactive quizzes to ensure you can use Excel like a master. The course will teach you how to launch Excel and how to navigate the worksheets within the workbook. You will learn how to enter data and how to use AutoFill to enter your data quickly and easily. You will learn all about print formulas and how to print your worksheets. You will also learn to create pivot tables and how to work with pivot charts. This course includes lessons on both the basic and advanced functions in Excel and you will even learn to share your workbooks using SkyDrive.

For more tutorials on the lookup function read: