Section 27.1. Macros 101 | Excel 2007[c] The Missing Manual

27.1. Macros 101

Macros can automate everything from simple tasks to complex operations, making your life immensely easier. Here are examples of tasks people commonly use macros for:

Quickly inserting a group of cells you need often (like a company header for a report).
Applying complex formatting to multiple rows or columns .
Cleaning up text, rearranging cells, or transferring information from one place to another.
Printing data in a specific format. This operation could involve switching to a custom view, selecting a part of a worksheet, and choosing specific print options (like paper size or the number of copies).
Analyzing a selection of cells, perhaps by adding a set of functions or even by generating a specialized chart.

These examples are just the tip of the macro iceberg. When you create a macro, you can use the full range of Excel's features. It's like having a personal assistant, skilled in the ways of Excel, ready to carry out whatever instructions you provide.

Excel gives you two different ways to create a macro:

Write the macro by hand, using pure VBA code . This option isn't as difficult as you may think, but it isn't easy either. The problem isn't learning the syntax ,or rules, of the VBA language (which are fairly basic); it's learning how to find all the Excel features you want to use. When you want to enter text in a cell , create a new worksheet, or print a selection, you need to find the right Excel object that can handle the job. Objects are programming tools that let you get at features you need. If you want to write macro code that spell checks your spreadsheet, you need to know the name of the spell checker object.
Record the macro using the Excel macro recorder . You turn the recorder on, and then go about your business, entering text, navigating a worksheet, and selecting choices from Excel's ribbon. While you work, Excel records each operation and translates it into the corresponding VBA code. When you're finished, you can stop the recorder, save the macro, and replay it to repeat all the actions you just performed.

Using the macro recorder is the simplest approach, particularly if you've never used a programming language before. However, writing macros by hand is much more powerful and flexible. It lets you create macros that can make decisions, repeat actions in a loop, and even prompt whoever's viewing your worksheet to input some information.

In the next chapter, you'll take a closer look at the intricacies of VBA and start digging through the vast collection of Excel objects. In this chapter, you'll get right to work creating simple macros with the macro recorder.

Note: There's a middle ground between writing macros yourself and having Excel record your actions. You can record a macro with the macro recorder, and then tweak the VBA code with the Visual Basic editor in order to make it more powerful. At the start of the next chapter, you'll take a look at what a recorded macro looks like inside the Visual Basic editor.

27.1.1. Macro-Free and Macro-Enabled Workbooks

At the beginning of this book, you took your first look at Excel's new file formats (Section 1.4.1). So far, you've been sticking with Excel's .xlsx file type, the format of choice. However, .xlsx files have a serious limitation when it comes to macro writingthey can't store macros. That means you need to either choose a different format for your workbook (one that does support macros), or store your macros elsewhere, in a workbook that does have macro-storing abilities .

Fortunately, Excel has two perfectly good file formats that support macros and all the same features as their macro-free counterparts:

.xlsm files are macro-enabled workbooks. They're identical to .xlsx files, except they have the added ability to store macro code. Like .xlsx files, .xlsm files are stored using XML.
.xlsb files are workbooks stored in binary format, which is more efficient for certain types of files (like very large spreadsheets). They also allow macro code.

Microsoft recommends that you use the .xlsm format. In some situations, extremely complex files may load faster if they're stored in the .xlsb format. To review the difference between Excel's XML-based formats (like .xlsm) and its binary formats (like .xlsb), see Section 1.4.1.

FREQUENTLY ASKED QUESTION
A Reason to Like Macro-Free Workbooks

Why doesn't everyone use .xlsm files? Whose idea was it to create a file format that supports fewer features?

At first glance, it seems that Microsoft could have avoided a lot of confusion if it had just created a single file format and made sure that file format was able to store macro files. After all, why encourage people to use a file format that can't do everything?

The reason Microsoft created .xlsx files (and the reason they're standard when you don't need macro support) is security. In recent years , Microsoft's become increasingly paranoid , as have the thousands of companies using Excel in a business setting. Their nightmare is that one day the company accountant opens an innocent-looking Excel spreadsheet, and a malicious macro toasts the computer.

Of course, evil Excel macros are fairly rare, and you're guaranteed to stay safe if you avoid opening spreadsheets that come from mysterious sources (like a "Get Well Endowed" email message). But as more companies use Excel to deal with sensitive, mission-critical information, even a small risk is too high. As you'll learn a little later (Section 27.3), Microsoft automatically disables macros when you first open a document to prevent potential problems. It's up to you to re-enable them and accept the risk.

So what does all this have to do with the two file formats? These formats allow network administrators to quickly distinguish between macro-free files (.xlsx), which are always safe, and potentially risky macro-enabled files (.xslm). And thanks to the different file extensions, administrators can create spam-blocking rules that automatically remove macro-enabled workbooks that are attached to email messages. In other words, a little bit of extra complexity makes it easier to keep company systems safe.