Excel Dashboards and Reports, 2nd Edition

Chapter 13: Macro-Charged Reporting

In This Chapter

• Introducing macros

• Recording macros

• 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. ...