Excel VBA Tutorial: Make Your First Macro and Get to Know the Tools

excelvbatutorialIf you have come to use Microsoft Excel at an advanced level, one of the natural steps you might want to take next is to learn to implement and design programs that will automate routine tasks for you.

This is where your application skills begin to crossover in to programming capabilities, and Excel offers some great tools that will help you ease in to this gradually. If you can learn to use the developer tools in Excel, you will be implementing program designs that you’ll be able to transition in to many professional areas, and your credentials will be all the better for it.  Why don’t you get started today with a course designed to walk you through Excel VBA and get you making programs?

This tutorial will tell you how to find the basic tools you will be working with (in Excel 2007)  and start getting you familiar with what they do.

Macros

In the Microsoft Office suite, mini programs that automate tasks are called macros. Macros can be as simple as a keyboard shortcut, or they can be much more complicated, involving multiple inputs, user interfaces, and instruction processing.

You can develop macros manually by programming them in VBA, and this will be your ultimate goal for learning. But you can also start making simple macros through a recording function in Excel.

Developer tab

You access tools for making and editing macros in the Developer tab of Excel. If you do not see this tab available from the main ribbon, you may need to enable it.

To enable the Developer tab, go to File>Options>Excel Options; click on Customize Ribbon in the left window; then under Main tabs click the Developer option and select OK.

When you select the Developer tab, you will see options for accessing Visual Basic, viewing macros in the workbook, recording macros, inserting objects (e.g. buttons, input boxes), etc.

Recording a Macro

It is possible to write a macro without writing a single line of code. Microsoft has included a handy recording function that will allow you to carry out a task in Excel, assign it to an input (like a button or a keystroke), and keep it in the workbook so you can easily use it each time you access that workbook.

If you like the idea of simplifying macro building and you are interested in following a route that does not involve writing in a programming language, you can try out a macro and VBA course taking a coding-free approach.

To record and use your first macro, though, you can start right away with a few easy steps. The simplest type to create is one that involves a keyboard shortcut, rather than a button, so that is the type you will create here.

1. Go to the Developer tab, and select Record Macro (in the code group to the left)

2. Select Begin recording.

3. Assign the shortcut as follows:

a. Select Keyboard.

b. Select the macro you are currently recording from the Commands box.

c. Type the sequence you want to use in the Press new shortcut key box.

d. Close, and your macro will begin recording

4. Carry out whichever action you want to automate. For example, if you are making a simple macro for saving a file, click File then Save. Make it something easy, even if a shortcut already exists.

5. Click Stop recording.

Congratulations! You made a macro. Try it out by pressing the key sequence you assigned.

VBA editor

After you have recorded a simple macro, you can view and edit its code in the Visual Basic editor. All you need to do is select view code from the developer toolbar and select your macro. This is a great way to transition in to coding macros, if you want to take your skills to the next level.

The Visual Basic editor allows you to create full blown programs that operate within Excel and across the Microsoft office programs. Using an object-oriented language, you can design intricate automations that can solve significant business challenges. There is a virtually limitless range of options, and many capabilities you will not be able to accomplish with simple recorded macros.

You will need to build a foundation in a few areas to become proficient using this tool, but it is certainly within your grasp. To start, you can take a course that applies programming principles specifically within the visual basic editor.

This is a good place to begin because your instructor will assume no previous programming knowledge, and you will learn the specific syntax you need to work with. You will also develop some of the building blocks of general programming, and if you choose to, you will be able to carry this over into other programming contexts.

Moving forward

Once you have got the building blocks for working with the Visual Basic editor in Excel, there are a couple of great options for continuing your growth. One is to start learning more about programming techniques, which you can apply to your work with Excel. You may even find that you would like to expand in to new areas as well. A non-programmer’s introductory course in programming could be very helpful to get you a start here.

Another option is to expand your knowledge of VBA in to other areas. For example, you can complete a course in VBA for Microsoft Access and begin to learn how to develop programs that bridge applications.

Summary

Following this basic quickstart, you should now have an idea how to make a basic macro and view it in the visual basic editor. Don’t hesitate to begin building on these skills. Jump right in! Your productivity and your career may be better for it!