Learn VBA for Excel by making your own program

Just when you thought you knew all there was to know about Excel, you enter in to the vast world of VBA. If you aren’t already familiar with programming in the Visual Basic language, this tool can look pretty overwhelming. But not to worry! You have plenty of documentation and support to help you understand this highly useful feature.

Get started today with an online course in the fundamentals of Excel VBA, and read through the following steps to create a basic program that will show you the ins and outs.  This program will have you create a user form for looking up and returning information from a table. You will want to make the exact same table to make the process fail proof. Then you can apply it to your own projects.

Step 1: Make this table in a spreadsheet

This is a simple sales chart shows the revenue earned (in 100 thousands), number of accounts successfully sold, and unique identifier for individual sales executives. Try not to get hung up on whether you find these fields odd. What’s important here is that they are simple, so they can help you easily build your first VBA program.

Go ahead and save your workbook as “Test_form” once you have created the tab. Make sure you save it as a macro-enabled workbook (.xlsm)

Step 2: Make a new macro

Select the developer tab to view the developer ribbon. If you don’t see the developer tab on your menu, you need to enable it as follows:

1. Click the file tab

2. Select “Options”

3. In the Excel Options menu select Customize Ribbon from the list on the left.

4. From the checklist on the right, check the box next to “Developer” under “Main Tabs”

From the developer tab, select Macros; then, in the text box type “Lookup” and click create. This will bring up VBA with a module for your new macro. You will see a form that should be called “Test_form.xlsm – Module1.” The two boxes at the top should say “(General)” and “Lookup” respectively. Under that is the space for entry of your code.  At the top you see written “Sub Lookup()” and under that End Sub.

To the left, you should see two windows, one titled “Project – VBAProject” and another under that that says “Properties Module1.”

You are going to create a user form called “SalesLookup,” so you can  ahead and type in the instructions to load the form. Under “Sub Lookup( )” type the instructions as shown below. These will load the form and tell VBA to show it to the user.

Step 3: Make the form

You have instructed VBA to load and show the form, but you have not made that form yet. To do so, pull down the insert menu and click userform. A new box will pop up with a blank user form, and a toolbox for user controls will show up next to it.

You will see that the properties list is now showing properties for the new user form. This is where you will start on creating the form. Rename the form “SalesLookup” in the name property (which should currently say “UserForm1”). Also change the Caption property to “Sales Lookup”

After you have done that, click on the user form to bring back the tool box. You will use tools in this box to create the controls. To make a control, you will choose the one that you want from the menu, then click on the user form and drag to make the control the size you want.

Your first control will be a label directing the user to enter a name. This is the second control on the list, displayed as a capital A. Put the control near the top of the form on the left side, size it to less than half the width of the form. This will make a box with the name “Label 1.”

Change the properties for this box so that the name is “name_cue” and the caption reads “Enter Name:.” Next, make a text box directly under the label. The text box control is the third option on the controls toolbar. You want this box to be about the same size as the label above it. In the properties, name it “name_box” The “Text” property should be empty.

Next to the text box, make a command button. Name it and caption it with the word “Submit.”

Under the text box, make another label. This one will be labeled “revenue_lbl” and the caption should say “Revenue.” Finally, next to that make another label. This one should be named “revenue_out_lbl,” and the caption should be blank.  The finished product will look like this:

Step 4: program the form

So now to the meat of the program. You are making all of this program run when a user clicks the submit button, so get started by double clicking that button.

This will bring up a new editor for you to put in the code for your program. At the top, it should say “Private Sub Submit_Click().” To make this simple, here is all of the code you will enter, under that is a commented version explaining each element.

You do not need to include any of the comments in green text. Understand also that you will need to get to know these different elements. It is possible to learn as you go, but you can learn a lot more quickly and easily in a course that covers an introduction to Visual Basic.

Additionally, this walkthrough assumes that you are familiar with the vlookup function.  If this is not the case, you may want to get a thorough understanding of that function in an advanced course on Excel.

Step 5. Save it and assign it a command.

Save your workbook, with the macro in it. You can do this from the VBA screen. Just click the save icon on the menu at the top.

Go back to your Excel workbook, and bring up the developer tab. Click on “Macros.” You should now see your Lookup macro listed.  Highlight that macro and click options. You will see the option to assign this a shortcut key.  For the purpose of this walkthrough, assign it to ctrl+shift+L. Click OK.

Now when you go back to the spreadsheet, use your shortcut (ctrl+shift+L). This should bring up your form. Enter one of the names from your table and click the submit button.  You should see the revenue value returned.

Homework

Now see if you can expand the program you made to also return values for each sales executive’s number of accounts and unique ID. If you can do this, you are well on your way to understanding VBA. In addition to continuing your education in VBA and Visual Basic, you might want to consider taking some instruction in programming methods, so you can determine how to create programs that will solve real world problems in your Excel workbooks. You can learn a lot about how to do this in a course that covers fundamentals of programming.

Hopefully, you enjoyed this brief exercise and you were able to build a basic program to learn the basics in the world of VBA!