Section 25.1. Macros 101

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.

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.

Excel. The Missing Manual
Excel 2010: The Missing Manual
ISBN: 1449382355
EAN: 2147483647
Year: 2003
Pages: 185 © 2008-2017.
If you may any questions please contact us: