25.1. Macros 101
Macros can automate everything from simple tasks to complex operations, making your life immensely easier. Here're 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 might 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 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 might 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. For example, in order 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. For example, 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 menu. 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 a much more powerful and flexible technique. 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.