Michael Alexander - Excel 2019 Power Programming with VBA

1 Cover

2 Introduction Introduction For most of us, the path to Excel VBA programming starts with the need to perform some task that can't be done with the standard tools in Excel. That task is different for each of us. Maybe that task is to create separate workbooks automatically for all the rows in a data set. Maybe that task is to automate the emailing of dozens of reports. Whatever that task is for you, you can bet that someone has started their own journey into Excel VBA with the same need. The beautiful thing about Excel VBA is that you don't have to be an expert to start solving problems with it. You can learn just enough to solve a particular problem, or you can go further and discover ways to handle all kinds of automation scenarios. Whatever your goals may be, Excel 2019 Power Programming with VBA will help you harness the power of the VBA language to automate tasks, work smarter, and be more productive. Topics Covered Topics Covered This book focuses on Visual Basic for Applications (VBA), the programming language built into Excel (and other applications that make up Microsoft Office). More specifically, it shows you how to write programs that automate various tasks in Excel. This book covers everything from recording simple macros through creating sophisticated user-oriented applications and utilities. You can approach this book in any way you please. You can read it from cover to cover, or you can skip around, picking up useful tidbits here and there. VBA programming is often a task-oriented endeavor. So, if you're faced with a challenging task, you might try the index first to see where the book might specifically address your problem. This book does not cover Microsoft Visual Studio Tools for Office (VSTO), a technology that uses Visual Basic .NET and Microsoft Visual C#. VSTO can also be used to control Excel and other Microsoft Office applications. As you may know, Excel 2019 is available for other platforms. For example, you can use Microsoft's Excel Web App in your browser and even iPads and tablets. These versions do not support VBA. In other words, this book is for the desktop version of Excel 2019 for Windows. What You Need to Know What You Need to Know This is not a book for beginning Excel users. If you have no experience with Excel, a better choice might be the Excel 2019 Bible (Wiley, 2018), which provides comprehensive coverage of all the features of Excel and is meant for users of all levels. To get the most out of this book, you should be a relatively experienced Excel user who knows how to do the following: Create workbooks, insert sheets, save files, and so on Navigate through a workbook Use the Excel Ribbon user interface Enter formulas Use Excel's worksheet functions Name cells and ranges Use basic Windows features, such as file management techniques and the Clipboard What You Need to Have What You Need to Have Excel is available in several versions, including a web version and a version for tablets and phones. This book was written exclusively for the desktop version of Microsoft Excel 2019 for Windows. If you plan to develop applications that will be used in earlier versions of Excel, we strongly suggest you use the earliest version of Excel that your target audience will be using. Over the last few years, Microsoft has adopted an agile release cycle for the web version of Excel with Office 365, generating release updates practically on a monthly basis. It is important to have a full installation of Excel, and if you want to try the more advanced chapters involving communication between Excel and other Office applications, you will need a full installation of Office. The version of Windows you use is not important. Any computer system that can run Windows will suffice, but you'll be much better off with a fast machine with plenty of memory. Excel is a large program, and using it on a slower system or a system with minimal memory can be extremely frustrating. Please note that this book is not applicable to Microsoft Excel for Mac. Conventions Used in This Book Conventions Used in This Book Take a minute to skim this section and learn about some of the typographic conventions used throughout this book. What the Icons Mean What the Icons Mean Throughout the book, we use icons to call your attention to points that are particularly important. How This Book Is Organized How This Book Is Organized The chapters of this book are grouped into five main parts. How to Use This Book How to Use This Book The topics in this book get more advanced as you progress through it, so you can work through the material from front to back and build your skills as you go. You can also use this book as a reference that you can consult when you need help with the following situations: You're stuck while trying to do something You need to do something that you've never done before You have some time on your hands, and you're interested in learning something new about VBA The index is comprehensive, and each chapter typically focuses on a single broad topic. Don't be discouraged if some of the material is over your head. Most VBA programmers get by just fine by using only a subset of the language. What's on the Website What's on the Website Nearly everything discussed in this book has examples with it. You can (and should) download the many useful examples included with this book. The files are located at www.wiley.com/go/excel2019powerprogramming .

3 Part I: Introduction to Excel VBA CHAPTER 1: Essentials of Spreadsheet Application Development CHAPTER 1 Essentials of Spreadsheet Application Development IN THIS CHAPTER Discovering the basic steps involved in spreadsheet application development Determining end users' needs Planning applications to meet users' needs Developing and testing your applications Documenting your development efforts and writing user documentation What Is a Spreadsheet Application? What Is a Spreadsheet Application? For the purposes of this book, a spreadsheet application is a spreadsheet file (or group of related files) that is designed so that someone other than the developer can perform specific tasks without extensive training. According to this definition, most of the spreadsheet files that you've developed probably don't qualify as spreadsheet applications. You may have dozens or hundreds of spreadsheet files on your hard drive, but it's a safe bet that most of them aren't designed for others to use. A good spreadsheet application does the following: Enables the end user to perform a task that he or she probably would not be able to do otherwise. Provides the appropriate solution to the problem. (A spreadsheet environment isn't always the optimal approach.) Accomplishes what it is supposed to do. This prerequisite may be obvious, but it's not at all uncommon for applications to fail this test. Produces accurate results and is free of bugs. Uses appropriate and efficient methods and algorithms to accomplish its job. Traps errors before the user is forced to deal with them. Does not allow the user to delete or modify important components accidentally (or intentionally). Has a clear and consistent user interface so that the user always knows how to proceed. Has well-documented formulas, macros, and user interface elements that allow for subsequent changes, if necessary. Is designed so that it can be modified in simple ways without making major changes. A basic fact is that a user's needs change over time. Has an easily accessible help system that provides useful information on at least the major procedures. Is designed to be portable and to run on any system that has the proper software (in this case, a copy of a supported version of Excel). It should come as no surprise that it is possible to create spreadsheet applications for many different usage levels, ranging from a simple fill-in-the-blank template to an extremely complex application that uses a custom interface and may not even look like a spreadsheet.