Understanding Your Options for Automating Tasks
With Excel 2011 on Mac OS X, you can automate tasks in several ways
- Create macros. A macro is a sequence of commands that you can run all at once. The easy way to create a macro is to record it by turning on the Macro Recorder and performing the actions you want the macro to repeat; when you turn the Macro Recorder off, you have a macro that you can run again. You can also write macros from scratch by working in the Visual Basic Editor application. Or you can record macros and then use the Visual Basic Editor to edit out unwanted commands or to add other commands. This chapter shows you how to record macros and edit them in the Visual Basic Editor.
- Use Automator. The Automator application, which you'll find in the Applications folder, lets you assemble workflows consisting of actions. If you have the Home & Business Edition of Office, you can include Excel actions in workflows. (If you have Office 2011 Home & Student Edition, you can't use Excel with Automator.) This chapter doesn't cover Automator.
- Use AppleScript. AppleScript is a scripting language in which you can write scripts using the AppleScript Editor utility, which you'll find in the Applications/Utilities folder. (A script is essentially a small application.) This chapter doesn't cover AppleScript either.
TIP: When you're getting started automating tasks in Excel, VBA is usually the best place to start. VBA works from within Excel, so you run your macros from Excel. By contrast, an AppleScript runs outside the applications it manipulates. The advantage to using AppleScript is that you can easily manipulate multiple applications rather than just Excel. For example, you can make the Finder take an action, make Excel take an action, then make iTunes dance—all in the same script. You can also share macros with Windows users, whereas AppleScript and Automator run only on Mac OS X.
ADDING THE DEVELOPER TAB TO THE RIBBON
If you need to work extensively with macros, add the Developer tab to the Ribbon to give yourself easy access to essential commands.
To add the Developer tab to the Ribbon, follow these steps:
- Choose
Excel
Preferencesor press Cmd+, (Cmd and the comma key) to display the Excel Preferences dialog box. - In the Sharing and Privacy area, click the Ribbon icon to display the Ribbon preferences pane.
- Scroll down to the bottom of the “Show or hide tabs, or drag them into the order you prefer” list box, then select the Developer check box.
- Click the OK button to close the Ribbon Preferences dialog box.
The Developer tab then appears as shown in Figure 14-1, and you can use the four buttons in the Visual Basic group:
- Editor. Click this button to launch or switch to the Visual Basic Editor.
- Macros. Click this button to display the Macro dialog box.
- Record. Click this button to display the Record Macro dialog box.
- Relative Reference. Click this button to make the Macro Recorder record relative references in a macro rather than absolute references.
Figure 14-1. Add the Developer tab to Excel's Ribbon to give yourself easy access to essential commands for working with macros and VBA.