Book description
This is the Rough Cut version of the printed book.
SAVE TIME AND SUPERCHARGE EXCEL 2016 WITH VBA AND MACROS!
Use Excel 2016 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 2016’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 2016 macro development
Write macros that use Excel 2016 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 2016 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
- About This E-Book
- Title Page
- Copyright Page
- Contents at a Glance
- Contents
- About the Authors
- Dedications
- Acknowledgments
- We Want to Hear from You!
- Reader Services
- Introduction
- 1. Unleashing the Power of Excel with VBA
-
2. This Sounds Like BASIC, So Why Doesn’t It Look Familiar?
- I Can’t Understand This Code
- 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
- Tip 1: Don’t Select Anything
- Tip 2: Use Cells(2,5) Because It’s More Convenient Than Range("E2")
- Tip 3: Use More Reliable Ways to Find the Last Row
- Tip 4: Use Variables to Avoid Hard-Coding Rows and Formulas
- Tip 5: Use R1C1 Formulas That Make Your Life Easier
- Tip 6: Copy and Paste in a Single Statement
- Tip 7: Use With...End With to Perform Multiple Actions
- Next Steps
-
3. Referring to Ranges
- The Range Object
- Syntax for Specifying a Range
- Named Ranges
- Shortcut for Referencing Ranges
- 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
- 4. Looping and Flow Control
- 5. R1C1-Style Formulas
- 6. Creating and Manipulating Names in VBA
- 7. Event Programming
- 8. Arrays
-
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
- 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 Filename
- Next Steps
-
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
- 12. Using VBA to Create Pivot Tables
- 13. Excel Power
-
14. Sample User-Defined Functions
- Creating User-Defined Functions
- Sharing UDFs
-
Useful Custom Excel Functions
- Setting the Current Workbook’s Name in a Cell
- Setting the Current Workbook’s Name and File Path in a Cell
- Checking Whether a Workbook Is Open
- Checking Whether a Sheet in an Open Workbook Exists
- Counting the Number of Workbooks in a Directory
- Retrieving the User ID
- Retrieving Date and Time of Last Save
- Retrieving Permanent Date and Time
- Validating an Email Address
- Summing Cells Based on Interior Color
- Counting Unique Values
- Removing Duplicates from a Range
- Finding the First Nonzero-Length Cell in a Range
- Substituting Multiple Characters
- Retrieving Numbers from Mixed Text
- Converting Week Number into Date
- Extracting a Single Element from a Delimited String
- Sorting and Concatenating
- Sorting Numeric and Alpha Characters
- Searching for a String Within Text
- Reversing the Contents of a Cell
- Returning the Addresses of Duplicate Max Values
- Returning a Hyperlink Address
- Returning the Column Letter of a Cell Address
- Using Static Random
- Using Select Case on a Worksheet
- Next Steps
- 15. Creating Charts
-
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
- Formatting Cells That Are Above or Below Average
- Formatting Cells in the Top 10 or Bottom 5
- Formatting Unique or Duplicate Cells
- Formatting Cells Based on Their Value
- Formatting Cells That Contain Text
- Formatting Cells That Contain Dates
- Formatting Cells That Contain Blanks or Errors
- Using a Formula to Determine Which Cells to Format
- Using the New NumberFormat Property
- Next Steps
- 17. Dashboarding with Sparklines in Excel 2016
- 18. Reading from and Writing to the Web
- 19. Text File Processing
-
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 a 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
- 21. Using Access as a Back End to Enhance Multiuser Access to Data
- 22. Advanced Userform Techniques
- 23. The Windows Application Programming Interface (API)
- 24. Handling Errors
-
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
- The Attribute “Attribute Name” on the Element “customui Ribbon” Is Not Defined in the DTD/Schema
- Illegal Qualified Name Character
- Element “customui Tag Name” Is Unexpected According to Content Model of Parent Element “customui Tag Name”
- Found a Problem with Some Content
- Wrong Number of Arguments or Invalid Property Assignment
- Invalid File Format or File Extension
- Nothing Happens
- Other Ways to Run a Macro
- Next Steps
- 26. Creating Add-ins
-
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
- The Structure of a Function
- Variables
- Strings
- Arrays
- JavaScript for Loops
- How to Do an if Statement in JavaScript
- How to Do a Select..Case Statement in JavaScript
- How to Do a For each..next Statement in JavaScript
- Mathematical, Logical, and Assignment Operators
- Math Functions in JavaScript
- Writing to the Content Pane or Task Pane
- JavaScript Changes for Working in an Office Add-in
- Napa Office 365 Development Tools
- Next Steps
- 28. What’s New in Excel 2016 and What’s Changed
- Index
- Code Snippets
Product information
- Title: Excel 2016 VBA and Macros
- Author(s):
- Release date: November 2015
- Publisher(s): Que
- ISBN: 9780134386027
You might also like
book
Excel 2016 Power Programming with VBA
Maximize your Excel experience with VBA Excel 2016 Power Programming with VBA is fully updated to …
book
VBA and Macros for Microsoft Excel
Everyone is looking for ways to save money these days. That can be hard to do …
book
Writing Excel Macros with VBA, 2nd Edition
Newly updated for Excel 2002, Writing Excel Macros with VBA, 2nd Edition provides Excel power-users, as …
book
Excel 2013 Power Programming with VBA
Maximize your Excel 2013 experience using VBA application development The new Excel 2013 boasts updated features, …