Excel Macro Tutorial: Introduction

1. Introduction

1.1 What Is a Macro?

You can think of a macro as a mini computer program within Excel. A macro is a series of statements or commands. The simplest macro consists only of a sequence of commands; in other words, a macro that automatically performs operations that you could do manually in Excel. More complex macros include such programming features as variables, decisions, loops, and user interaction. These programming features are implemented using the statements that are part of Visual Basic for Applications ("VBA"), the macro language for Microsoft Office programs.

1.2 Advantages of Macros

Macros are run with just a few keystrokes, or by one or two mouse clicks, so they can automate repetitive tasks and save a lot of time. Macros guarantee consistency and accuracy; because they always run the same way, there is no chance of operator error (provided the macro was programmed correctly!). Macros can also simplify the implementation of features in the program, or perform operations in the program that a user might not know how to do manually.

1.3 Programming Skills Required for Macros

Simple macros can be created just by storing keystrokes or mouse clicks, so for those kinds of macros you do not need to know anything about programming. You just need to know how to perform the operation in Excel. Creating a macro in this way is known as "recording" a macro. More complex macros that incorporate logic or operator interaction use programming techniques, which require some practice to master. These programming techniques are covered in later chapters of this tutorial.

1.4 Scope of this tutorial

This tutorial is aimed at beginner-level macro writers. No previous macro-writing or programming experience of any kind is assumed. The tutorial covers macro commands that operate on elements of an Excel workbook (using the "Object Model" discussed later in this tutorial). The tutorial introduces basic VBA language statements for variables, user interaction, decisions, and loops. Not covered are more advanced concepts such as arrays and user-created dialog boxes. Once you are comfortable using the features covered in this tutorial, I encourage you to explore the advanced features of VBA, using books or resources available on the internet.

This tutorial is based on Excel 2003. Where there are differences in Excel 2007/2010 I have tried to note them.

1.5 Resources for more information

Detailed information on all macro topics is available from VBA's on-line Help. Unfortunately Help on the specific topic needed can sometimes be hard to find.

Probably the best source of information regarding macros and VBA is the internet. A search on a particular topic or problem will almost always turn up solutions and information from a variety of websites and message boards.

For users who prefer consulting a book, comprehensive manuals for Excel macros are available from O'Reilly. Books on Excel macros were also published by Wrox; they are out of print but might be available used.