Excel® 2013 VBA and Macros

Excel® 2013 VBA and Macros

Released February 2013
Publisher(s): Que
ISBN: 9780133259469

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

SAVE TIME AND SUPERCHARGE EXCEL 2013 WITH VBA AND MACROS!

Use Excel® 2013 VBA and Macros to automate virtually any routine task, and save yourself hours, days, maybe
even weeks. Then, learn how to make Excel do things you thought were simply impossible! You’ll discover macro
techniques you won’t find anywhere else and learn how to create automated reports that are amazingly powerful
and useful. Bill Jelen and Tracy Syrstad show how to instantly visualize information, so you and your colleagues
can understand and act on it…how to capture data from anywhere, and use it anywhere…how to automate
Excel 2013’s most valuable new features. Mastering advanced Excel macros has never been easier. You’ll find
simple, step-by-step instructions, real-world examples and case studies, and 50 workbooks packed with bonus
examples, macros, and solutions–straight from MrExcel.

•   Get started fast with Excel 2013 macro development

•   Write macros that use Excel 2013 enhancements, including Timelines and the latest pivot table models

•   Work efficiently with ranges, cells, and R1C1-style formulas

•   Build super-fast applications with arrays

•   Write Excel 2013 VBA code that works on older versions of Excel

•   Create custom dialog boxes to collect information from your users

•   Use error handling to make your macros more resilient

•   Use web queries and new web service functions to integrate data from anywhere

•   Master advanced techniques such as classes, collections, and custom functions

•   Build sophisticated data mining and business analysis applications

•   Read and write to both Access and SQL Server databases

•   Control other Office programs–and even control Windows itself

•   Start writing Excel Apps similar to those in the Excel App Store

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 MrExcel.com, 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. Title Page
  2. Copyright Page
  3. Contents at a Glance
  4. Table of Contents
  5. About the Author
  6. Dedication
  7. Acknowledgments
  8. We Want to Hear from You!
  9. Reader Services
  10. Introduction
    1. Getting Results with VBA
    2. What Is in This Book?
    3. The Future of VBA and Windows Versions of Excel
    4. Special Elements and Typographical Conventions
    5. Code Files
    6. Next Steps
  11. 1. Unleash the Power of Excel with VBA
    1. The Power of Excel
    2. Barriers to Entry
    3. Knowing Your Tools: The Developer Tab
    4. Understanding Which File Types Allow Macros
    5. Macro Security
    6. Overview of Recording, Storing, and Running a Macro
    7. Running a Macro
    8. Understanding the VB Editor
    9. Understanding Shortcomings of the Macro Recorder
    10. Next Steps
  12. 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
    4. Examining Recorded Macro Code: Using the VB Editor and Help
    5. Using Debugging Tools to Figure Out Recorded Code
    6. Object Browser: The Ultimate Reference
    7. Seven Tips for Cleaning Up Recorded Code
    8. Next Steps
  13. 3. Referring to Ranges
    1. The Range Object
    2. Syntax to Specify 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. Use the Cells Property to Select a Range
    8. Use the Offset Property to Refer to a Range
    9. Use the Resize Property to Change the Size of a Range
    10. Use the Columns and Rows Properties to Specify a Range
    11. Use the Union Method to Join Multiple Ranges
    12. Use the Intersect Method to Create a New Range from Overlapping Ranges
    13. Use the ISEMPTY Function to Check Whether a Cell Is Empty
    14. Use the CurrentRegion Property to Select a Data Range
    15. Use the Areas Collection to Return a Noncontiguous Range
    16. Referencing Tables
    17. Next Steps
  14. 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
  15. 5. R1C1-Style Formulas
    1. Referring to Cells: A1 Versus R1C1 References
    2. Toggling to R1C1-Style References
    3. The Miracle of Excel Formulas
    4. Explanation of R1C1 Reference Style
    5. Array Formulas Require R1C1 Formulas
    6. Next Steps
  16. 6. Create and Manipulate Names in VBA
    1. Excel Names
    2. Global Versus Local Names
    3. Adding Names
    4. Deleting Names
    5. Adding Comments
    6. Types of Names
    7. Hiding Names
    8. Checking for the Existence of a Name
    9. Next Steps
  17. 7. Event Programming
    1. Levels of Events
    2. Using Events
    3. Workbook Events
    4. Worksheet Events
    5. Chart Sheet Events
    6. Application-Level Events
    7. Next Steps
  18. 8. Arrays
    1. Declare an Array
    2. Declare a Multidimensional Array
    3. Fill an Array
    4. Retrieve Data from an Array
    5. Use Arrays to Speed Up Code
    6. Use Dynamic Arrays
    7. Passing an Array
    8. Next Steps
  19. 9. Creating Classes, Records, 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 Property Let and Property Get to Control How Users Utilize Custom Objects
    6. Using Collections to Hold Multiple Records
    7. Using User-Defined Types to Create Custom Properties
    8. Next Steps
  20. 10. Userforms: An Introduction
    1. User Interaction Methods
    2. Creating a Userform
    3. Calling and Hiding a Userform
    4. Programming the Userform
    5. Programming Controls
    6. Using Basic Form Controls
    7. Verifying Field Entry
    8. Illegal Window Closing
    9. Getting a Filename
    10. Next Steps
  21. 11. Data Mining with Advanced Filter
    1. Replacing a Loop with AutoFilter
    2. Advanced Filter Is 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
  22. 12. Using VBA to Create Pivot Tables
    1. Introducing Pivot Tables
    2. Understanding Versions
    3. Building a Pivot Table in Excel VBA
    4. Using Advanced Pivot Table Features
    5. Filtering a Dataset
    6. Using the Data Model in Excel 2013
    7. Using Other Pivot Table Features
    8. Next Steps
  23. 13. Excel Power
    1. File Operations
    2. Combining and Separating Workbooks
    3. Working with Cell Comments
    4. Utilities to Wow Your Clients
    5. Techniques for VBA Pros
    6. Cool Applications
    7. Next Steps
  24. 14. Sample User-Defined Functions
    1. Creating User-Defined Functions
    2. Sharing UDFs
    3. Useful Custom Excel Functions
    4. Next Steps
  25. 15. Creating Charts
    1. Charting in Excel 2013
    2. Understanding the Global Settings
    3. Creating a Chart in Various Excel Versions
    4. Customizing a Chart
    5. Creating a Combo Chart
    6. Creating Advanced Charts
    7. Exporting a Chart as a Graphic
    8. Creating Pivot Charts
    9. Next Steps
  26. 16. Data Visualizations and Conditional Formatting
    1. Introduction to Data Visualizations
    2. VBA Methods and Properties for Data Visualizations
    3. Adding Data Bars to a Range
    4. Adding Color Scales to a Range
    5. Adding Icon Sets to a Range
    6. Using Visualization Tricks
    7. Using Other Conditional Formatting Methods
    8. Next Steps
  27. 17. Dashboarding with Sparklines in Excel 2013
    1. Creating Sparklines
    2. Scaling Sparklines
    3. Formatting Sparklines
    4. Creating a Dashboard
    5. Next Steps
  28. 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
  29. 19. Text File Processing
    1. Importing from Text Files
    2. Writing Text Files
    3. Next Steps
  30. 20. Automating Word
    1. Using Early Binding to Reference the Word Object
    2. Using Late Binding to Reference the 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
  31. 21. Using Access as a Back End to Enhance Multiuser Access to Data
    1. ADO Versus DAO
    2. The Tools of ADO
    3. Adding a Record to the Database
    4. Retrieving Records from the 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
  32. 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 the Userform
    8. Creating Transparent Forms
    9. Next Steps
  33. 23. Windows API
    1. What Is the Windows API?
    2. Understanding an API Declaration
    3. Using an API Declaration
    4. Making 32-Bit and 64-Bit Compatible API Declarations
    5. API Examples
    6. Next Steps
  34. 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. Train Your Clients
    5. Errors While Developing Versus Errors Months Later
    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. Out with the Old, In with the New
    2. Where to Add Your Code: customui Folder and File
    3. Creating the Tab and Group
    4. Adding a Control to Your Ribbon
    5. Accessing the File Structure
    6. Understanding the RELS File
    7. Renaming the Excel File and Opening the Workbook
    8. Using Images on Buttons
    9. Troubleshooting Error Messages
    10. Other Ways to Run a Macro
    11. Next Steps
  36. 26. Creating Add-Ins
    1. Characteristics of Standard Add-Ins
    2. Converting an Excel Workbook to an Add-In
    3. Having Your Client Install the 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 Apps for Office
    1. Creating Your First App—Hello World
    2. Adding Interactivity to Your App
    3. A Basic Introduction to HTML
    4. Using XML to Define Your App
    5. Using JavaScript to Add Interactivity to Your App
    6. Napa Office 365 Development Tools
    7. Next Steps
  38. 28. What Is New in Excel 2013 and What Has Changed
    1. If It Has Changed in the Front End, It Has Changed in VBA
    2. Learning the New Objects and Methods
    3. Compatibility Mode
    4. Next Steps
  39. Index

Product information

  • Title: Excel® 2013 VBA and Macros
  • Author(s):
  • Release date: February 2013
  • Publisher(s): Que
  • ISBN: 9780133259469