Automating Procedures With Excel Events

Understanding Excel Events 276

Run a Procedure as a Workbook Opens 280

Run a Procedure Before Closing a Workbook 282

Run a Procedure Before Saving a Workbook 284

Run a Procedure When Excel Creates a Workbook 286

Monitor a Range of Cells for Changes 290

Execute a Procedure at a Specific Time 292

Execute a Procedure When You Press Keys 294

Run a Procedure When Right-Clicking a Chart 296


VBA and Excel Object Model Quick Reference 298


What's on the CD-ROM 322

Using the E-Version of the Book 324

Hungry Minds, Inc. End-User License Agreement 326

Vba Scripts


Excel Programming: Your visual blueprint for creating interactive spreadsheets uses simple, straightforward examples to teach you how to create powerful and dynamic programs.

To get the most out of this book, you should read each chapter in order, from beginning to end. Each chapter introduces new ideas and builds on the knowledge learned in previous chapters. When you become familiar with Excel Programming: Your visual blueprint for creating interactive spreadsheets, you can use this book as an informative desktop reference.

Courier Font

Indicates the use of Visual Basic for Applications (VBA) code such as tags or attributes, scripting language code such as statements, operators, or functions, and Excel Object Model code such as objects, methods, or properties.


Indicates information that you must type.

Who This Book Is For

If you are interested in writing macros for Microsoft Excel using Visual Basic for Applications (VBA), Excel Programming: Your visual blueprint for creating interactive spreadsheets is the book for you.

This book takes you through the basics of using the Visual Basic Editor that comes with Microsoft Office, and familiarizes you with the essentials of Visual Basic for Applications programming. The book also covers the Excel Object Model, and illustrates how to use the various objects, properties, and methods to create macros. Although this book requires no prior experience with programming, a familiarity with the Microsoft Windows operating system installed on your computer and Microsoft Excel is an asset.

What You Need To Use This Book

To perform the tasks in this book, you need a computer with Microsoft Windows 98, ME, NT 4.0, 2000, or XP installed, as well as Microsoft Excel 2000 or 2002. You do not require any special development tools, because all the tools are part of Excel.

The Conventions In This Book

A number of typographic and layout styles have been used throughout Excel Macros: Your visual blueprint for creating interactive spreadsheets to distinguish different types of information.


Indicates a new term.

An Apply It section usually contains a segment of code that takes the lesson you just learned one step further. Apply It sections offer inside information and pointers that you can use to enhance the functionality of your code.


An Extra section provides additional information about the task you just accomplished. Extra sections often contain interesting tips and useful tricks to make working with Excel macros easier and more efficient. Please note that the majority of the tasks in this book require that you start by creating a new subroutine and finish by running a macro. Because this book covers creating a new subroute and running a macro in Chapters 3 and 1, respectively, and because the tasks have limited space to restate cross-references to these topics, only the first task of every chapter contains cross-references to these chapters. The rest of the tasks in each chapter assume that the read knows the location of these operations.

The Organization Of This Book

Excel Programming: Your visual blueprint for creating interactive spreadsheets contains 15 chapters and two appendixes.

The first chapter, "Getting Started with Excel Macros," shows you how work with macros in Excel, how to record a simple macro, how to assign macros to a menu or toolbar button, how to launch a macro, and how to remove a macro from a workbook.

Chapter 2, "Using the Visual Basic Editor," shows you how to navigate and work with the Visual Basic Editor that comes with Microsoft Office applications. This chapter shows you how to set up your Visual Basic Editor window to quickly create and modify code modules.

Chapter 3, "VBA Programming Basics," introduces you to the essentials of Visual Basic for Applications (VBA). This chapter also covers some VBA programming fundamentals that enable you to use the material in the following chapters to create your own Excel macros. The fourth chapter, "Working with the Excel Object Model," shows you how to work with the Excel Object Model to access the various elements that make up the Excel application. This chapter provides a basis for the information covered in the remainder of the book, specifically Chapters 9 through 12.

Chapters 5 through 7 build on the VBA programming language by showing you how to work with variables and create arrays. You also learn how to use the various control statements to determine which code is executed within your macros. You create pop-up dialog boxes using the MsgBox and InputBox functions.

Chapter 8, "Debugging Macros," shows you how to the use the various features of the Visual Basic Editor to find programming and logical errors within your VBA code.

Chapters 9 through 12 illustrate how you can use the Workbook, Worksheet, and Range objects to create custom macros. You also learn how to use the corresponding properties and methods associated with these objects.

Chapter 13, "Customizing Dialog Boxes, Menus, and Toolbars," shows you how to create a graphical interface for your macros by creating custom dialog boxes, adding new toolbars, and creating new menus and menu items.

Chapter 14, "Working with Charts," shows you how to create and modify charts from within your macro.

Chapter 15, "Automating Procedures with Excel Events," shows you how to capture both user- and system-created events and use those events to trigger various procedures. You also learn how to execute a procedure at a specific time, or how to determine when a specific key sequence is pressed.

The first appendix contains a reference section. After you become familiar with the contents of this book, you can use the references to obtain at-a-glance information for the VBA statements, functions, and constants used by VBA functions and Excel Object Model properties and methods.

What's on the CD-ROM

The CD-ROM included in this book contains the sample macro code from each of the two-page lessons in Chapters 5 through 15. This saves you from having to type the code and helps you quickly get started creating VBA code. The CD-ROM also contains several shareware and evaluation versions of programs that you can use to work with Excel Programming: Your visual blueprint for creating interactive spreadsheets. An e-version of the book and all the URLs mentioned in the book are also available on the disc.

0 0

Post a comment