Management Science: The Art of Modeling with Spreadsheets, 4th Edition

Appendix 2: Macros and VBA

INTRODUCTION

Macros are small computer programs that automate Excel tasks that are performed frequently. Macros are written in a language called Visual Basic for Applications (VBA), so a deep understanding of macros requires knowledge of VBA. Fortunately, Excel provides a mechanism for creating macros simply by recording the steps involved, so many straightforward macros can be created by users who have little or no programming knowledge. In this appendix, we illustrate how to create a macro by recording keystrokes, how to edit a macro using basic concepts from VBA to make it more powerful, and how to turn a macro into a user-defined function.

Any set of steps in Excel that a user repeats frequently is a good candidate for a macro. For example, some organizations require that every spreadsheet has a certain header and footer (containing date, author, and related information) and that the first several columns are formatted in a standard manner. To carry out these tasks manually might require 20–30 separate actions in Excel and take 10–15 minutes every time a new workbook must be created. If these actions can be captured in a macro, the entire sequence can be executed with one key combination that takes only a moment to run. Some firms provide such macros to employees as a way of ensuring compliance with corporate standards for good spreadsheet practice.

Beyond the automation of routine tasks, VBA provides a platform for magnifying the power of Excel. ...