Excel Macros For Dummies. Alexander Michael

Warning icons hold critical information about pitfalls you will want to avoid.

      Beyond the Book

      In addition to the material in the print or e-book you’re reading, this product comes with more online goodies:

      ❯❯ Sample files: Each macro in this book has an associated sample file that enables you to see the macro working and to review the code. You can use the sample files also to copy and paste the code into your environment (as opposed to typing each macro from scratch). Download the sample files at:

      www.dummies.com/go/excelmacros

      Each macro in this book has detailed instructions on where to copy and paste the code. In general terms, you open the sample file associated with the macro, go to the Visual Basic Editor (by pressing Alt+F11), and copy the code. Then you go to your workbook, open the Visual Basic Editor, and paste the code in the appropriate location.

      

Note that in some macros, you need to change the macro to suit your situation. For example, in the macro that prints all workbooks in a directory (see Chapter 4), you point to the C: \Temp\ directory. Before using this macro, you must edit it to point to your target directory.

      

If a macro is not working for you, most likely a component of the macro needs to be changed. Pay special attention to range addresses, directory names, and any other hard-coded names.

      ❯❯ Cheat sheet: The cheat sheet offers shortcut keys that can help you work more efficiently in Excel’s Visual Basic Editor. You can find the cheat sheet by visiting www.dummies.com and searching for “Excel Macros Cheat Sheet”.

      Where to Go from Here

      If you’re completely new to Excel macros, start with Part 1 (Chapters 1 – 3) to get the fundamentals you’ll need to leverage the macros in this book. There, you will gain a concise understanding of how macros and VBA work, along with the basic foundation you need to implement the macros provided in this book.

      If you’ve got some macro experience and want to dive right into the macro examples, feel free to peruse Chapters 4 – 9 and search for the task or macro that looks interesting to you. Don’t worry. Each macro example stands on its own within its own section that gives you all the guidance you need to understand and implement the code in your own workbook.

      Visit Part 2 if you’re interested in macros that automate common workbook and worksheet tasks to save time and gain efficiencies.

      Explore Part 3 to find macros that navigate ranges, format cells, and manipulate the data in your workbooks.

      If you want to find macros that work with PivotTables, charts, and emails, thumb through the macros in Part 4 where you will discover macros that automate redundant PivotTable and chart tasks, as well as macros that send emails and connect to external data sources.

      Don’t forget to hit Part 5 for some useful tips and advice on how to get the most out of your new macro skills.

      Here are some final things to keep in mind while working with the macros in this book:

      ❯❯ Any file that contains a macro must have the .xlsm file extension. See the section on macro-enabled file extensions in Chapter 1 for more information.

      ❯❯ Excel does not run macros until they are enabled. As you implement these macros, you and your customers must comply with Excel’s macro security measures. See the section in Chapter 1 on macro security in Excel for details.

      ❯❯ You cannot undo macro actions. When working in Excel, you can often undo the actions you've taken because Excel keeps a log (called the undo stack) recording your last 100 actions. However, running a macro automatically destroys the undo stack, so you can't undo the actions you take in a macro.

      ❯❯ You need to tweak the macros to fit your workbook. Many of the macros reference example sheet names and ranges that you may not have in your workbook. Be sure to replace references like “Sheet 1” or Range(“A1”) with the sheet names and cell addresses you are working with in your own workbooks.

Part 1

      Holy Macro Batman!

      IN THIS PART …

      Build a foundation for your macro skills with fundamental macro recording concepts.

      Get a solid understanding of the ground rules for using and distributing macros in Excel.

      Explore Excel’s coding environment with a deep-dive of the Visual Basic Editor.

      Explore how to leverage the Excel object model to start writing your own macros from scratch.

      Understand the roles played by variables, events, and error handling in macro development.

Chapter 1

      Macro Fundamentals

      IN THIS CHAPTER

      ❯❯ Why use macros

      ❯❯ Recording macros

      ❯❯ Understanding macro security

      ❯❯ Examples of macros in action

      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’ll explore macros and learn how you can use macros to automate your recurring processes to simplify your life.

      Why Use a Macro?

      The first step in using macros is admitting you have a problem. Actually, you may have several 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 PivotTables. 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 – You’re making mistakes: When you go 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 and constant change requests, and your error rate goes up. Why not calmly record a macro, ensure that everything is running correctly, and then forget it? The macro is sure to perform every action the same way every time you run it, reducing the chance of errors.

      ❯❯