In its broadest sense, a macro is a sequence of instructions that automates some aspect of Excel so that you can work more efficiently and with fewer errors. You might create a macro, for example, to format and print a month-end sales report. After you develop the macro, you can execute it to perform many time-consuming procedures automatically.

Macros are written in VBA, which stands for Visual Basic for Applications. VBA is a programming language developed by Microsoft and a tool used to develop programs that control Excel.

Excel programming terminology can be a bit confusing. For example, VBA is a programming language but also serves as a macro language. What do you call something written in VBA and executed in Excel? Is it a macro or is it a program? Excel’s Help system often refers to VBA procedures as macros, so this is the terminology used in this book.

You’ll also see the term automate throughout this book. This word means that a series of steps are completed automatically. For example, if you write a macro that adds color to some cells, prints the worksheet, and then removes the color, you have automated those three steps.

You’re probably aware that people use Excel for thousands of different tasks. Here are just a few examples:

• Keeping lists of things, such as customer names and transactions
• Budgeting and forecasting
• Analyzing scientific data
• Creating invoices and other forms
• Developing charts from data

The list could go on and on. The point is simply that Excel is used for a wide variety of tasks, and everyone reading this book has different needs and expectations regarding Excel. One thing most readers have in common, however, is the need to automate some aspect of Excel, which is what macros (and this book) are all about.

About This Book

This book approaches the topic of Excel macros with the recognition that programming VBA takes time and practice — time that you may not have right now. In fact, many analysts don’t have the luxury of taking a few weeks to become expert at VBA. So instead of the same general overview of VBA topics, this book provides some of the most commonly used real-world Excel macros.

Each section in the book outlines a common problem and provides an Excel macro to solve the problem — along with a detailed explanation of how the macro works and where to use it.

Each section presents the following:

• The problem
• The macro solution
• How the macro works

After reading each section, you'll be able to

• Immediately implement the required Excel macro
• Understand how the macro works
• Reuse the macro in other workbooks or with other macros

The macros in this book are designed to get you up and running with VBA in the quickest way possible. Each macro tackles a common task that benefits from automation. The idea here is to learn through application. This book is designed so that you can implement the macro while getting a clear understanding of what the macro does and how it works.

Foolish Assumptions

I make three assumptions about you as the reader:

• You've installed Microsoft Excel 2007 or a higher version.
• You have some familiarity with the basic concepts of data analysis, such as working with tables, aggregating data, creating formulas, referencing cells, filtering, and sorting.
• You have an Internet connection so you can download the sample files.

Icons Used In This Book

Throughout this book, you'll see a few nifty icons that call out items that deserve special mention. Here is a list of these icons and what they mean.

Technical icons outline some of the technical aspects of the topic being discussed.

Tip icons cover tricks or techniques related to the current discussion.

Remember icons indicate notes or asides that are important to keep in mind.

Warning icons hold critical information about pitfalls you will want to avoid.

Beyond the Book

In additional to the material in the print or ebook you’re reading, this product comes with more online goodies:

• Sample files: Each macro in this book has an associated sample file that enables you to see the macro working and to review the code. You can use the sample files also to copy and paste the code into your environment (as opposed to typing each macro from scratch). Download the sample files at

  www.dummies.com/extras/excelmacros

  Each macro in this book has detailed instructions on where to copy and paste the code. In general terms, you open the sample file associated with the macro, go to Visual Basic Editor (by pressing Alt+F11), and copy the code. Then you go to your workbook, open Visual Basic Editor, and paste the code in the appropriate location.

  Note that in some macros, you need to change the macro to suit your situation. For instance, in the macro that prints all workbooks in a directory (see Chapter 4), you point to the C:Temp directory. Before using this macro, you must edit it to point to your target directory.

  If a macro is not working for you, most likely a component of the macro needs to be changed. Pay special attention to range addresses, directory names, and any other hard-coded names.

• Cheat sheet: The cheat sheet offers shortcut keys that can help you work more efficiently in Excel’s Visual Basic Editor. You can find the cheat sheet at

  www.dummies.com/cheatsheet/excelmacros

• Web extras: You’ll find some great references that you can use, including a resume template, a sample resume, and a list of websites of value to networking professionals. Go to

  www.dummies.com/extras/excelmacros

• Updates: If we have any updates to this book, you can find them at

  www.dummies.com/go/excelmacroupdates

Where to Go from Here

If you’re new to Excel macros, start with Chapters 1–3 to get the fundamentals you’ll need to leverage the macros in this book. You'll gain a concise understanding of how macros and VBA work, along with the basic foundation you'll need to implement the macros provided in this book

If you have some macro experience and want to dive right into the macro examples, feel free to peruse Chapters 4–9 for a task or macro that looks interesting to you. Each macro example stands on its own; within each section, you get all the guidance you'll need to understand and implement the code in your own workbook.

Visit Chapters 4 and 5 if you’re interested in macros that automate common workbook and worksheet tasks to save time and gain efficiencies.

Explore Chapters 6 and 7 to find macros that navigate ranges, format cells, and manipulate the data in your workbooks.

If you want to find macros that automate redundant pivot table and chart tasks as well as macros that send emails and attachments, thumb through the macros in Chapters 8 and 9.

Don’t forget to read Chapters 10 and 11 for some useful tips and advice on how to get the most out of your new macro skills.

Here are some final things to keep in mind while working with the macros in this book:

• Any file that contains a macro must have the .xlsm file extension. See the section on macro-enabled file extensions in Chapter 1 for more information.
• Excel will not run macros until they are enabled. As you implement these macros, you and your customers must comply with Excel’s macro security measures. See the section in Chapter 1 on macro security in Excel for details.
• You cannot undo macro actions. When working in Excel, you can often undo the actions you've taken because Excel keeps a log (called the undo stack) recording your last 100 actions. However, running a macro automatically destroys the undo stack, so you can't undo the actions you take in a macro.
• You need to tweak the macros to fit your workbook. Many of the macros reference example sheet names and ranges that you may not have in your workbook. Be sure to replace references such as Sheet 1 or Range(“A1”) with the sheet names and cell addresses you are working with in your own workbooks.