Microsoft Excel VBA and Macros
- Length: 640 pages
- Edition: 1
- Language: English
- Publisher: Microsoft Press
- Publication Date: 2022-04-13
- ISBN-10: 0137521529
- ISBN-13: 9780137521524
- Sales Rank: #183353 (See Top 100 Books)
Renowned Excel experts Bill Jelen (MrExcel) and Tracy Syrstad help you automate Excel tasks to get more done, faster than ever.
Use this guide to automate virtually any routine Excel task: save yourself hours, days, maybe even weeks. Make Excel do things you thought were impossible, discover macro techniques you won’t find anywhere else, and create automated reports that are amazingly powerful. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable; capture data from anywhere, and use it anywhere; and automate the best new features in Microsoft Excel. You’ll find simple, step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and complete, easy-to-adapt solutions.
By reading this book, you will:
- Quickly master Excel macro development
- Work more efficiently with ranges, cells, and formulas
- Generate automated reports and quickly adapt them for new requirements
- Learn to automate PivotTables to summarize, analyze, explore, and present data
- Leverage more of Excel VBA’s power with classes, collections, and Advanced Filter
- Make the most of User-Defined Functions (UDFs), including new LAMBDA functions
- Build sophisticated UserForm custom dialogs to collect data from other Excel users
- Integrate data from the Internet, Access and SQL Server databases, and other sources
- Automatically generate charts, visualizations, sparklines, and Word documents
- Extend Excel’s power by controlling other Office programs and Windows itself
- Create Office Add-ins to expand worksheet functionality or streamline retrieval of external information
- Get started creating brand-new TypeScript applications for Excel Online
About This Book
- For everyone who wants to get more done with Microsoft Excel in less time
- For business and financial professionals, entrepreneurs, students, and others who need to efficiently manage and analyze data
Cover Page Title Page Copyright Page Pearson’s Commitment to Diversity, Equity, and Inclusion Dedication Contents at a Glance Contents Acknowledgments About the Authors Introduction In this Introduction, you will: Is TypeScript a threat to VBA? What is in this book? Versions of Excel Special elements and typographical conventions About the companion content Errata, updates, and book support Stay in touch Chapter 1. Unleashing the power of Excel with VBA Barriers to entry Knowing your tools: The Developer tab Understanding which file types allow macros Macro security Overview of recording, storing, and running a macro Running a macro Understanding the VB Editor Understanding shortcomings of the macro recorder Next steps Chapter 2. This sounds like BASIC, so why doesn’t it look familiar? Understanding the parts of VBA “speech” VBA is not really hard Examining recorded macro code: Using the VB Editor and Help Using debugging tools to figure out recorded code Object Browser: The ultimate reference Seven tips for cleaning up recorded code Next steps Chapter 3. Referring to ranges The Range object Referencing ranges in other sheets Referencing a range relative to another range Using the Cells property to select a range Using the Offset property to refer to a range Using the Resize property to change the size of a range Using the Columns and Rows properties to specify a range Using the Union method to join multiple ranges Using the Intersect method to create a new range from overlapping ranges Using the IsEmpty function to check whether a cell is empty Using the CurrentRegion property to select a data range Using the Areas collection to return a noncontiguous range Referencing tables Next steps Chapter 4. Looping and flow control For...Next loops Do loops The VBA loop: For Each Flow control: Using If...Then...Else and Select Case Next steps Chapter 5. R1C1-style formulas Toggling to R1C1-style references Witnessing the miracle of Excel formulas Understanding the R1C1 reference style Next steps Chapter 6. Creating and manipulating names in VBA Global versus local names Adding names Deleting names Adding comments Types of names Hiding names Checking for the existence of a name Next steps Chapter 7. Event programming Levels of events Using events Workbook events Worksheet events Chart events Application-level events Next steps Chapter 8. Arrays Declaring an array Declaring a multidimensional array Filling an array Retrieving data from an array Using arrays to speed up code Using dynamic arrays Passing an array Next steps Chapter 9. Creating classes and collections Inserting a class module Trapping application and embedded chart events Creating a custom object Using a custom object Using collections Using dictionaries Using user-defined types to create custom properties Next steps Chapter 10. Userforms: An introduction Input boxes Message boxes Creating a userform Calling and hiding a userform Programming userforms Programming controls Using basic form controls Verifying field entry Illegal window closing Getting a file name Next steps Chapter 11. Data mining with Advanced Filter Replacing a loop with AutoFilter Advanced Filter—easier in VBA than in Excel Using Advanced Filter to extract a unique list of values Using Advanced Filter with criteria ranges Using Filter In Place in Advanced Filter The real workhorse: xlFilterCopy with all records rather than unique records only Next steps Chapter 12. Using VBA to create pivot tables Understanding how pivot tables evolved over various Excel versions Building a pivot table in Excel VBA Using advanced pivot table features Filtering a data set Formatting the intersection of values in a pivot table Using the Data Model in Excel Using other pivot table features Comparing VBA to TypeScript Next steps Chapter 13. Excel power File operations Combining and separating workbooks Placing a chart in a cell note Tracking user changes Techniques for VBA pros Next steps Chapter 14. Sample user-defined functions Creating user-defined functions Sharing UDFs Useful custom Excel functions Creating LAMBDA functions Next steps Chapter 15. Creating charts Using .AddChart2 to create a chart Understanding chart styles Formatting a chart Creating a combo chart Creating map charts Creating waterfall charts Exporting a chart as a graphic Considering backward compatibility Next steps Chapter 16. Data visualizations and conditional formatting VBA methods and properties for data visualizations Adding data bars to a range Adding color scales to a range Adding icon sets to a range Using visualization tricks Using other conditional formatting methods Next steps Chapter 17. Dashboarding with sparklines in Excel Creating sparklines Scaling sparklines Formatting sparklines Creating a dashboard Next steps Chapter 18. Reading from the web using M and VBA Get credentials for accessing an API Build a query in Power Query using the M language to retrieve data from the web for one specific value Generalizing the queries using VBA Using global variables and loops in M Application.OnTime to periodically analyze data Next steps Chapter 19. Text file processing Importing from text files Writing text files Next steps Chapter 20. Automating Word Using early binding to reference a Word object Using late binding to reference a Word object Using the New keyword to reference the Word application Using the CreateObject function to create a new instance of an object Using the GetObject function to reference an existing instance of Word Using constant values Understanding Word’s objects Controlling form fields in Word Next steps Chapter 21. Using Access as a back end to enhance multiuser access to data ADO versus DAO The tools of ADO Adding a record to a database Retrieving records from a database Updating an existing record Deleting records via ADO Summarizing records via ADO Other utilities via ADO SQL Server examples Next steps Chapter 22. Advanced userform techniques Using the UserForm toolbar in the design of controls on userforms More userform controls Controls and collections Modeless userforms Using hyperlinks in userforms Adding controls at runtime Adding help to a userform Creating transparent forms Next steps Chapter 23. The Windows Application Programming Interface (API) Understanding an API declaration Using an API declaration Making 32-bit- and 64-bit-compatible API declarations API function examples Next steps Chapter 24. Handling errors What happens when an error occurs? Basic error handling with the On Error GoTo syntax Generic error handlers Training your clients Errors that won’t show up in Debug mode Errors while developing versus errors months later The ills of protecting code More problems with passwords Errors caused by different versions Next steps Chapter 25. Customizing the ribbon to run macros Where to add code: The customui folder and file Creating a tab and a group Adding a control to a ribbon Accessing the file structure Understanding the RELS file Renaming an Excel file and opening a workbook Using images on buttons Troubleshooting error messages Other ways to run a macro Next steps Chapter 26. Creating Excel add-ins Characteristics of standard add-ins Converting an Excel workbook to an add-in Having a client install an add-in Add-in security Closing add-ins Removing add-ins Using a hidden workbook as an alternative to an add-in Next steps Chapter 27. An introduction to creating Office add-ins Creating your first Office add-in—Hello World Adding interactivity to an Office add-in A basic introduction to HTML Using XML to define an Office add-in Using JavaScript to add interactivity to an Office add-in Next steps Chapter 28. What’s new in Excel 365 and what’s changed Office 365 subscription versus Excel 2021 perpetual If it has changed in the front end, it has changed in VBA Learning the new objects and methods Compatibility mode Next steps Index Code Snippets
Donate to keep this site alive
1. Disable the AdBlock plugin. Otherwise, you may not get any links.
2. Solve the CAPTCHA.
3. Click download link.
4. Lead to download server to download.