- 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
Dedications
For Robert K. Jelen
—Bill Jelen
For Marlee Jo Jacobson
—Tracy Syrstad
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.