Microsoft Office Tutorials and References
In Depth Information
Chapter 13: Macro-Charged Reporting
In This Chapter
Setting up trusted locations for your macros
Adding macros to your dashboards and reports
A macro is essentially a set of instructions or code that you create to tell Excel to execute any number
of actions. In Excel, macros can be written or recorded. The key word here is recorded .
Recording a macro is like programming a phone number into your cell phone. You first manually dial
and save a number. Then when you want, you can redial those numbers with the touch of a button.
Just as on a cell phone, you can record your actions in Excel while you perform them. While you
record, Excel gets busy in the background, translating your keystrokes and mouse clicks to written
code (also known as Visual Basic for Applications, VBA ). After a macro is recorded, you can play back
those actions anytime you want.
In this chapter, you explore macros and discover how to simplify your life by using macros to
automate recurring processes.
Why Use a Macro?
Macros can help you solve some common data-analysis problems.
➤ Problem 1: Repetitive tasks. As each new month rolls around, you have to make the donuts
(that is, crank out those reports). You have to import that data. You have to update those pivot
tables. You have to delete those columns, and so on. Wouldn’t it be nice if you could fire up a
macro and have those more redundant parts of your dashboard processes done automatically?
➤ Problem 2: Human error. When you do hand-to-hand combat with Excel, you’re bound to
make mistakes. When you’re repeatedly applying formulas, sorting, and moving things
around manually, there’s always that risk of catastrophe. Add to that the looming deadlines