How to Write VBA Code in Excel

Instructor: Dr. Douglas Hawks

Douglas has two master's degrees (MPA & MBA) and is currently working on his PhD in Higher Education Administration.

While Microsoft Excel is a popular spreadsheet and data analysis tool, fewer users are aware of the ability to program custom formulas and macros. In this lesson, we'll introduce the basics of Excel's programming language - VBA.

What is VBA?

VBA, or Visual Basic for Applications, is the programming language used by Microsoft to make Excel do calculations, formatting, charts, and other functionalities. Advanced users of Excel may understand how to use the basic 'Record Macro' option, but fewer understand that when they do that, Excel is actually translating their actions into VBA code. A macro is a set of events in a computer program that is done the same every time it is run. Excel allows you to record macros, but to really make them powerful, you need to learn how to modify and add to the VBA code behind Excel.

How to Access VBA

There are two ways to access VBA - a keyboard shortcut and a toolbar. The toolbar, named the Developer Toolbar, does not automatically show up for most users for Excel. To see the Developer Toolbar, you need to activate it by selecting File > Options > Customize Ribbon, then click the box next to 'Developer' on the right-hand side of the dialog box. Once the Developer Toolbar is visible in Excel, if you click on it, the first choice is 'Visual Basic.'

The other way to access VBA is simply the keyboard shortcut Alt + F11. Although this seems much simpler than setting up the Developer Toolbar, the toolbar has other important functions you will need, so it is important to activate it if you are going to learn and use VBA.

VBA Programming Window
VBA Page

Building Blocks of VBA

Now that you can access VBA, it's time to discuss the basic idea behind programming for Excel. First, you need to think about Excel as a collection of objects. A cell is an object. A range of cells is an object. A row of cells is an object. A tab is an object. A chart is an object. Even the Excel application is an object.

One way to figure out if something is an object in Excel is to determine whether it has properties. Properties are unique attributes associated with an object, such as size, color, borders, values, names, and many more. Using VBA, you can change the properties of objects, which allows you to significantly increase the functionality and customization of Excel.

Programming in VBA

The basic programming method in VBA is very similar to programming in any other computer language, although the syntax is different. Essentially, you first identify an object and then you change the property you want to change. That change might be based on some other event or value, in which case you would specify variables that represent other objects or properties, and this allows you to refer to other parts of your Excel file without your VBA code becoming overly complex and long.

For example, just for convenience (and for simplicity in this lesson), let's say you want to program VBA so that whenever you go to the first sheet of your workbook - named Sheet1 - the cell A1 is always selected. This will ensure you see what you have in the sheet and don't get your cursor in some odd location that shows you a blank screen. Here are the steps you would program to make that happen.

First, you would identify what is going to happen to trigger your programming. VBA makes it possible for this to be clicking a button, using a keyboard shortcut, or any number of events that can occur in Excel. In this case, what is the event that will trigger your code? Remember what we said? Whenever you go to the first sheet of the workbook. So, whenever you click on the little tab at the bottom of Excel that says 'Sheet1,' or in VBA terms - activate Sheet1 - your code will run.

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support