Excel VBA 24-Hour Trainer 2nd Edition

Book Preface

Introduction

You want to learn programming for Microsoft Excel with Visual Basic for Applications (VBA), and you’ve purchased this book to teach you. Excel is the most powerful and widely used spreadsheet application in the world. VBA enables you to become much more productive and efficient, while getting your everyday Excel tasks done more quickly and with fewer errors. You’ll gain a programming skill that is in high demand, which will improve your value in the workplace and your marketability when searching for employment.

This book covers VBA from the ground up, and assumes you have never programmed Excel before. If you’ve never recorded or written an Excel macro, this book shows you how. If you’ve worked with VBA before, this book has examples of programming techniques you might not have seen. The instruction and examples in this book teach VBA concepts that range in levels from fundamental to advanced. The techniques in this book apply just as well to the Excel business power user as to the keeper of the family budget.

VBA is the programming language for Microsoft’s popular Office suite of applications, including Excel, Word, Access, PowerPoint, and Outlook. A full section of this book explains how to control each of those applications from Excel with VBA. By the time you complete this book, you will have learned how to record, write, and run your own macros. You’ll learn how to make VBA run itself by programming Excel to monitor and respond to users’ actions, and how to create friendly, customized interfaces that the users of your workbooks will enjoy.

The future of VBA is solid. Microsoft has confirmed time and again that VBA will be supported in versions of Excel into the foreseeable future, and the programming skills you learn in this book will serve you throughout your career. You’ll be able to apply the principles you learn in this book to other tasks that can be automated in Excel and Microsoft’s other Office applications. VBA is an enormous programming language, and when combined with Excel, using it is an ongoing, rewarding process of learning something new every day. With this book as your entry into the world of VBA programming, you are well on your way.

Who This Book Is For

This book is for Excel users who have never programmed Excel before. You are an Excel user who has been doing a frequent task manually, and you are ready to automate the task with VBA. You might also be a job seeker, and you want to improve your chances of being hired in this difficult job market by learning a valuable skill. Whether your Excel tasks are large or small, this book is for you. You find out how to use VBA to automate your work by doing anything from recording a simple one-line macro to writing a complex program with a customized, user-friendly interface that will look nothing like Excel. This book has something for everyone, but especially for the person who wants to dive right into VBA from square one and learn to use its powerful programming tools.

What This Book Covers

This book contains 33 lessons, which are broken into five parts:

• Part I, Understanding the BASICs: Part I includes Lessons 1 to 4, introducing you to VBA by providing a historical background and a discussion of what VBA is and what it can do for you. This part familiarizes you with the Macro Recorder and the Visual Basic Editor, where VBA code is maintained.
• Part II, Diving Deeper Into VBA: Part II includes Lessons 5 to 9, which discuss VBA topics including an overview of object-oriented programming, variable declaration, objects and collections, arrays, and options for decision-making.
• Part III, Beyond the Macro Recorder: Writing Your Own Code: Part III includes Lessons 10 to 20. You learn how to write your own macros without help from the Macro Recorder. You become familiar with loops, event programming at the workbook and worksheet levels, charts, PivotTables, user-defined functions, and embedded controls. You learn to program formulas and how to debug your VBA code.
• Part IV, Advanced Programming Techniques: Part IV includes Lessons 21 to 28, and deals with the more advanced topics of UserForms, class modules, add-ins, retrieving external data, and various examples of programming Excel to achieve solutions you might not have thought possible.
• Part V, Interacting with Other Office Applications: Part V includes Lessons 29 to 33, dealing with how to control Word, Outlook, Access, and PowerPoint from Excel.