Microsoft Excel 2019 VBA and Macros, First Edition

Microsoft Excel 2019 VBA and Macros, First Edition

by Bill Jelen, Tracy Syrstad
Released December 2018
Publisher(s): Microsoft Press
ISBN: 9781509307203

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Renowned Excel experts Bill Jelen (MrExcel) and Tracy Syrstad explain how to build more powerful, reliable, and efficient Excel spreadsheets.

 

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 Excel 2019 and Excel in Office 365. 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 pivot tables to summarize, analyze, explore, and present data
  • Use custom dialog boxes to collect data from others using Excel
  • Improve the reliability and resiliency of your macros
  • Integrate data from the internet, Access databases, and other sources
  • Automatically generate charts, visualizations, sparklines, and Word documents
  • Create powerful solutions with classes, collections, and custom functions
  • Solve sophisticated business analysis problems more rapidly

 

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

Table of contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Dedication Page
  5. Contents at a Glance
  6. Contents
  7. Acknowledgments
  8. About the Authors
  9. Introduction
  10. Chapter 1 Unleashing the power of Excel with VBA
    1. Barriers to entry
    2. Knowing your tools: The Developer tab
    3. Understanding which file types allow macros
    4. Macro security
    5. Overview of recording, storing, and running a macro
    6. Running a macro
    7. Understanding the VB Editor
    8. Understanding shortcomings of the macro recorder
    9. Next steps
  11. Chapter 2 This sounds like BASIC, so why doesn’t it look familiar?
    1. Understanding the parts of VBA “speech”
    2. VBA is not really hard
    3. Examining recorded macro code: Using the VB Editor and Help
    4. Using debugging tools to figure out recorded code
    5. Object Browser: The ultimate reference
    6. Seven tips for cleaning up recorded code
    7. Next steps
  12. Chapter 3 Referring to ranges
    1. The Range object
    2. Referencing ranges in other sheets
    3. Referencing a range relative to another range
    4. Using the Cells property to select a range
    5. Using the Offset property to refer to a range
    6. Using the Resize property to change the size of a range
    7. Using the Columns and Rows properties to specify a range
    8. Using the Union method to join multiple ranges
    9. Using the Intersect method to create a new range from overlapping ranges
    10. Using the IsEmpty function to check whether a cell is empty
    11. Using the CurrentRegion property to select a data range
    12. Using the Areas collection to return a noncontiguous range
    13. Referencing tables
    14. Next steps
  13. Chapter 4 Looping and flow control
    1. For...Next loops
    2. Do loops
    3. The VBA loop: For Each
    4. Flow control: Using If...Then...Else and Select Case
    5. Next steps
  14. Chapter 5 R1C1-style formulas
    1. Toggling to R1C1-style references
    2. Witnessing the miracle of Excel formulas
    3. Understanding the R1C1 reference style
    4. Using R1C1 formulas with array formulas
    5. Next steps
  15. Chapter 6 Creating and manipulating names in VBA
    1. Global versus local names
    2. Adding names
    3. Deleting names
    4. Adding comments
    5. Types of names
    6. Hiding names
    7. Checking for the existence of a name
    8. Next steps
  16. Chapter 7 Event programming
    1. Levels of events
    2. Using events
    3. Workbook events
    4. Worksheet events
    5. Chart events
    6. Application-level events
    7. Next steps
  17. Chapter 8 Arrays
    1. Declaring an array
    2. Declaring a multidimensional array
    3. Filling an array
    4. Retrieving data from an array
    5. Using arrays to speed up code
    6. Using dynamic arrays
    7. Passing an array
    8. Next steps
  18. Chapter 9 Creating classes and collections
    1. Inserting a class module
    2. Trapping application and embedded chart events
    3. Creating a custom object
    4. Using a custom object
    5. Using collections
    6. Using dictionaries
    7. Using user-defined types to create custom properties
    8. Next steps
  19. Chapter 10 Userforms: An introduction
    1. Input boxes
    2. Message boxes
    3. Creating a userform
    4. Calling and hiding a userform
    5. Programming userforms
    6. Programming controls
    7. Using basic form controls
    8. Verifying field entry
    9. Illegal window closing
    10. Getting a file name
    11. Next steps
  20. Chapter 11 Data mining with Advanced Filter
    1. Replacing a loop with AutoFilter
    2. Advanced Filter—easier in VBA than in Excel
    3. Using Advanced Filter to extract a unique list of values
    4. Using Advanced Filter with criteria ranges
    5. Using filter in place in Advanced Filter
    6. The real workhorse: xlFilterCopy with all records rather than unique records only
    7. Next steps
  21. Chapter 12 Using VBA to create pivot tables
    1. Understanding how pivot tables evolved over various Excel versions
    2. While building a pivot table in Excel VBA
    3. Using advanced pivot table features
    4. Filtering a data set
    5. Using the Data Model in Excel 2019
    6. Using other pivot table features
    7. Next steps
  22. Chapter 13 Excel power
    1. File operations
    2. Combining and separating workbooks
    3. Working with cell comments
    4. Tracking user changes
    5. Techniques for VBA pros
    6. Next steps
  23. Chapter 14 Sample user-defined functions
    1. Creating user-defined functions
    2. Sharing UDFs
    3. Useful custom Excel functions
    4. Next steps
  24. Chapter 15 Creating charts
    1. Using .AddChart2 to create a chart
    2. Understanding chart styles
    3. Formatting a chart
    4. Creating a combo chart
    5. Creating map charts
    6. Creating waterfall charts
    7. Exporting a chart as a graphic
    8. Considering backward compatibility
    9. Next steps
  25. Chapter 16 Data visualizations and conditional formatting
    1. VBA methods and properties for data visualizations
    2. Adding data bars to a range
    3. Adding color scales to a range
    4. Adding icon sets to a range
    5. Using visualization tricks
    6. Using other conditional formatting methods
    7. Next steps
  26. Chapter 17 Dashboarding with sparklines in Excel 2019
    1. Creating sparklines
    2. Scaling sparklines
    3. Formatting sparklines
    4. Creating a dashboard
    5. Next steps
  27. Chapter 18 Reading from and writing to the web
    1. Getting data from the web
    2. Using Application.OnTime to periodically analyze data
    3. Publishing data to a web page
    4. Next steps
  28. Chapter 19 Text file processing
    1. Importing from text files
    2. Writing Text Files
    3. Next steps
  29. Chapter 20 Automating Word
    1. Using early binding to reference a Word object
    2. Using late binding to reference a Word object
    3. Using the New keyword to reference the Word application
    4. Using the CreateObject function to create a new instance of an object
    5. Using the GetObject function to reference an existing instance of Word
    6. Using constant values
    7. Understanding Word’s objects
    8. Controlling form fields in Word
    9. Next steps
  30. Chapter 21 Using Access as a back end to enhance multiuser access to data
    1. ADO versus DAOs
    2. The tools of ADO
    3. Adding a record to a database
    4. Retrieving records from a database
    5. Updating an existing record
    6. Deleting records via ADO
    7. Summarizing records via ADO
    8. Other utilities via ADO
    9. SQL Server examples
    10. Next steps
  31. Chapter 22 Advanced userform techniques
    1. Using the UserForm toolbar in the design of controls on userforms
    2. More userform controls
    3. Controls and collections
    4. Modeless userforms
    5. Using hyperlinks in userforms
    6. Adding controls at runtime
    7. Adding help to a userform
    8. Creating transparent forms
    9. Next steps
  32. Chapter 23 The Windows Application Programming Interface (API)
    1. Understanding an API declaration
    2. Using an API declaration
    3. Making 32-bit- and 64-bit-compatible API declarations
    4. API function examples
    5. Next steps
  33. Chapter 24 Handling errors
    1. What happens when an error occurs?
    2. Basic error handling with the On Error GoTo syntax
    3. Generic error handlers
    4. Training your clients
    5. Errors that won’t show up in debug mode
    6. Errors while developing versus errors months later
    7. The ills of protecting code
    8. More problems with passwords
    9. Errors caused by different versions
    10. Next steps
  34. Chapter 25 Customizing the ribbon to run macros
    1. Where to add code: The customui folder and file
    2. Creating a tab and a group
    3. Adding a control to a ribbon
    4. Accessing the file structure
    5. Understanding the RELS file
    6. Renaming an Excel file and opening a workbook
    7. Using images on buttons
    8. Troubleshooting error messages
    9. Other ways to run a macro
    10. Next steps
  35. Chapter 26 Creating add-ins
    1. Characteristics of standard add-ins
    2. Converting an Excel workbook to an add-in
    3. Having a client install an add-in
    4. Standard add-ins are not secure
    5. Closing add-ins
    6. Removing add-ins
    7. Using a hidden workbook as an alternative to an add-in
    8. Next steps
  36. Chapter 27 An introduction to creating Office add-ins
    1. Creating your first Office add-in—Hello World
    2. Adding interactivity to an Office add-in
    3. A basic introduction to HTML
    4. Using XML to define an Office add-in
    5. Using JavaScript to add interactivity to an Office add-in
    6. Next steps
  37. Chapter 28 What’s new in Excel 2019 and what’s changed
    1. Office 365 subscription versus Excel 2019 perpetual
    2. If it has changed in the front end, it has changed in VBA
    3. Learning the new objects and methods
    4. Compatibility mode
    5. Next steps
  38. Index
  39. Code Snippets

Product information

  • Title: Microsoft Excel 2019 VBA and Macros, First Edition
  • Author(s): Bill Jelen, Tracy Syrstad
  • Release date: December 2018
  • Publisher(s): Microsoft Press
  • ISBN: 9781509307203