3 ページ
Programming Excel with VBA: A Practical Real-World Guide Flavio Morgado Rio de Janeiro, Brazil ISBN-13 (pbk): 978-1-4842-2204-1 ISBN-13 (electronic): 978-1-4842-2205-8 DOI 10.1007/978-1-4842-2205-8 Library of Congress Control Number: 2016957877 Copyright © 2016 by Flavio Morgado This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein. Cover image designed by FreePik Managing Director: Welmoed Spahr Lead Editor: Gwenan Spearing Technical Reviewer: Fabio Claudio Ferracchiati Editorial Board: Steve Anglin, Pramila Balan, Laura Berendson, Aaron Black, Louise Corrigan, Jonathan Gennick, Robert Hutchinson, Celestin Suresh John, Nikhil Karkal, James Markham, Susan McDermott, Matthew Moodie, Natalie Pao, Gwenan Spearing Coordinating Editor: Mark Powers Copy Editor: Kim Wimpsett Compositor: SPi Global Indexer: SPi Global Artist: SPi Global Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail orders-ny@springer -sbm.com, or visit www.springer.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation. For information on translations, please e-mail rights@apress.com, or visit www.apress.com. Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales– eBook Licensing web page at www.apress.com/bulk-sales. Any source code or other supplementary materials referenced by the author in this text are available to readers at www.apress.com/9781484222041. For detailed information about how to locate your book’s source code, go to www.apress.com/source-code/. Readers can also access source code at SpringerLink in the Supplementary Material section for each chapter. Printed on acid-free paper
5 ページ
Contents at a Glance About the Author .....................................................................................................xv About the Technical Reviewer ...............................................................................xvii Acknowledgments ..................................................................................................xix Introduction ............................................................................................................xxi ■Chapter 1: Understanding Visual Basic for Applications (VBA)............................. 1 ■Chapter 2: Programming the Microsoft Excel Application Object ....................... 81 ■Chapter 3: Programming the Microsoft Excel Workbook Object ....................... 149 ■Chapter 4: Programming the Microsoft Excel Worksheet Object ...................... 185 ■Chapter 5: Programming the Microsoft Excel Range Object ............................. 239 ■Chapter 6: Special Range Object Properties and Methods ................................ 333 ■Chapter 7: Using Excel as a Database Repository ............................................. 427 ■Chapter 8: Creating and Setting a Worksheet Database Class .......................... 495 ■Chapter 9: Exchanging Data Between Excel Applications ................................. 603 ■Chapter 10: Using the Windows API .................................................................. 711 ■Chapter 11: Producing a Personal Ribbon Using RibbonEditor.xlam................. 761 Afterword.............................................................................................................. 779 Index..................................................................................................................... 783 v
6 ページ
Contents About the Author .....................................................................................................xv About the Technical Reviewer ...............................................................................xvii Acknowledgments ..................................................................................................xix Introduction ............................................................................................................xxi ■Chapter 1: Understanding Visual Basic for Applications (VBA)............................. 1 What Is Macro Code? ....................................................................................................... 1 The VBA Environment ....................................................................................................... 2 Modules: The VBA Documents.......................................................................................... 3 Standard and Class Modules .................................................................................................................. 6 The VBA Language ........................................................................................................... 7 Procedures: The VBA Code...................................................................................................................... 7 Using Function and Sub Procedures....................................................................................................... 7 Variable Declaration ............................................................................................................................. 18 Variable Scope and Lifetime................................................................................................................. 27 Create a Flow Chart for the Algorithm of Complex Procedures ............................................................ 32 Comment Your Code! ............................................................................................................................ 36 Using Public Code Module Variables .................................................................................................... 38 Public Procedures and Variables Constitute the Module Interface....................................................... 40 Using Enumerators ............................................................................................................................... 40 Passing Arguments by Reference or by Value ...................................................................................... 42 Using a Naming Convention ........................................................................................... 42 Using Property Procedures............................................................................................. 46 Property Procedures Allow Greater Control of Private Variables .......................................................... 48 vii
7 ページ
■ CONTENTS VBA Statements, Functions, and Instructions................................................................. 48 Using VBA Instructions.......................................................................................................................... 49 Using Event Procedures ................................................................................................. 54 Using Class Modules ...................................................................................................... 57 Declaring and Raising Events on Object Code Modules ....................................................................... 58 Using VBA UserForms..................................................................................................... 59 The VBA Me Keyword............................................................................................................................ 64 Evoking a VBA Procedure from an Excel Worksheet ...................................................... 64 Two Special VBA Functions: MsgBox and InputBox........................................................ 67 Using MsgBox( ).................................................................................................................................... 67 Using InputBox...................................................................................................................................... 72 Dealing with VBA Errors ................................................................................................. 75 The On Error Resume Next Instruction ................................................................................................. 76 Setting an Error Trap............................................................................................................................. 76 Protecting Your VBA Code............................................................................................... 77 Conclusion...................................................................................................................... 78 Summary........................................................................................................................ 78 ■Chapter 2: Programming the Microsoft Excel Application Object ....................... 81 The Microsoft Excel Object Model.................................................................................. 81 The Application Object.................................................................................................... 82 Using Application Properties to Control the Way the Excel Interface Behaves ..................................... 87 Using Application Methods to Show Excel File Dialogs ........................................................................ 88 Using Application Events to React to User Actions ............................................................................. 125 Chapter Summary ........................................................................................................ 147 ■Chapter 3: Programming the Microsoft Excel Workbook Object ....................... 149 The Workbook Object ................................................................................................... 149 Using Workbook Object Events........................................................................................................... 151 Setting Workbook Object References ................................................................................................. 164 Chapter Summary ........................................................................................................ 184 viii
8 ページ
■ CONTENTS ■Chapter 4: Programming the Microsoft Excel Worksheet Object ...................... 185 The Worksheet Object .................................................................................................. 185 Using Worksheet Object Events.......................................................................................................... 188 Referring to Worksheets ..................................................................................................................... 191 Using Worksheet Object Properties and Methods .............................................................................. 197 Chapter Summary ........................................................................................................ 237 ■Chapter 5: Programming the Microsoft Excel Range Object ............................. 239 The Range Object ......................................................................................................... 239 Using the Application.Range Property.......................................................................... 243 Using Range Object Properties and Methods............................................................... 245 Updating the UserForm Interface ....................................................................................................... 247 Resizing the Selected Range.............................................................................................................. 254 Changing the Range Reference .......................................................................................................... 257 Using the Names Collection ......................................................................................... 258 Hiding Named Ranges ........................................................................................................................ 260 Resizing Named Ranges..................................................................................................................... 262 Using Name Object Properties and Methods................................................................ 265 Recovering Name Object Properties................................................................................................... 269 Adding a New Name Object................................................................................................................ 278 Selecting Items in the lstNames ListBox ............................................................................................ 290 Editing an Existing Name Object ........................................................................................................ 306 Resizing an Existing Name Object ...................................................................................................... 317 Performing Multiple Name Properties Changes ................................................................................. 322 Changing the Name.RefersTo Property............................................................................................... 326 Chapter Summary ........................................................................................................ 332 ■Chapter 6: Special Range Object Properties and Methods ................................ 333 Defi ning a Range with VBA........................................................................................... 333 Using the Range.End Property ............................................................................................................ 335 Using the Range.CurrentRegion Property ........................................................................................... 336 Moving Through a Range with VBA..................................................................................................... 336 Creating the USDA Range Name......................................................................................................... 339 ix
9 ページ
■ CONTENTS Sorting Range Names................................................................................................... 341 Using Cascading Data Validation List Cells .................................................................. 343 The USDA Food Composer.xlsm Worksheet Application ..................................................................... 345 Creating USDA Worksheet Range Names..................................................................... 347 Finding the Last Worksheet Used Cell.......................................................................... 353 Finding the Last Worksheet Row/Column with Range.End................................................................. 353 Finding the Last Worksheet Row/Column with Worksheet.UsedRange.............................................. 354 Warning: Range.End Method and Hidden Rows.................................................................................. 356 Finding Range Information........................................................................................... 358 The Range.Find Method...................................................................................................................... 359 The Range.AutoFilter Method ............................................................................................................. 379 Finding Food Items with the Range.Sort Method................................................................................ 396 Using frmSearchFoodItems.xlsm ................................................................................. 415 Showing Selected Food Item Common Measures .............................................................................. 417 Returning the Selected Food Item ...................................................................................................... 419 Researching for a Selected Food Item................................................................................................ 423 Chapter Summary ........................................................................................................ 425 ■Chapter 7: Using Excel as a Database Repository ............................................. 427 The Worksheet Database Storage System ................................................................... 427 The BMI Companion Chart............................................................................................ 428 The BMI Companion Chart_Database.xlsm Excel Application ...................................... 433 Parameterization of BMI Chart Data ................................................................................................... 438 Changing BMI Chart Data ................................................................................................................... 441 Saving the Last Selected Record........................................................................................................ 444 Saving BMI Chart Data........................................................................................................................ 444 Loading BMI Chart Data...................................................................................................................... 470 Deleting BMI Chart Data ..................................................................................................................... 471 Associating Database Procedures to Worksheet Button Controls ...................................................... 477 Making Copies of the BMI Chart Worksheet ....................................................................................... 478 x
10 ページ
■ CONTENTS The USDA Food Composer_Database.xlsm Excel Application ...................................... 479 Changing Database Constant Values .................................................................................................. 483 Saving Recipe Data............................................................................................................................. 484 Saving Recipe Nutritional Information in My_Recipes Range Name.................................................. 486 Deleting a Recipe Data ....................................................................................................................... 487 Things That Are Worth Being Mentioned............................................................................................. 488 Chapter Summary ........................................................................................................ 493 ■Chapter 8: Creating and Setting a Worksheet Database Class .......................... 495 Creating a Database Class ........................................................................................... 495 Steps 1 and 2: Create the Database Class Module............................................................................. 496 Step 3: Create an Object Variable to Capture Worksheet Events ........................................................ 497 Steps 4 and 5: Change Constant Names to Variable Declarations...................................................... 498 Step 6: Save Database Properties as Range Names .......................................................................... 501 Step 7: Use the Class_Initialize( ) Event to Read Database Properties............................................... 503 Referencing the clsDatabase Class .................................................................................................... 504 Improving the clsDatabase Class Interface.................................................................. 506 Improving the Object Model................................................................................................................ 507 Using the SheetDBEngine Class ................................................................................... 532 Producing Data Navigation Controls................................................................................................... 536 Setting the Worksheet Database Class ........................................................................ 540 Implementing the Worksheet Database Wizard.................................................................................. 540 Using the SheetDBEngine Class and frmDBProperties....................................................................... 601 Conclusion.................................................................................................................... 601 Chapter Summary ........................................................................................................ 601 ■Chapter 9: Exchanging Data Between Excel Applications ................................. 603 Updating the USDA Worksheet ..................................................................................... 603 Using the USDA Food List Creator Application.................................................................................... 604 The USDA Worksheet Updating Method.............................................................................................. 606 Using the USDA Food Composer_SheetDBEnginebasUSDA.xlsm Application .................................... 607 Warning About USDA Worksheet Updates .......................................................................................... 620 Using the USDA Food Composer_SheetDBEnginefrmUSDA.xlsm Application .................................... 622 xi
11 ページ
■ CONTENTS Managing Worksheet Application Data......................................................................... 641 The frmManageRecipes_Initialize( ) Event ......................................................................................... 643 Inserting Copies of the My Recipes Sheet Tab.................................................................................... 648 Selecting Desired Recipes.................................................................................................................. 653 Saving Recipe Nutritional Information in the My_Recipes Range Name............................................ 655 Exporting and Importing Recipe Data................................................................................................. 667 Deleting Recipes from This Workbook................................................................................................ 699 Exporting/Importing Recipes with Database Copy/Paste Methods..................................................... 703 Conclusion.................................................................................................................... 709 Chapter Summary ........................................................................................................ 709 ■Chapter 10: Using the Windows API .................................................................. 711 The Microsoft Windows API.......................................................................................... 711 Using Declare Statements............................................................................................ 712 Constants Declaration ........................................................................................................................ 713 Window Handles........................................................................................................... 713 Class Instance Handle .................................................................................................. 714 Creating a Timer Class ................................................................................................. 714 The TimerProc( ) Procedure ................................................................................................................ 718 Using the Timer Class ......................................................................................................................... 718 UserForm Handle.......................................................................................................... 720 Setting Bit Values ......................................................................................................... 721 Animating the UserForm Window................................................................................. 722 The Animate( ) Procedure ................................................................................................................... 724 Manipulating the UserForm Window ............................................................................ 729 The UserForm Title Bar ....................................................................................................................... 730 The Appearance( ) Procedure ............................................................................................................. 732 The UserForm Transparency............................................................................................................... 737 xii
12 ページ
■ CONTENTS Applying a Skin to a UserForm..................................................................................... 743 Device Contexts.................................................................................................................................. 744 Changing the UserForm Shape Using Windows Regions.................................................................... 745 The Skin( ) Procedure ......................................................................................................................... 748 The frmSkin UserForm........................................................................................................................ 753 The USDA Food Composer_frmAbout.xlsm Application...................................................................... 755 Conclusion.................................................................................................................... 758 Chapter Summary ........................................................................................................ 759 ■Chapter 11: Producing a Personal Ribbon Using RibbonEditor.xlam................. 761 How Personal Ribbon Information Is Stored................................................................. 762 Using RibbonEditor.xlam............................................................................................... 762 Adding Tabs, Groups, and Buttons Using the RibbonX Add-In ............................................................ 767 Removing the CustomUI.xml File from a Workbook Application......................................................... 775 Producing a Nice Ribbon with RibbonEditor.xlam............................................................................... 776 Conclusion.................................................................................................................... 777 Chapter Summary ........................................................................................................ 777 Afterword.............................................................................................................. 779 Index..................................................................................................................... 783 xiii
13 ページ
About the Author Flavio Morgado is a food engineer with a master’s degree in food science and technology. He is also a VBA professional developer, a technical writer, an English to Brazilian Portuguese technical translator, and a professor at UNIFESO – Centro Universitário Serra dos Órgãos in the city of Teresopolis in Rio de Janeiro, Brasil. He has written more than 30 books (and translated an equal number) — all published just in Brazil. Flavio lives in Teresopolis; when he is not teaching, writing, or developing a love of something (or somebody…), he is running or riding his mountain bike throughout the Teresopolis Mountains, followed by his 11 dogs (2016’s dog count). xv
14 ページ
About the Technical Reviewer Fabio Claudio Ferracchiati is a senior consultant and a senior analyst/developer using Microsoft technologies. He works for BluArancio (www.bluarancio.com). He is a Microsoft Certified Solution Developer for .NET, a Microsoft Certified Application Developer for .NET, a Microsoft Certified Professional, and a prolific author and technical reviewer. Over the past ten years, he’s written articles for Italian and international magazines and coauthored more than ten books on a variety of computer topics. xvii
15 ページ
Acknowledgments This book shows how to improve worksheet applications using Visual Basic for Applications (VBA). A great part of it was inspired or is based on content and knowledge available for free on the Internet. So, I like to thank the existence of these VBA Internet sites (in alphabetical order): • Better Solutions: www.bettersolutions.com/ • Excel Matters: http://excelmatters.com/ • ExcelVBA: http://excelevba.com.br/ • Mr. Excel: www.mrexcel.com • Microsoft System Development Network (MSDN): https://msdn.microsoft.com/ • Ozgrid: www.ozgrid.com/VBA/find-method.htm • Pearson Software Consulting: www.cpearson.com/ • Ron de Bruin Excel Automation: www.rondebruin.nl/win/section2.htm • StackExchange: http://stackexchange.com/ • StackOverflow em Português: http://pt.stackoverflow • StackOverflow: http://stackoverflow • VB-fun.de: www.vb-fun.de/ And thanks for the existence of these nutrient Internet sites (in alphabetical order): • Agricultural Research Serving of United States: www.ars.usda.gov • EatingWell: www.eatingwell.com/ • Food and Agriculture Organization for the United Nations: www.fao.org • National Heart, Lung, and Blood Institute: www.nhlbi.nih.gov • The Dash Diet Eating Plan: dashdiet.org • Wikipédia: www.wikipedia.com I also want to thank Microsoft and all the people on the Microsoft Excel development team for giving us Excel—a superb piece of software that is versatile and powerful. As a professional developer, I know how difficult it is to produce something so good, even though it is not perfect, but what is? Please Microsoft, receive my most profound respect and compliments. xix
16 ページ
Introduction This book was created to teach you how to use Visual Basic for Applications (VBA) to automate worksheet applications. It is a code book that was written to teach everyone, including people with zero experience in programming and people with decent programming knowledge, how to use and apply programming techniques to better interact with the users of your Microsoft Excel solutions in a more professional way. It uses some ready-made worksheet applications as practical examples of how you can produce solid, precise, and reliable worksheet applications based on VBA programming. First I’ll introduce you to the VBA environment and the language structure and show you some basic examples that will take you on a consistent journey through the Excel object model. All the proposed examples use VBA to teach you how to interact with the Excel object model and its many properties, methods, and events. As a basic strategy to teaching Excel VBA programming, this book uses the VBA Immediate window to first test each proposed Excel feature before showing how to use it in a code procedure. It is full of programming examples whose complexity grows from the book’s beginning to its end. This means it was written to be read one chapter at a time, with each chapter using the knowledge of previous chapters to provide a jump on your programmer skills. My teaching strategy is to show a UserForm interface or code procedure step-by-step and to comment on all its instructions, one at a time, to give you a better understanding of how the VBA code can use good programming techniques to produce the desired result, with clear, concise, and reusable code. What’s in the Book This book is divided into 11 chapters, each one approaching Excel VBA programming with a new complexity. To get a big picture of what you will find inside it, here is a summary of each chapter: • Chapter 1, “Understanding Visual Basic for Applications (VBA),” is intended to show you the VBA metaphor, including how to use VBA integrated development environment (IDE) and the VBA language structure. It is a basic chapter to show you a first approach to VBA and a programming language. • Chapter 2, “Programming the Microsoft Excel Application Object,” touches on the first programmable object in the Microsoft Excel object model hierarchy: the Application object, which represents the Microsoft Excel application window, with some of its main properties, methods, and events. In this chapter, you will learn about the FileDialog , GetOpenFileName, GetSaveAsFileName , and OnTime methods; when the Application object events fire; and how to use a Class module to watch and/or control whether a worksheet tab name can be changed, as a first approach to producing VBA objects. xxi
17 ページ
■ INTRODUCTION • Chapter 3, “Programming the Microsoft Excel Workbook Object,” talks about the second layer of the Microsoft Excel object model, which represents the Workbooks collection and contains all the open Workbook objects inside the main Excel window. You will learn about how and when the Workbook object events fire and will see the VBA UserForm and its controls, learning how to use the ListBox control and its interface (properties, methods, and events) to interact with the Workbook object using VBA code. • Chapter 4, “Programming the Microsoft Excel Worksheet Object,” touches on the third object level in the Excel object model hierarchy: the Worksheet object, which represents the sheet tab in a Excel workbook file. It also shows how its programmable interface is composed (it properties, methods, and events). In this chapter, you will use again a VBA UserForm to learn how to add, delete, copy, move, rename, sort, and change sheet tab visibility inside a workbook file. You will also learn the many ways (and the preferable one) to reference sheet tabs in the VBA code and how to control the “cascade events” phenomenon that happens with VBA objects. • Chapter 5, “Programming the Microsoft Excel Range Object,” talks about the deepest object inside the Excel object model hierarchy: the Range object, which can represent any number of cells inside a Worksheet object. Using another UserForm interface, you will learn how to programmatically define the cell addresses that compose any selected range, how to use VBA to name a range (using the Names collection), how to use the VBA Collection object, and how to produce a similar interface to the Excel Name Manager interface using VBA, improving the Excel interface by allowing you to change the range name visibility inside the workbook file. • Chapter 6, “Special Range Object Properties and Methods,” expands your knowledge about the Range object by covering the Cells and CurrentRegion properties and the End , OffSet , Find , AutoFilter , Sort Copy , and PasteSpecial methods, using again the UserForm approach. At the end of this chapter you are presented with the frmFindFoodItems UserForm that uses most of this knowledge to find food items in the USDA food table using different search criteria (by food item name or nutrient content). • Chapter 7, “Using Excel as a Database Repository,” presents you with a programmable approach to implementing a database storage system to store worksheet data as database records in unused worksheet rows, based on a data validation list cell and range names. This chapter uses all the knowledge gathered so far to produce a standard code module full of procedures that use module-level constant values to define the database parameters, allowing you to adapt it to any worksheet application design. • Chapter 8, “Creating and Setting a Worksheet Database Class,” expands the database code module to a Class module that uses range names to store the database properties inside unused worksheet rows. It defines the SheetDBEngine class interface, showing how to implement its properties, methods, and events. To allow you to easily use the SheetDBEngine class, this chapter also presents the frmDBPRoperties UserForm , which was produced as a database wizard, to help implement the database storage system on any worksheet application. xxii
18 ページ
■ INTRODUCTION • Chapter 9, “Exchanging Data Between Excel Applications,” answers important questions regarding how to programmatically update worksheet-based tables from where the worksheet application gathers its data. It teaches how to update the USDA worksheet to any version released by the ARS-USDA web page, using either a simple, silent procedural approach or a UserForm that reacts to the updating process, finding food item name inconsistencies between two releases of SRxx.accdb or SRxx. mdb Microsoft Access database nutrient tables. You will be also presented with the frmManage UserForm to allow mass operation on the worksheet database so you can delete and save recipe nutrient data and export and import recipes, using VBA automation. • Chapter 10, “Using the Windows API,” takes your VBA knowledge gathered so far in this book to the next level by teaching you how to understand and use the Windows application programmable interface (API), based on dynamic linked libraries (DLLs) and C++ procedures. The API can be declared and called from within a VBA project. In this chapter, you will learn how to implement a Timer class to create programmable timer objects and how to change the UserForm appearance by removing its title bar; adding a resizable border and minimize, restore, and maximize buttons; and adding transparency and creating a fade effect. You’ll also learn how to animate a UserForm when it loads and how to apply a skin effect to change the UserForm shape. • Chapter 11, “Producing a Personal Ribbon Using RibbonEditor.xlam,” uses the RibbonX VBA add-in developed by Andy Pope as free software to produce a personalized ribbon with tools that interact with your worksheet application, giving it a professional appearance. • The afterword is a brief testimony of the path used to write this book, with my insights about how I imagined it and created it step-by-step, with trial-and-error experimentation as I learned the Excel object model. This Book’s Special Features Programming Excel 2016 with VBA was designed to give you all the information you need to understand how to replicate a behavior, insert a formula, define an interface, and so on, without making you wade through ponderous explanations and interminable background. To make your life easier, this book includes various features and conventions to help you get the most of the book and Excel itself. • Steps: Throughout the book each Excel task is enumerated in step-by-step procedures. • Commands: I used the following style for Excel commands: Conditional Formatting button in Styles area on the Home tab of the ribbon (this mean you must click the Conditional Formatting button that you find in the Styles group of the Home tab). • Menus: To indicate that you click the File menu and then select Save, I use File ➤ Save. xxiii
19 ページ
■ INTRODUCTION • Functions: Excel worksheet functions appears in capital letters and are followed by parenthesis: IF( ) . When I list the arguments you can use with a function, they will appear in a bullet list of items, using the same order that they appear inside the function arguments list to explain the meaning of each one. • Rows, columns, cell address, ranges, and sheet names: Excel rows, columns, cell address, range names, and sheet names appear using an Arial font to detach them from the text. This book also uses the following box to call attention to important (or merely interesting) information: ■ Attention The Attention box presents asides that give you more information about the topic under discussion. These tidbits provide extra insights that give you a better understanding of the task at hand, offer complementary information about the issue that is being currently discussed, or even talk about an unexpected Excel behavior regarding a given task. Web Site Extras All the examples presented in this book are available at www.apress.com/9781484222041 as ZIP files for each book chapter. In addition to the example workbooks or worksheet applications, I’ll post any updates, corrections, and other useful information related to this book. Your Feedback Is (Very) Important! Before you continue reading, I would like to say that your opinion is important to me. I really don’t know how many of you will write to me to give any feedback, but I hope I can answer everyone and, whenever possible, resolve any questions or problems that arise. Since I have many other duties, sometimes it may take a little while so I can answer you, but I promise to do my best. Please feel free to write to me at the following e-mail: flaviomorgado@gmail.com . xxiv
20 ページ
CHAPTER 1 Understanding Visual Basic for Applications (VBA) This chapter will teach you how to program Microsoft Excel using Visual Basic for Applications code procedures. It will show you how to interact with the VBA integrated development environment (IDE), show how VBA programming code works, and give lots of examples. This introduction is for people who do not know anything about VBA and want to get started. If you already have some experience, you can skip to Chapter 2. You can obtain all the procedure code in this chapter by downloading the Chapter01.zip file from the book’s page at Apress.com, located at www.apress.com/9781484222041, or from http:// ProgrammingExcelWithVBA.4shared.com. What Is Macro Code? There was a time in computer usage that if you needed to accomplish a series of successive commands in software, you could use the appropriate program syntax to type the menu commands to follow, one command per row, and then make the program execute them, row by row. In the DOS age of the early 1980s, the spreadsheet program called Lotus 123 called this method a macro, and since then, every time a program tries to allow the user to implement such a resource to execute any desired number of successive menu commands, the convention is to say that the program is following a macro code. The first versions of Windows and all Microsoft Office applications (including Microsoft Excel) could implement this sort of automation by recording successive series of actions performed on the interface using the keyboard or the mouse. Until 1994 when Microsoft Excel was on version 3.0, each Microsoft Office application had its own way to store its macro codes, meaning that these codes were not interchangeable between Office applications. In other words, Word has its own set of commands, PowerPoint had another set, and Excel had yet another one. At this time you needed to be very specialized to read and understand each Microsoft Office application macro code. Everything changed in 1995, when Microsoft released Microsoft Visual Basic 4; its IDE used the VBA language and made it part of all Microsoft Office 6 applications. From this time on, all Microsoft Office applications were capable of being programmed using a single language, allowing all applications to interact with each other. Even though this code was produced with VBA as a true programming language, the term macro code stuck to this sort of application automation and continues to be used today. Electronic supplementary material The online version of this chapter (doi:10.1007/978-1-4842-2205-8_1) contains supplementary material, which is available to authorized users. © Flavio Morgado 2016 1 F. Morgado, Programming Excel with VBA, DOI 10.1007/978-1-4842-2205-8_1
コメント (0)
まだコメントはありません。最初のコメントを書きましょう！