Transcription
Excel 2007 VBAMacro Programming
About the AuthorRichard Shepherd has worked for many years for major banks and corporations in theUnited Kingdom writing spreadsheet macros to solve specific problems. He has worked forNational Grid plc (electricity distribution), Hertz Car Leasing, NatWest plc (retail banking),Schroders plc (fund management), BNP Paribas (investment banking), Lloyds TSB plc(corporate banking), The Royal Bank of Scotland plc (investment banking), and the BritishNational Health Service.He has developed advanced spreadsheets and macros for budgeting, business planning,and profit and loss reporting. He originally started working with Lotus 123 spreadsheetsbut moved some years ago to Microsoft Excel and Microsoft Access. He qualified as anaccountant with the Chartered Association of Certified Accountants in 1976 and has beena Fellow since 1981.He can be contacted at tollside@yahoo.com, but he cannot promise to answer everye-mail!About the Technical EditorTodd Meister has been developing using Microsoft technologies for over ten years. He’sbeen a technical editor on over 50 titles ranging from SQL Server to the .NET Framework.Besides technically editing titles, he is an assistant director for Computing Services at BallState University in Muncie, Indiana. He lives in central Indiana with his wife, Kimberly, andtheir four remarkable children.
Excel 2007 VBAMacro ProgrammingRichard ShepherdNew York Chicago San FranciscoLisbon London Madrid Mexico City MilanNew Delhi San Juan Seoul Singapore Sydney Toronto
Copyright 2010 by The McGraw-Hill Companies. All rights reserved. Except as permitted under the United States Copyright Act of 1976,no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, withoutthe prior written permission of the publisher.ISBN: 978-0-07-162701-6MHID: 0-07-162701-4The material in this eBook also appears in the print version of this title: ISBN: 978-0-07-162700-9, MHID: 0-07-162700-6.All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of atrademarked name, we use names in an editorial fashion only, and to the benefit of the trademark owner, with no intention of infringement ofthe trademark. Where such designations appear in this book, they have been printed with initial caps.McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in corporate training programs. To contact a representative please e-mail us at bulksales@mcgraw-hill.com.Information has been obtained by McGraw-Hill from sources believed to be reliable. However, because of the possibility of human ormechanical error by our sources, McGraw-Hill, or others, McGraw-Hill does not guarantee the accuracy, adequacy, or completeness of anyinformation and is not responsible for any errors or omissions or the results obtained from the use of such information.TERMS OF USEThis is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGraw-Hill”) and its licensors reserve all rights in and to the work. Useof this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to store and retrieve one copy of thework, you may not decompile, disassemble, reverse engineer, reproduce, modify, create derivative works based upon, transmit, distribute, disseminate, sell, publish or sublicense the work or any part of it without McGraw-Hill’s prior consent. You may use the work for your own noncommercial and personal use; any other use of the work is strictly prohibited. Your right to use the work may be terminated if you fail to comply with these terms.THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR WARRANTIES AS TO THEACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED FROM USING THE WORK, INCLUDING ANYINFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA HYPERLINK OR OTHERWISE, AND EXPRESSLYDISCLAIM ANY WARRANTY, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. McGraw-Hill and its licensors do not warrant or guarantee that thefunctions contained in the work will meet your requirements or that its operation will be uninterrupted or error free. Neither McGraw-Hill norits licensors shall be liable to you or anyone else for any inaccuracy, error or omission, regardless of cause, in the work or for any damagesresulting therefrom. McGraw-Hill has no responsibility for the content of any information accessed through the work. Under no circumstancesshall McGraw-Hill and/or its licensors be liable for any indirect, incidental, special, punitive, consequential or similar damages that result fromthe use of or inability to use the work, even if any of them has been advised of the possibility of such damages. This limitation of liability shallapply to any claim or cause whatsoever whether such claim or cause arises in contract, tort or otherwise.
To my wife, Elaine, and my son, Alexander.
This page intentionally left blank
Contents at a GlancePart IProgramming in Excel VBAChapter 1The Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3Chapter 2Variables, Arrays, Constants, and Data Types . . . . . . . . . . . . . . .13Chapter 3Modules, Functions, and Subroutines . . . . . . . . . . . . . . . . . . . . .27Chapter 4Programming Basics: Decisions and Looping . . . . . . . . . . . . . . . . .35Chapter 5Strings and Functions and Message Boxes. . . . . . . . . . . . . . . . .45Chapter 6Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .71Chapter 7Debugging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .81Chapter 8Errors and the Error Function . . . . . . . . . . . . . . . . . . . . . . . . .91Chapter 9Dialogs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .97Chapter 10Common Dialog Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113Chapter 11Command Bars and Buttons . . . . . . . . . . . . . . . . . . . . . . . . . . 123Part IIObject ModelsChapter 12The Excel Object Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133Chapter 13The Excel Object Model—Main Objects . . . . . . . . . . . . . . . . . . . 151Chapter 14Using Excel to Interact with Other Office Programs . . . . . . . . . . . . 171vii
viiiExcel 2007 VBA Macro ProgrammingPart IIIAdvanced Techniques in Excel VBAChapter 15Charts and Graphs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181Chapter 16Working with Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . 187Chapter 17API Calls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193Chapter 18Class Modules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205Chapter 19Animation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213Chapter 20Working with XML Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217Chapter 21The Ribbon . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233Chapter 22Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249Part IVVBA in ActionChapter 23Converting Labels to Numbers and Numbers to Labels . . . . . . . . . . . 261Chapter 24Transposing a Range of Cells . . . . . . . . . . . . . . . . . . . . . . . . . 265Chapter 25Adding Formula Details into Comments . . . . . . . . . . . . . . . . . . . 269Chapter 26Calculating a Range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273Chapter 27Reversing a Label . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275Chapter 28Who Created the Workbook? . . . . . . . . . . . . . . . . . . . . . . . . . 277Chapter 29Evaluating a Cell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281Chapter 30Sorting Worksheets into Alphabetical OrderChapter 31Replacing Characters in a String . . . . . . . . . . . . . . . . . . . . . . . 287Chapter 32Timed Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291Chapter 33Auto-Totaling a Matrix of Numbers . . . . . . . . . . . . . . . . . . . . . 293Chapter 34Absolute and Relative Formulas . . . . . . . . . . . . . . . . . . . . . . . 297Chapter 35Coloring Alternate Rows and Columns of the Spreadsheet . . . . . . . . 303Chapter 36Coloring Cells Containing Formulas . . . . . . . . . . . . . . . . . . . . . . 309. . . . . . . . . . . . . . . . 285
Contents at a GlanceChapter 37Summing Cells by Reference to a Master Cell . . . . . . . . . . . . . . . . 313Chapter 38Globally Changing a Range of Values . . . . . . . . . . . . . . . . . . . . 317Chapter 39Displaying Hidden Sheets Without a Password . . . . . . . . . . . . . . . 321Chapter 40Searching Multiple Sheets and Workbooks . . . . . . . . . . . . . . . . . 327Chapter 41Brighten Up Your Comments . . . . . . . . . . . . . . . . . . . . . . . . . . 337Chapter 42Importing a CSV File Containing Leading Zeros . . . . . . . . . . . . . . . 347Chapter 43Working with Shapes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353Chapter 44Turning Your VBA Code into an Add-In . . . . . . . . . . . . . . . . . . . . 357AppendixASCII Character Codes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373ix
This page intentionally left blank
ContentsAcknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Part IChapter 1Chapter 2xixxxiProgramming in Excel VBAThe Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3Exploring the Visual Basic Editor in Excel . . . . .VBA Project Explorer and Code Windows .Your First Excel VBA Macro . . . . . . . .More Exploring of the VBA Project WindowSaving Off Your Macro . . . . . . . . . . . . . .Enabling the Developer Item in the Excel Menu . .357101112Variables, Arrays, Constants, and Data Types . . . . . . . . . . . . . . .13Variables . . . . . . . . . . . . . . . . . .Implicit Declaration . . . . . . . . .Explicit Declaration . . . . . . . . .Scope and Lifetime of Variables . . .Local Variables . . . . . . . . . . .Module-Level Variables . . . . . . .Global Variables . . . . . . . . . .Name Conflicts and Shadowing . . .Static Variables . . . . . . . . . . .Data Types . . . . . . . . . . . . . . . . .Variant . . . . . . . . . . . . . . .Date/Time Values Stored in VariantsEmpty Value . . . . . . . . . . . .Null Value . . . . . . . . . . . . .Other Data Types . . . . . . . . . .131415151616171717181819192020.xi
xiiExcel 2007 VBA Macro ProgrammingVBA Data Types . . . . . . . .Numeric Types . . . . .String Types . . . . . .Arrays . . . . . . . . . . . . .Multidimensional ArraysDynamic Arrays . . . .User-Defined Types . . . . . . .Constants . . . . . . . . . . .Reserved Words . . . . . . . .Chapter 3Chapter 4Chapter 5.202021222324252526Modules, Functions, and Subroutines . . . . . . . . . . . . . . . . . . . . .27Modules . . . . . . . . . . . . . . . . . . . . .The Difference Between Subroutines and FunctionsWriting a Simple Subroutine . . . . . . . . . . .Writing a Simple Function . . . . . . . . . . . .Public and Private Functions and Subroutines . . .Argument Data Types . . . . . . . . . . . . . . .Optional Arguments . . . . . . . . . . . . . . .Passing Arguments by Value . . . . . . . . . . .2728293032333434Programming Basics: Decisions and Looping . . . . . . . . . . . . . . . . .35Decisions . . . . . . . . . . . . . . .Multiple Conditional StatementsSelect Case Statements . . . .Looping . . . . . . . . . . . . . . .For.Next Loops . . . . . . . .For Each Loops . . . . . . . .Do Until Loops . . . . . . . .While.Wend Loops . . . . . .Early Exit of Loops . . . . . .363838394041414242. . . . . . . . . . . . . . . . .45.4546464748494949.Strings and Functions and Message BoxesStrings . . . . . . . . . . . . . . . . . .Concatenation . . . . . . . . . .Splitting Strings . . . . . . . . . .Changing the Appearance of StringsSearching Strings . . . . . . . . .Functions . . . . . . . . . . . . . . . . .Len . . . . . . . . . . . . . . .Abs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
xiiiContentsInt . . . . . . .Sqr . . . . . . .Asc . . . . . . .Chr . . . . . . .Conversion Functions . .CStr . . . . . .CInt . . . . . .CLng . . . . . .CDbl . . . . . .Val . . . . . . .Format Function . . . .Date and Time FunctionsNow . . . . . .Date . . . . . .Time . . . . . .DateAdd . . . .DateDiff . . . .DatePart . . . .DateSerial . . .DateValue . . .Day . . . . . .Hour . . . . . .Month . . . . .Second . . . . .Minute . . . . .Year . . . . . .Weekday . . . .SendKeys Command . .Message Boxes . . . . .Chapter 6161626266Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .71Arithmetic Operators* Operator . Operator .– Operator ./ Operator .\ Operator . Operator .Mod Operator7272727373737474.
xivExcel 2007 VBA Macro ProgrammingComparison OperatorsConcatenation OperatorLogical Operators . . .And Operator .Not Operator .Or Operator .Xor Operator .Other Operators . . .Is Operator . .Like OperatorChapter 7Chapter 8Chapter 9. . . . . . . . . .74757575767678797979Debugging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .81Types of Errors . . . . . . . . . . . . . . . . .Compile Errors . . . . . . . . . . . . .Runtime Errors . . . . . . . . . . . . .Logic Errors . . . . . . . . . . . . . .Design Time, Runtime, and Break Mode . . . .Breakpoints . . . . . . . . . . . . . . . . . .Using Stop Statements . . . . . . . . . . . . .Running Selected Parts of Your Code . . . . . .Single Stepping . . . . . . . . . . . . .Procedure Stepping . . . . . . . . . . .Call Stack Dialog . . . . . . . . . . . .The Debug Window . . . . . . . . . . . . . . .Events That Can Cause Problems When DebuggingMouse Down . . . . . . . . . . . . . .Key Down . . . . . . . . . . . . . . .Got Focus/Lost Focus . . . . . . . . . .Using Message Boxes in Debugging . . . . . . .Avoiding Bugs . . . . . . . . . . . . . . . . .818182828284848485858586888888888890Errors and the Error Function . . . . . . . . . . . . . . . . . . . . . . . . .91The Resume Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Implications of Error Trapping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Generating Your Own Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .939494Dialogs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .97Viewing Your Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Displaying Your Form in Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .101103.
ContentsPopulating Your Form .Default Toolbox ControlsLabel . . . . . .TextBox . . . .ComboBox . . .ListBox . . . . .CheckBox . . .OptionButton . .ToggleButton . .Frame . . . . .CommandButtonTabStrip . . . .MultiPage . . .ScrollBar . . . .SpinButton . . .Image . . . . .RefEdit . . . . .Chapter 10.Chapter 12114115116117118119120Command Bars and Buttons . . . . . . . . . . . . . . . . . . . . . . . . . . 123Command Bars . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Command Buttons . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Part 112Common Dialog Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113Using the Common Dialog ControlThe Open Dialog . . . .The Save As Dialog . . .The Color Dialog . . . .The Font Dialog . . . .The Print Dialog . . . .Default Dialogs . . . . . . . . .Chapter 11.124128Object ModelsThe Excel Object Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133Properties and Methods Explained . . . . .Manipulating Properties . . . . . .Calling Methods . . . . . . . . . .Using the Object Browser . . . . . . . . . .Communicating with the Spreadsheet.134136138143144xv
xviExcel 2007 VBA Macro ProgrammingHierarchy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Recording Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Chapter 13The Excel Object Model—Main Objects . . . . . . . . . . . . . . . . . . . 151Application Object . . . . . . . . . . . . . . .Main Properties, Methods, and CollectionsWorkbook Object . . . . . . . . . . . . . . . .Main Properties, Methods, and CollectionsWindows Object . . . . . . . . . . . . . . . .Main Properties, Methods, and CollectionsWorksheet Object . . . . . . . . . . . . . . .Main Properties, Methods, and CollectionsRange Object . . . . . . . . . . . . . . . . . .Main Properties, Methods, and CollectionsChapter 14.Chapter 15Charts and Graphs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181184185185Working with Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . 187ODBC Links . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Using ADO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Chapter 17174176Advanced Techniques in Excel VBAWorking with the Series Collection Object . . . . . . . . . . . . . . . . . . . . . . . . . .Exporting a Chart as a Picture File . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Using Macro Recording . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Chapter 16151152156157158159163163165165Using Excel to Interact with Other Office Programs . . . . . . . . . . . . 171Driving Microsoft Outlook . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Driving Excel from Other Office Programs . . . . . . . . . . . . . . . . . . . . . . . . .Part III146147188190API Calls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193What Is an API Call? . . . . . . . . . . . .Using an API Call . . . . . . . . . . . . . .Getting Disk Space . . . . . . . . .Reading from and Writing to INI FilesRead Keyboard Activity . . . . . . .Play Multimedia Sounds . . . . . .193194194195197202
ContentsChapter 18Class Modules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205Inserting a Class Module . .Creating an Object . . . . .Creating a Collection . . . .Using the PNames Collection.206206208210Chapter 19Animation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213Chapter 20Working with XML Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217The XML Document . . . . . . . . . . .How Does XML Relate to VBA? . . . . .XML Maps . . . . . . . . . . . . . . .Customizing an XML Map . . . . . . . .Importing and Exporting Data . . . . .Manipulating XML Files ProgrammaticallyChapter 21.218221221226228229. .234234238239239242Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249Creating a Simple Pivot Table fromthe Front End of Excel . . . . .Creating a Pivot Table Using VBA . .Using VBA with a Pivot Table . . . .Advanced VBA for Pivot Tables . . .Points to Bear in Mind on Pivot TablesPart IV.The Ribbon . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233Creating a Ribbon Customization . . . . .Using the Custom UI Editor . . . . . . . .Creating Code for Your Custom Buttons . .Images . . . . . . . . . . . . . . . . . .How Can You Use VBA Code with the RibbonMore on the Ribbon . . . . . . . . . . .Chapter 22.249253254257258VBA in ActionChapter 23Converting Labels to Numbers and Numbers to Labels . . . . . . . . . . . 261Chapter 24Transposing a Range of Cells . . . . . . . . . . . . . . . . . . . . . . . . . 265Chapter 25Adding Formula Details into Comments . . . . . . . . . . . . . . . . . . . 269Chapter 26Calculating a Range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273xvii
xviiiExcel 2007 VBA Macro ProgrammingChapter 27Reversing a Label . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275Chapter 28Who Created the Workbook? . . . . . . . . . . . . . . . . . . . . . . . . . 277Chapter 29Evaluating a Cell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281Chapter 30Sorting Worksheets into Alphabetical OrderChapter 31Replacing Characters in a String . . . . . . . . . . . . . . . . . . . . . . . 287Chapter 32Timed Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291Chapter 33Auto-Totaling a Matrix of Numbers . . . . . . . . . . . . . . . . . . . . . 293Chapter 34Absolute and Relative Formulas . . . . . . . . . . . . . . . . . . . . . . . 297Chapter 35Coloring Alternate Rows and Columns of the Spreadsheet . . . . . . . . 303Chapter 36Coloring Cells Containing Formulas . . . . . . . . . . . . . . . . . . . . . . 309Chapter 37Summing Cells by Reference to a Master Cell . . . . . . . . . . . . . . . . 313Chapter 38Globally Changing a Range of Values . . . . . . . . . . . . . . . . . . . . 317Chapter 39Displaying Hidden Sheets Without a Password . . . . . . . . . . . . . . . 321Chapter 40Searching Multiple Sheets and Workbooks . . . . . . . . . . . . . . . . . 327Chapter 41Brighten Up Your Comments . . . . . . . . . . . . . . . . . . . . . . . . . . 337. . . . . . . . . . . . . . . . 285Test It Out . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .344Chapter 42Importing a CSV File Containing Leading Zeros . . . . . . . . . . . . . . . 347Chapter 43Working with Shapes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353Chapter 44Turning Your VBA Code into an Add-In . . . . . . . . . . . . . . . . . . . . 357AppendixASCII Character Codes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373
AcknowledgmentsWith grateful thanks to all the people at McGraw-Hill who have made this bookhappen. Special thanks to Joya Anthony and Vipra Fauzdar, who havemanaged this project and dealt with the vast numbers of e-mails this hasgenerated. Others who deserve a special mention for all the help they have given to theproject include Wendy Rinaldi (who asked me to write the Excel 2007 version), ToddMeister, and Melinda Lytle.Finally a big thank you to Elaine and Alexander for all their support on this project.xix
This page intentionally left blank
IntroductionSpreadsheet macro programming has changed enormously within the last 12 years,when we went from text-based macros to VBA. There was also a major change inOffice 97, when macros went to VBA worksheet modules in a separate environmentaccessed via the Visual Basic Editor. It used to be fairly basic: code was entered onto aspecially designated worksheet. Although the language was fairly powerful in its own right, itwas not a structured language and could certainly not be described as object-oriented. Thenumber of commands was limited, and a fair amount of ingenuity was required to do certaintasks. The main advantage of it was that it was fairly easy to learn and understand; manyprogrammers cut their teeth by initially writing spreadsheet macros.However, it was also quite difficult to document and for other programmers to understand,since code could be placed anywhere on the macro spreadsheet, and blocks of code were onlydefined by a range name. This meant that code could be all over the place and following theflow of the program around the macro spreadsheet could become very complicated. Ofcourse, professional developers could comment their code by using a cell to the right andcould organize their code and its placement on the worksheet. However, it was still acomplicated and unstructured process.If anyone other than the original author examined the code, it could take days to find outexactly how it worked and what it was doing. Commercial companies frequently found thatwhen the author of a complicated macro left the company, that macro had to be rewrittenfrom scratch because of the time involved in assessing what it was doing.Since the advent of Excel 5.0, Microsoft has introduced a new programming languagecalled Visual Basic for Applications (VBA). VBA is a more intuitive and robust programminglanguage using an object-oriented design. It has a great deal of similarity with its older andlarger cousin, Visual Basic (VB). Once you learn VBA, you will have a fair understanding ofhow Visual Basic itself operates.VBA is extremely different from the old macro language, and if the older language is whatyou are used to, it will involve a t
Part III Advanced Techniques in Excel VBA Chapter 15 Charts and Graphs. 181 Chapter 16 Working with Databases. 187 Chapter 17 API Calls. 193 Chapter 18 Class Modules. 205 Chapter 19 Animation. 213 Chapter 20 Working with XML Files. 217 Chapter 21 The Ribbon. 233 Chapter 22 Pivot Tables. 249 Part IV
![[MS-VBAL]: VBA Language Specification - Framework](/img/22/ms-vbal.jpg)
