You are previewing Excel 2016 VBA and Macros.
O'Reilly logo
Excel 2016 VBA and Macros

Book Description


Use this guide to automate virtually any routine 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, so you can act on it… capture data from anywhere, and use it anywhere… automate Excel 2016’s best new features. You’ll find simple, step-by-step instructions, real-world case studies, and 50 workbooks packed with bonus examples, macros, and solutions—straight from MrExcel!

  • Get started fast with Excel macro development

  • Work efficiently with ranges, cells, and formulas

  • Build super-fast applications with arrays

  • Automate Excel’s new pivot table enhancements

  • Collect user data with custom dialogs

  • Make your macros more reliable and resilient

  • Pull data from the Internet with web queries

  • Use advanced classes, collections, and custom functions

  • Build sophisticated business analysis solutions

  • Read and write to Access or SQL Server databases

  • Control other Office programs, and Windows itself

  • Write code that also works on older Excel versions

  • Start writing Office Store-style Excel Apps

  • About MrExcel Library

    Every book in the MrExcel Library pinpoints a specific set of crucial Excel tasks and presents focused skills and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website, these books will

  • Dramatically increase your productivity—saving you 50 hours a year or more

  • Present proven, creative strategies for solving real-world problems

  • Show you how to get great results, no matter how much data you have

  • Help you avoid critical mistakes that even experienced users make

  • Table of Contents

    1. About This E-Book
    2. Title Page
    3. Copyright Page
    4. Contents at a Glance
    5. Contents
    6. About the Authors
    7. Dedications
    8. Acknowledgments
    9. We Want to Hear from You!
    10. Reader Services
    11. Introduction
      1. What Is in This Book?
        1. Reducing the Learning Curve
        2. Excel VBA Power
        3. Techie Stuff Needed to Produce Applications
        4. Does This Book Teach Excel?
      2. The Future of VBA and Windows Versions of Excel
        1. Versions of Excel
        2. Differences for Mac Users
      3. Special Elements and Typographical Conventions
      4. Code Files
      5. Next Steps
    12. 1. Unleashing the Power of Excel with VBA
      1. The Power of Excel
      2. Barriers to Entry
        1. The Macro Recorder Doesn’t Work!
        2. No One on the Excel Team Is Focused on the Macro Recorder
        3. Visual Basic Is Not Like BASIC
        4. Good News: Climbing the Learning Curve Is Easy
        5. Great News: Excel with VBA Is Worth the Effort
      3. Knowing Your Tools: The Developer Tab
      4. Understanding Which File Types Allow Macros
      5. Macro Security
        1. Adding a Trusted Location
        2. Using Macro Settings to Enable Macros in Workbooks Outside Trusted Locations
        3. Using Disable All Macros with Notification
      6. Overview of Recording, Storing, and Running a Macro
        1. Filling Out the Record Macro Dialog
      7. Running a Macro
        1. Creating a Macro Button on the Ribbon
        2. Creating a Macro Button on the Quick Access Toolbar
        3. Assigning a Macro to a Form Control, Text Box, or Shape
      8. Understanding the VB Editor
        1. VB Editor Settings
        2. The Project Explorer
        3. The Properties Window
      9. Understanding Shortcomings of the Macro Recorder
        1. Recording the Macro
        2. Examining Code in the Programming Window
        3. Running the Macro on Another Day Produces Undesired Results
        4. Possible Solution: Use Relative References When Recording
        5. Never Use AutoSum or Quick Analysis While Recording a Macro
        6. Four Tips for Using the Macro Recorder
      10. Next Steps
    13. 2. This Sounds Like BASIC, So Why Doesn’t It Look Familiar?
      1. I Can’t Understand This Code
      2. Understanding the Parts of VBA “Speech”
      3. VBA Is Not Really Hard
        1. VBA Help Files: Using F1 to Find Anything
        2. Using Help Topics
      4. Examining Recorded Macro Code: Using the VB Editor and Help
        1. Optional Parameters
        2. Defined Constants
        3. Properties Can Return Objects
      5. Using Debugging Tools to Figure Out Recorded Code
        1. Stepping Through Code
        2. More Debugging Options: Breakpoints
        3. Backing Up or Moving Forward in Code
        4. Not Stepping Through Each Line of Code
        5. Querying Anything While Stepping Through Code
        6. Using a Watch to Set a Breakpoint
        7. Using a Watch on an Object
      6. Object Browser: The Ultimate Reference
      7. Seven Tips for Cleaning Up Recorded Code
        1. Tip 1: Don’t Select Anything
        2. Tip 2: Use Cells(2,5) Because It’s More Convenient Than Range("E2")
        3. Tip 3: Use More Reliable Ways to Find the Last Row
        4. Tip 4: Use Variables to Avoid Hard-Coding Rows and Formulas
        5. Tip 5: Use R1C1 Formulas That Make Your Life Easier
        6. Tip 6: Copy and Paste in a Single Statement
        7. Tip 7: Use With...End With to Perform Multiple Actions
      8. Next Steps
    14. 3. Referring to Ranges
      1. The Range Object
      2. Syntax for Specifying a Range
      3. Named Ranges
      4. Shortcut for Referencing Ranges
      5. Referencing Ranges in Other Sheets
      6. Referencing a Range Relative to Another Range
      7. Using the Cells Property to Select a Range
      8. Using the Offset Property to Refer to a Range
      9. Using the Resize Property to Change the Size of a Range
      10. Using the Columns and Rows Properties to Specify a Range
      11. Using the Union Method to Join Multiple Ranges
      12. Using the Intersect Method to Create a New Range from Overlapping Ranges
      13. Using the IsEmpty Function to Check Whether a Cell Is Empty
      14. Using the CurrentRegion Property to Select a Data Range
      15. Using the Areas Collection to Return a Noncontiguous Range
      16. Referencing Tables
      17. Next Steps
    15. 4. Looping and Flow Control
      1. For...Next Loops
        1. Using Variables in the For Statement
        2. Variations on the For...Next Loop
        3. Exiting a Loop Early After a Condition Is Met
        4. Nesting One Loop Inside Another Loop
      2. Do Loops
        1. Using the While or Until Clause in Do Loops
      3. The VBA Loop: For Each
        1. Object Variables
      4. Flow Control: Using If...Then...Else and Select Case
        1. Basic Flow Control: If...Then...Else
        2. Using Select Case...End Select for Multiple Conditions
      5. Next Steps
    16. 5. R1C1-Style Formulas
      1. Referring to Cells: A1 Versus R1C1 References
      2. Toggling to R1C1-Style References
      3. Witnessing the Miracle of Excel Formulas
        1. Entering a Formula Once and Copying 1,000 Times
        2. The Secret: It’s Not That Amazing
      4. Understanding the R1C1 Reference Style
        1. Using R1C1 with Relative References
        2. Using R1C1 with Absolute References
        3. Using R1C1 with Mixed References
        4. Referring to Entire Columns or Rows with R1C1 Style
        5. Replacing Many A1 Formulas with a Single R1C1 Formula
        6. Remembering Column Numbers Associated with Column Letters
      5. Using R1C1 Formulas with Array Formulas
      6. Next Steps
    17. 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
        1. Formulas
        2. Strings
        3. Numbers
        4. Tables
        5. Using Arrays in Names
        6. Reserved Names
      6. Hiding Names
      7. Checking for the Existence of a Name
      8. Next Steps
    18. 7. Event Programming
      1. Levels of Events
      2. Using Events
        1. Event Parameters
        2. Enabling Events
      3. Workbook Events
        1. Workbook-Level Sheet and Chart Events
      4. Worksheet Events
      5. Chart Events
        1. Embedded Charts
        2. Embedded Chart and Chart Sheet Events
      6. Application-Level Events
      7. Next Steps
    19. 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
    20. 9. Creating Classes and Collections
      1. Inserting a Class Module
      2. Trapping Application and Embedded Chart Events
        1. Application Events
        2. Embedded Chart Events
      3. Creating a Custom Object
      4. Using a Custom Object
      5. Using Collections
        1. Creating a Collection
        2. Creating a Collection in a Standard Module
        3. Creating a Collection in a Class Module
      6. Using Dictionaries
      7. Using User-Defined Types to Create Custom Properties
      8. Next Steps
      9. Input Boxes
      10. Message Boxes
      11. Creating a Userform
      12. Calling and Hiding a Userform
      13. Programming Userforms
        1. Userform Events
      14. Programming Controls
      15. Using Basic Form Controls
        1. Using Labels, Text Boxes, and Command Buttons
        2. Deciding Whether to Use List Boxes or Combo Boxes in Forms
        3. Adding Option Buttons to a Userform
        4. Adding Graphics to a Userform
        5. Using a Spin Button on a Userform
        6. Using the MultiPage Control to Combine Forms
      16. Verifying Field Entry
      17. Illegal Window Closing
      18. Getting a Filename
      19. Next Steps
    21. 11. Data Mining with Advanced Filter
      1. Replacing a Loop with AutoFilter
        1. Using AutoFilter Techniques
        2. Selecting Visible Cells Only
      2. Advanced Filter—Easier in VBA Than in Excel
        1. Using the Excel Interface to Build an Advanced Filter
      3. Using Advanced Filter to Extract a Unique List of Values
        1. Extracting a Unique List of Values with the User Interface
        2. Extracting a Unique List of Values with VBA Code
        3. Getting Unique Combinations of Two or More Fields
      4. Using Advanced Filter with Criteria Ranges
        1. Joining Multiple Criteria with a Logical OR
        2. Joining Two Criteria with a Logical AND
        3. Other Slightly Complex Criteria Ranges
        4. The Most Complex Criteria: Replacing the List of Values with a Condition Created as the Result of a Formula
      5. Using Filter in Place in Advanced Filter
        1. Catching No Records When Using a Filter in Place
        2. Showing All Records After Running a Filter in Place
      6. The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only
        1. Copying All Columns
        2. Copying a Subset of Columns and Reordering
        3. Excel in Practice: Turning Off a Few Drop-downs in the AutoFilter
      7. Next Steps
    22. 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
        1. Defining the Pivot Cache
        2. Creating and Configuring the Pivot Table
        3. Adding Fields to the Data Area
        4. Learning Why You Cannot Move or Change Part of a Pivot Report
        5. Determining the Size of a Finished Pivot Table to Convert the Pivot Table to Values
      3. Using Advanced Pivot Table Features
        1. Using Multiple Value Fields
        2. Grouping Daily Dates to Months, Quarters, or Years
        3. Changing the Calculation to Show Percentages
        4. Eliminating Blank Cells in the Values Area
        5. Controlling the Sort Order with AutoSort
        6. Replicating the Report for Every Product
      4. Filtering a Data Set
        1. Manually Filtering Two or More Items in a Pivot Field
        2. Using the Conceptual Filters
        3. Using the Search Filter
        4. Setting Up Slicers to Filter a Pivot Table
        5. Setting Up a Timeline to Filter an Excel 2016 Pivot Table
      5. Using the Data Model in Excel 2016
        1. Adding Both Tables to the Data Model
        2. Creating a Relationship Between the Two Tables
        3. Defining the PivotCache and Building the Pivot Table
        4. Adding Model Fields to the Pivot Table
        5. Adding Numeric Fields to the Values Area
        6. Putting It All Together
      6. Using Other Pivot Table Features
        1. Calculated Data Fields
        2. Calculated Items
        3. Using ShowDetail to Filter a Record Set
        4. Changing the Layout from the Design Tab
        5. Settings for the Report Layout
        6. Suppressing Subtotals for Multiple Row Fields
      7. Next Steps
    23. 13. Excel Power
      1. File Operations
        1. Listing Files in a Directory
        2. Importing and Deleting a CSV File
        3. Reading a Text File into Memory and Parsing
      2. Combining and Separating Workbooks
        1. Separating Worksheets into Workbooks
        2. Combining Workbooks
        3. Filtering and Copying Data to Separate Worksheets
        4. Copying Data to Separate Worksheets Without Using Filter
        5. Exporting Data to an XML File
      3. Working with Cell Comments
        1. Resizing Comments
        2. Placing a Chart in a Comment
      4. Selecting Cells
        1. Using Conditional Formatting to Highlight the Selected Cell
        2. Highlighting the Selected Cell Without Using Conditional Formatting
        3. Selecting/Deselecting Noncontiguous Cells
        4. Creating a Hidden Log File
      5. Techniques for VBA Pros
        1. Creating an Excel State Class Module
        2. Drilling-Down a Pivot Table
        3. Filtering an OLAP Pivot Table by a List of Items
        4. Creating a Custom Sort Order
        5. Creating a Cell Progress Indicator
        6. Using a Protected Password Box
        7. Changing Case
        8. Selecting with SpecialCells
        9. Resetting a Table’s Format
      6. Cool Applications
        1. Getting Historical Stock/Fund Quotes
        2. Using VBA Extensibility to Add Code to New Workbooks
      7. Next Steps
    24. 14. Sample User-Defined Functions
      1. Creating User-Defined Functions
      2. Sharing UDFs
      3. Useful Custom Excel Functions
        1. Setting the Current Workbook’s Name in a Cell
        2. Setting the Current Workbook’s Name and File Path in a Cell
        3. Checking Whether a Workbook Is Open
        4. Checking Whether a Sheet in an Open Workbook Exists
        5. Counting the Number of Workbooks in a Directory
        6. Retrieving the User ID
        7. Retrieving Date and Time of Last Save
        8. Retrieving Permanent Date and Time
        9. Validating an Email Address
        10. Summing Cells Based on Interior Color
        11. Counting Unique Values
        12. Removing Duplicates from a Range
        13. Finding the First Nonzero-Length Cell in a Range
        14. Substituting Multiple Characters
        15. Retrieving Numbers from Mixed Text
        16. Converting Week Number into Date
        17. Extracting a Single Element from a Delimited String
        18. Sorting and Concatenating
        19. Sorting Numeric and Alpha Characters
        20. Searching for a String Within Text
        21. Reversing the Contents of a Cell
        22. Returning the Addresses of Duplicate Max Values
        23. Returning a Hyperlink Address
        24. Returning the Column Letter of a Cell Address
        25. Using Static Random
        26. Using Select Case on a Worksheet
      4. Next Steps
    25. 15. Creating Charts
      1. Contrasting the Good and Bad VBA to Create Charts
      2. Planning for More Charts to Break
      3. Using .AddChart2 to Create a Chart
      4. Understanding Chart Styles
      5. Formatting a Chart
        1. Referring to a Specific Chart
        2. Specifying a Chart Title
        3. Applying a Chart Color
        4. Filtering a Chart
        5. Using SetElement to Emulate Changes from the Plus Icon
        6. Using the Format Method to Micromanage Formatting Options
        7. Changing an Object’s Fill
        8. Formatting Line Settings
      6. Creating a Combo Chart
      7. Exporting a Chart as a Graphic
      8. Considering Backward Compatibility
      9. Next Steps
    26. 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
        1. Specifying an Icon Set
        2. Specifying Ranges for Each Icon
      5. Using Visualization Tricks
        1. Creating an Icon Set for a Subset of a Range
        2. Using Two Colors of Data Bars in a Range
      6. Using Other Conditional Formatting Methods
        1. Formatting Cells That Are Above or Below Average
        2. Formatting Cells in the Top 10 or Bottom 5
        3. Formatting Unique or Duplicate Cells
        4. Formatting Cells Based on Their Value
        5. Formatting Cells That Contain Text
        6. Formatting Cells That Contain Dates
        7. Formatting Cells That Contain Blanks or Errors
        8. Using a Formula to Determine Which Cells to Format
        9. Using the New NumberFormat Property
      7. Next Steps
    27. 17. Dashboarding with Sparklines in Excel 2016
      1. Creating Sparklines
      2. Scaling Sparklines
      3. Formatting Sparklines
        1. Using Theme Colors
        2. Using RGB Colors
        3. Formatting Sparkline Elements
        4. Formatting Win/Loss Charts
      4. Creating a Dashboard
        1. Observations About Sparklines
        2. Creating Hundreds of Individual Sparklines in a Dashboard
      5. Next Steps
    28. 18. Reading from and Writing to the Web
      1. Getting Data from the Web
        1. Building Multiple Queries with VBA
        2. Finding Results from Retrieved Data
        3. Putting It All Together
        4. Examples of Scraping Websites Using Web Queries
      2. Using Application.OnTime to Periodically Analyze Data
        1. Using Ready Mode for Scheduled Procedures
        2. Specifying a Window of Time for an Update
        3. Canceling a Previously Scheduled Macro
        4. Closing Excel Cancels All Pending Scheduled Macros
        5. Scheduling a Macro to Run x Minutes in the Future
        6. Scheduling a Verbal Reminder
        7. Scheduling a Macro to Run Every Two Minutes
      3. Publishing Data to a Web Page
        1. Using VBA to Create Custom Web Pages
        2. Using Excel as a Content Management System
        3. Bonus: FTP from Excel
      4. Next Steps
    29. 19. Text File Processing
      1. Importing from Text Files
        1. Importing Text Files with Fewer Than 1,048,576 Rows
        2. Dealing with Text Files with More Than 1,048,576 Rows
      2. Writing Text Files
      3. Next Steps
    30. 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 a 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
        1. Using the Watches Window to Retrieve the Real Value of a Constant
        2. Using the Object Browser to Retrieve the Real Value of a Constant
      7. Understanding Word’s Objects
        1. The Document Object
        2. The Selection Object
        3. The Range Object
      8. Controlling Form Fields in Word
      9. Next Steps
    31. 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
        1. Checking for the Existence of Tables
        2. Checking for the Existence of a Field
        3. Adding a Table On the Fly
        4. Adding a Field On the Fly
      9. SQL Server Examples
      10. Next Steps
    32. 22. Advanced Userform Techniques
      1. Using the UserForm Toolbar in the Design of Controls on Userforms
      2. More Userform Controls
        1. Checkbox Controls
      3. Controls and Collections
      4. Modeless Userforms
      5. Using Hyperlinks in Userforms
      6. Adding Controls at Runtime
        1. Resizing the Userform On the Fly
        2. Adding a Control On the Fly
        3. Sizing On the Fly
        4. Adding Other Controls
        5. Adding an Image On the Fly
        6. Putting It All Together
      7. Adding Help to a Userform
        1. Showing Accelerator Keys
        2. Adding Control Tip Text
        3. Creating the Tab Order
        4. Coloring the Active Control
      8. Creating Transparent Forms
      9. Next Steps
    33. 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
        1. Retrieving the Computer Name
        2. Checking Whether an Excel File Is Open on a Network
        3. Retrieving Display-Resolution Information
        4. Customizing the About Dialog
        5. Disabling the X for Closing a Userform
        6. Creating a Running Timer
        7. Playing Sounds
      5. Next Steps
    34. 24. Handling Errors
      1. What Happens When an Error Occurs?
        1. A Misleading Debug Error in Userform Code
      2. Basic Error Handling with the On Error GoTo Syntax
      3. Generic Error Handlers
        1. Handling Errors by Choosing to Ignore Them
        2. Suppressing Excel Warnings
        3. Encountering Errors on Purpose
      4. Training Your Clients
      5. Errors While Developing Versus Errors Months Later
        1. Runtime Error 9: Subscript Out of Range
        2. Runtime Error 1004: Method Range of Object Global Failed
      6. The Ills of Protecting Code
      7. More Problems with Passwords
      8. Errors Caused by Different Versions
      9. Next Steps
    35. 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
        1. Using Microsoft Office Icons on a Ribbon
        2. Adding Custom Icon Images to a Ribbon
      8. Troubleshooting Error Messages
        1. The Attribute “Attribute Name” on the Element “customui Ribbon” Is Not Defined in the DTD/Schema
        2. Illegal Qualified Name Character
        3. Element “customui Tag Name” Is Unexpected According to Content Model of Parent Element “customui Tag Name”
        4. Found a Problem with Some Content
        5. Wrong Number of Arguments or Invalid Property Assignment
        6. Invalid File Format or File Extension
        7. Nothing Happens
      9. Other Ways to Run a Macro
        1. Using a Keyboard Shortcut to Run a Macro
        2. Attaching a Macro to a Command Button
        3. Attaching a Macro to a Shape
        4. Attaching a Macro to an ActiveX Control
        5. Running a Macro from a Hyperlink
      10. Next Steps
    36. 26. Creating Add-ins
      1. Characteristics of Standard Add-ins
      2. Converting an Excel Workbook to an Add-in
        1. Using Save As to Convert a File to an Add-in
        2. Using the VB Editor to Convert a File to an Add-in
      3. Having a Client Install an Add-in
      4. Closing Add-ins
      5. Removing Add-ins
      6. Using a Hidden Workbook as an Alternative to an Add-in
      7. Next Steps
    37. 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
        1. Using Tags
        2. Adding Buttons
        3. Using CSS Files
      4. Using XML to Define an Office Add-in
      5. Using JavaScript to Add Interactivity to an Office Add-in
        1. The Structure of a Function
        2. Variables
        3. Strings
        4. Arrays
        5. JavaScript for Loops
        6. How to Do an if Statement in JavaScript
        7. How to Do a Select..Case Statement in JavaScript
        8. How to Do a For Statement in JavaScript
        9. Mathematical, Logical, and Assignment Operators
        10. Math Functions in JavaScript
        11. Writing to the Content Pane or Task Pane
        12. JavaScript Changes for Working in an Office Add-in
      6. Napa Office 365 Development Tools
      7. Next Steps
    38. 28. What’s New in Excel 2016 and What’s Changed
      1. If It Has Changed in the Front End, It Has Changed in VBA
        1. The Ribbon
        2. Single Document Interface (SDI)
        3. Quick Analysis Tool
        4. Charts
        5. Pivot Tables
        6. Slicers
        7. SmartArt
      2. Learning the New Objects and Methods
      3. Compatibility Mode
        1. Using the Version Property
        2. Using the Excel8CompatibilityMode Property
      4. Next Steps
    39. Index
    40. Code Snippets