The Ultimate VBA Tutorial: How to Use VBA to Add Even More Functionality to Excel

Microsoft Excel is still the most dominant application on the market when it comes to spreadsheets. Excel is part of Microsoft Office, which is in use in most offices. Excel allows people who are not programmers to manipulate and analyze data. It allows you to organize your data in rows and columns. It gives you the ability to dynamically calculate values from your data. With these features, Microsoft Excel offers non-technical users some of the same abilities a more technical user would have.

This is all possible with the standard Excel features. But these features only go so far. What if you want a form to prompt a new user to enter data? What if a user needs to ensure the data goes into the correct fields? What if you have hundreds of rows in your document that you have to clean up? The standard Excel spreadsheet functions will not help you with these tasks. You need more.

The good news is, you can automate tasks like this in Microsoft Excel. You just need to learn how to use Visual Basic for Applications (VBA). With VBA, you can do much more than you can with ordinary formulas. You just need to understand what VBA is and how to use it. In this Excel VBA tutorial, we will talk about what VBA is, how it can help you, and how to use it. 

This tutorial covers some of the basics of VBA programming. We will take a look at the programming language and will cover some of the terms you want to know before you start working with VBA programming. Then we will add a form to an Excel spreadsheet that interacts with users. This will give you an idea of how you can use VBA in an actual project. But first, let’s start with what VBA is.

What is VBA?

VBA is a way to automate the tasks you do at work. It takes the programming language of Visual Basic and gives the user an easy way to use the language with Microsoft Office applications. Excel and the other Microsoft Office applications come with a wide range of features. Some of those features are working behind the scenes or in the basic toolbar. Others, like VBA, function more like add-on features. VBA can add a lot more functionality to your Excel spreadsheets.

Anything that you can do with a mouse or keyboard, you can do with VBA. Once you write a script in VBA, you can use it over and over. You can use the scripting power of VBA to automate tasks that you repeat frequently. You can create forms to interact with users. Overall, you can add new functionality to Microsoft Excel.

Okay, so now you know what VBA is. Next, let’s take a look at a few reasons you would want to use it.

Why should you learn VBA?

Knowing VBA can make you better at the job you have now. You can use it to do your job faster, make tasks smoother, and make your work more efficient. VBA can also help you get a new job. Learning VBA can be an entry point into VBA programming, finance, data analytics, and related fields.

To get started, look for places where you can use VBA in your work. 

When should you use VBA?

A lot of what you will need out of Microsoft Excel you can do with formulas, copying and pasting, and selecting a range of cells. The basic functionality of Excel does its job really well, and you should use it when it is more efficient. With VBA, you can do more tasks, do tasks better, and do tasks faster. Here are some examples of where you would want to use VBA.

Repetitive tasks

Do you ever find yourself or your team doing the same actions over and over in Excel spreadsheets? If so, that could be an opportunity to write some VBA to speed up the task. For example, say you regularly convert CSV files to Microsoft Excel files. Whenever you do, you end up with an invalid character in one column. That character messes up your built-in Excel formatting. You have to fix the invalid character by hand every time. Or, you could use VBA to do the conversion and fix the issue every time.

If you are doing a task by hand, you can find a way to do it with VBA.

Interactions between Office applications

VBA is also great for when you need information in more than one Office application. One advantage to VBA is that the users can interact between all Microsoft Office Suite applications. For example, you can use VBA to move data from an Excel file into a PowerPoint presentation. Or you might use VBA to add client records in an Excel spreadsheet to contacts in Outlook.

If you need to get information from Microsoft Excel to another Office Application, you can get that information with VBA.

User interactions

While Excel workbooks and spreadsheets can be easy to use, not everyone knows everything. Perhaps more importantly, not everyone will know the rules of each specific spreadsheet. Users may not understand what to do with a Microsoft Excel workbook. Everyone designs their Excel spreadsheets differently. You could add a cell or a whole sheet for instructions. But what if users do not see it? One way to guide users through using an Excel workbook or spreadsheet is using VBA. The VBA code can do more than work in the background. You can use it to create buttons, forms, dialog boxes, and other elements to interact with users.

If you need to guide users through using your spreadsheet, you can do that with VBA. 

So how do you learn how to do all of that in VBA? 

What do you need to know before learning VBA?

The good news is what you do not need to know before learning VBA. You do not need to be a Microsoft Excel expert. Some comfort with Excel spreadsheets already will help, but you don’t need to know every aspect of it. VBA can help you avoid using some of the features of Excel and do the task even better. 

You also do not need to know any programming languages. While VBA uses Visual Basic, people with little to no programming experience can start with it, and it uses simple language and code.  

In this tutorial, we will assume you do not know any programming yet. So we will start at the beginning, with the terms and base concepts, before going through an example of using VBA on your own.

What are the main terms in VBA?

One of the best ways to get your grounding in VBA is to make sure you understand the terms we use when we work with it. There are some central ideas in using VBA. Before you start writing VBA code, you should have some knowledge of the terms you may run into. This will help as you read about VBA and start using it. Here are some of the terms and concepts you may need to know:

Macros

Macros are probably the term you will hear the most about when people talk about VBA. Macros are sets of instructions used to automate applications. When you automate that repetitive task, what you create is a macro. VBA is a way to create those Excel macros. So you can use VBA to create a macro to automate a set of instructions for Microsoft Excel.

Sometimes people use the terms procedure or routine interchangeably with macro. The term macros specifically applies to automating existing software with a set of instructions. So how is that different from a procedure?

Procedures

A procedure is a block of code that performs a set of commands. You use that procedure by calling its name. When you create a procedure, you name it and define what code the procedure will run when you call it. Sometimes procedures accept parameters. Parameters are variables that the procedure needs to complete its calculation. We will define variables below in the context of programming. 

Procedures can go inside of modules.

Modules

Modules are containers for separating your code in VBA. You can think of them as one file containing code. This module may include a variety of procedures and variables. Usually, if you are creating a lot of procedures, it helps to organize them by what they do into different modules — for example, a data cleaning module and a forms module. 

Visual Basic Editor has a built-in way to insert these modules. Open the Visual Basic Editor in Excel. You can press Alt+F11 in Windows or FN+ALT+F11 on Mac. Now right click on the project that represents the Microsoft Excel file you have open. If you click Insert -> Module, the editor will show you a file. 

What are some other key terms in VBA?

We have covered the main key terms of VBA. Here are some other programming concepts and VBA terms that you should try to understand as you begin to work with VBA: 

• Statement: A way of saying what the programmer wants to do. In VBA, you can use statements that look like what someone would say in English to explain what you want.  
• Subroutine: A group of statements that can do more than one task.  
• Object VBA: An object-oriented programming language. This means that everything the programmer works with is an object. An object can hold data and code.  
• Function: A reusable set of instructions the programmer can call anywhere in the code. 
• Collection: A gathered amount of data from an external source that the programmer can put into Microsoft Excel. 
• Variable: A location in memory with a name or signifier that holds a piece of information. You can change that information while running your VBA code. 
• Array: Just as a variable holds one piece of information, an array can contain several pieces of information. 
• Condition: A statement that the code tests to see what will happen. It can be True or False. 
• Loop: A code state that allows the programmer to run the same code over and over as needed. 

Some of these are general programming terms that will help you in other programming languages. 

Now that you understand the main ideas and terms in VBA, let’s start using it. The best way to learn is by doing.

How can you create a VBA Macro in Excel?

Let’s say we have an Excel spreadsheet that has two columns in it, Items and Prices. These list product items and prices. Now we want an easy way to record sales. If you are following along, enter some data in an Excel spreadsheet. You can get free sample data to practice with, like car sales in Maryland, from sites like data.gov. Label Column A “Items” and Column E “Sales.”

Now open Preferences in Microsoft Excel. Then go to View. At the bottom, you should see a checkbox that says Developer Tab. Check that checkbox like in the image below and close Preferences.

DeveloperTab

Adding a Button in Excel

Now there should be a new Developer tab in Microsoft Excel. Choose that tab. Now click the Button button in the Excel ribbon menu. When you click it, a menu like below will pop up. Name your macro Add_Sale and click the New button. 

We are going to be writing a macro in VBA that will run when a user clicks the button. You can also create a new macro by recording a macro. This means you have Excel save the steps you do. In this case, we will learn how to make our macro with code. 

This will create a new Sub procedure in Module1 of the Visual Basic Editor named Add_Sale(), ready to add code to. We will get to that later.

Adding a Combo Box for selection

Now we will add a Combo Box so that a user can choose the item that sold. Do this by clicking the Combo Box button in the Developer tab and then clicking the area on the form you want to add it. Now that you have the two elements, you can resize them by right-clicking on the element and dragging them by the corners. You can rename the text on the button by double-clicking it and typing the new text.

Right-click on the Combo Box and choose Assign Macro from the window and click New. This will generate a new Sub procedure in your module with a name like DropDown2_Change(). You can rename this to something like Choose_Item(). If you do that, you would have to right-click the Combo Box again and reassign it to the Sub procedure’s new name.

Now right click on the Combo Box again and choose Format Control. We will use the products in the Items column of the Excel spreadsheet as the values in the Combo Box. Click the Input range field like below and select the item names in the Items column. Then click OK. Once you have done that, you should see the Items’ values when you click on the Combo Box.

Using VBA Code in an Excel form

We have our data in an Excel spreadsheet, we have a form to add sales, and we populated the Combo Box in the form with the items from our store. So far, the form does nothing except the Combo Box. Nothing happens when we choose an item. So that is the first step that we are going to take. Just note that this is an example of how VBA code works, not how you would actually code something to do this. There would be much more to the code you would write to use in a real business.

Where do you go from here with VBA?

Microsoft Excel and spreadsheets, in general, have put a lot of useful tools in the hands of the average user. VBA adds to this. It gives you the ability to script anything you would normally do manually with a mouse or keyboard. This feature is already available if you have Excel. With a little study, you can begin automating repetitive tasks. You can add to the base functionality of Excel with your own VBA modules. With even more VBA practice, you can become a data expert!