Home Page Icon
Home Page
Table of Contents for
Title Page
Close
Title Page
by Tracy Syrstad, Bill Jelen
Excel 2016 VBA and Macros
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
What Is in This Book?
Reducing the Learning Curve
Excel VBA Power
Techie Stuff Needed to Produce Applications
Does This Book Teach Excel?
The Future of VBA and Windows Versions of Excel
Versions of Excel
Differences for Mac Users
Special Elements and Typographical Conventions
Code Files
Next Steps
1. Unleashing the Power of Excel with VBA
The Power of Excel
Barriers to Entry
The Macro Recorder Doesn’t Work!
No One on the Excel Team Is Focused on the Macro Recorder
Visual Basic Is Not Like BASIC
Good News: Climbing the Learning Curve Is Easy
Great News: Excel with VBA Is Worth the Effort
Knowing Your Tools: The Developer Tab
Understanding Which File Types Allow Macros
Macro Security
Adding a Trusted Location
Using Macro Settings to Enable Macros in Workbooks Outside Trusted Locations
Using Disable All Macros with Notification
Overview of Recording, Storing, and Running a Macro
Filling Out the Record Macro Dialog
Running a Macro
Creating a Macro Button on the Ribbon
Creating a Macro Button on the Quick Access Toolbar
Assigning a Macro to a Form Control, Text Box, or Shape
Understanding the VB Editor
VB Editor Settings
The Project Explorer
The Properties Window
Understanding Shortcomings of the Macro Recorder
Recording the Macro
Examining Code in the Programming Window
Running the Macro on Another Day Produces Undesired Results
Possible Solution: Use Relative References When Recording
Never Use AutoSum or Quick Analysis While Recording a Macro
Four Tips for Using the Macro Recorder
Next Steps
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
VBA Help Files: Using F1 to Find Anything
Using Help Topics
Examining Recorded Macro Code: Using the VB Editor and Help
Optional Parameters
Defined Constants
Properties Can Return Objects
Using Debugging Tools to Figure Out Recorded Code
Stepping Through Code
More Debugging Options: Breakpoints
Backing Up or Moving Forward in Code
Not Stepping Through Each Line of Code
Querying Anything While Stepping Through Code
Using a Watch to Set a Breakpoint
Using a Watch on an Object
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
For...Next Loops
Using Variables in the For Statement
Variations on the For...Next Loop
Exiting a Loop Early After a Condition Is Met
Nesting One Loop Inside Another Loop
Do Loops
Using the While or Until Clause in Do Loops
The VBA Loop: For Each
Object Variables
Flow Control: Using If...Then...Else and Select Case
Basic Flow Control: If...Then...Else
Using Select Case...End Select for Multiple Conditions
Next Steps
5. R1C1-Style Formulas
Referring to Cells: A1 Versus R1C1 References
Toggling to R1C1-Style References
Witnessing the Miracle of Excel Formulas
Entering a Formula Once and Copying 1,000 Times
The Secret: It’s Not That Amazing
Understanding the R1C1 Reference Style
Using R1C1 with Relative References
Using R1C1 with Absolute References
Using R1C1 with Mixed References
Referring to Entire Columns or Rows with R1C1 Style
Replacing Many A1 Formulas with a Single R1C1 Formula
Remembering Column Numbers Associated with Column Letters
Using R1C1 Formulas with Array Formulas
Next Steps
6. Creating and Manipulating Names in VBA
Global Versus Local Names
Adding Names
Deleting Names
Adding Comments
Types of Names
Formulas
Strings
Numbers
Tables
Using Arrays in Names
Reserved Names
Hiding Names
Checking for the Existence of a Name
Next Steps
7. Event Programming
Levels of Events
Using Events
Event Parameters
Enabling Events
Workbook Events
Workbook-Level Sheet and Chart Events
Worksheet Events
Chart Events
Embedded Charts
Embedded Chart and Chart Sheet Events
Application-Level Events
Next Steps
8. Arrays
Declaring an Array
Declaring a Multidimensional Array
Filling an Array
Retrieving Data from an Array
Using Arrays to Speed Up Code
Using Dynamic Arrays
Passing an Array
Next Steps
9. Creating Classes and Collections
Inserting a Class Module
Trapping Application and Embedded Chart Events
Application Events
Embedded Chart Events
Creating a Custom Object
Using a Custom Object
Using Collections
Creating a Collection
Creating a Collection in a Standard Module
Creating a Collection in a Class Module
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
Userform Events
Programming Controls
Using Basic Form Controls
Using Labels, Text Boxes, and Command Buttons
Deciding Whether to Use List Boxes or Combo Boxes in Forms
Adding Option Buttons to a Userform
Adding Graphics to a Userform
Using a Spin Button on a Userform
Using the MultiPage Control to Combine Forms
Verifying Field Entry
Illegal Window Closing
Getting a Filename
Next Steps
11. Data Mining with Advanced Filter
Replacing a Loop with AutoFilter
Using AutoFilter Techniques
Selecting Visible Cells Only
Advanced Filter—Easier in VBA Than in Excel
Using the Excel Interface to Build an Advanced Filter
Using Advanced Filter to Extract a Unique List of Values
Extracting a Unique List of Values with the User Interface
Extracting a Unique List of Values with VBA Code
Getting Unique Combinations of Two or More Fields
Using Advanced Filter with Criteria Ranges
Joining Multiple Criteria with a Logical OR
Joining Two Criteria with a Logical AND
Other Slightly Complex Criteria Ranges
The Most Complex Criteria: Replacing the List of Values with a Condition Created as the Result of a Formula
Using Filter in Place in Advanced Filter
Catching No Records When Using a Filter in Place
Showing All Records After Running a Filter in Place
The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only
Copying All Columns
Copying a Subset of Columns and Reordering
Excel in Practice: Turning Off a Few Drop-downs in the AutoFilter
Next Steps
12. Using VBA to Create Pivot Tables
Understanding How Pivot Tables Evolved Over Various Excel Versions
While Building a Pivot Table in Excel VBA
Defining the Pivot Cache
Creating and Configuring the Pivot Table
Adding Fields to the Data Area
Learning Why You Cannot Move or Change Part of a Pivot Report
Determining the Size of a Finished Pivot Table to Convert the Pivot Table to Values
Using Advanced Pivot Table Features
Using Multiple Value Fields
Grouping Daily Dates to Months, Quarters, or Years
Changing the Calculation to Show Percentages
Eliminating Blank Cells in the Values Area
Controlling the Sort Order with AutoSort
Replicating the Report for Every Product
Filtering a Data Set
Manually Filtering Two or More Items in a Pivot Field
Using the Conceptual Filters
Using the Search Filter
Setting Up Slicers to Filter a Pivot Table
Setting Up a Timeline to Filter an Excel 2016 Pivot Table
Using the Data Model in Excel 2016
Adding Both Tables to the Data Model
Creating a Relationship Between the Two Tables
Defining the PivotCache and Building the Pivot Table
Adding Model Fields to the Pivot Table
Adding Numeric Fields to the Values Area
Putting It All Together
Using Other Pivot Table Features
Calculated Data Fields
Calculated Items
Using ShowDetail to Filter a Record Set
Changing the Layout from the Design Tab
Settings for the Report Layout
Suppressing Subtotals for Multiple Row Fields
Next Steps
13. Excel Power
File Operations
Listing Files in a Directory
Importing and Deleting a CSV File
Reading a Text File into Memory and Parsing
Combining and Separating Workbooks
Separating Worksheets into Workbooks
Combining Workbooks
Filtering and Copying Data to Separate Worksheets
Copying Data to Separate Worksheets Without Using Filter
Exporting Data to an XML File
Working with Cell Comments
Resizing Comments
Placing a Chart in a Comment
Selecting Cells
Using Conditional Formatting to Highlight the Selected Cell
Highlighting the Selected Cell Without Using Conditional Formatting
Selecting/Deselecting Noncontiguous Cells
Creating a Hidden Log File
Techniques for VBA Pros
Creating an Excel State Class Module
Drilling-Down a Pivot Table
Filtering an OLAP Pivot Table by a List of Items
Creating a Custom Sort Order
Creating a Cell Progress Indicator
Using a Protected Password Box
Changing Case
Selecting with SpecialCells
Resetting a Table’s Format
Cool Applications
Getting Historical Stock/Fund Quotes
Using VBA Extensibility to Add Code to New Workbooks
Next Steps
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
Contrasting the Good and Bad VBA to Create Charts
Planning for More Charts to Break
Using .AddChart2 to Create a Chart
Understanding Chart Styles
Formatting a Chart
Referring to a Specific Chart
Specifying a Chart Title
Applying a Chart Color
Filtering a Chart
Using SetElement to Emulate Changes from the Plus Icon
Using the Format Method to Micromanage Formatting Options
Changing an Object’s Fill
Formatting Line Settings
Creating a Combo Chart
Exporting a Chart as a Graphic
Considering Backward Compatibility
Next Steps
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
Specifying an Icon Set
Specifying Ranges for Each Icon
Using Visualization Tricks
Creating an Icon Set for a Subset of a Range
Using Two Colors of Data Bars in a Range
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
Creating Sparklines
Scaling Sparklines
Formatting Sparklines
Using Theme Colors
Using RGB Colors
Formatting Sparkline Elements
Formatting Win/Loss Charts
Creating a Dashboard
Observations About Sparklines
Creating Hundreds of Individual Sparklines in a Dashboard
Next Steps
18. Reading from and Writing to the Web
Getting Data from the Web
Building Multiple Queries with VBA
Finding Results from Retrieved Data
Putting It All Together
Examples of Scraping Websites Using Web Queries
Using Application.OnTime to Periodically Analyze Data
Using Ready Mode for Scheduled Procedures
Specifying a Window of Time for an Update
Canceling a Previously Scheduled Macro
Closing Excel Cancels All Pending Scheduled Macros
Scheduling a Macro to Run x Minutes in the Future
Scheduling a Verbal Reminder
Scheduling a Macro to Run Every Two Minutes
Publishing Data to a Web Page
Using VBA to Create Custom Web Pages
Using Excel as a Content Management System
Bonus: FTP from Excel
Next Steps
19. Text File Processing
Importing from Text Files
Importing Text Files with Fewer Than 1,048,576 Rows
Dealing with Text Files with More Than 1,048,576 Rows
Writing Text Files
Next Steps
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
Using the Watches Window to Retrieve the Real Value of a Constant
Using the Object Browser to Retrieve the Real Value of a Constant
Understanding Word’s Objects
The Document Object
The Selection Object
The Range Object
Bookmarks
Controlling Form Fields in Word
Next Steps
21. Using Access as a Back End to Enhance Multiuser Access to Data
ADO Versus DAOs
The Tools of ADO
Adding a Record to a Database
Retrieving Records from a Database
Updating an Existing Record
Deleting Records via ADO
Summarizing Records via ADO
Other Utilities via ADO
Checking for the Existence of Tables
Checking for the Existence of a Field
Adding a Table On the Fly
Adding a Field On the Fly
SQL Server Examples
Next Steps
22. Advanced Userform Techniques
Using the UserForm Toolbar in the Design of Controls on Userforms
More Userform Controls
Checkbox Controls
Controls and Collections
Modeless Userforms
Using Hyperlinks in Userforms
Adding Controls at Runtime
Resizing the Userform On the Fly
Adding a Control On the Fly
Sizing On the Fly
Adding Other Controls
Adding an Image On the Fly
Putting It All Together
Adding Help to a Userform
Showing Accelerator Keys
Adding Control Tip Text
Creating the Tab Order
Coloring the Active Control
Creating Transparent Forms
Next Steps
23. The Windows Application Programming Interface (API)
Understanding an API Declaration
Using an API Declaration
Making 32-Bit- and 64-Bit-Compatible API Declarations
API Function Examples
Retrieving the Computer Name
Checking Whether an Excel File Is Open on a Network
Retrieving Display-Resolution Information
Customizing the About Dialog
Disabling the X for Closing a Userform
Creating a Running Timer
Playing Sounds
Next Steps
24. Handling Errors
What Happens When an Error Occurs?
A Misleading Debug Error in Userform Code
Basic Error Handling with the On Error GoTo Syntax
Generic Error Handlers
Handling Errors by Choosing to Ignore Them
Suppressing Excel Warnings
Encountering Errors on Purpose
Training Your Clients
Errors While Developing Versus Errors Months Later
Runtime Error 9: Subscript Out of Range
Runtime Error 1004: Method Range of Object Global Failed
The Ills of Protecting Code
More Problems with Passwords
Errors Caused by Different Versions
Next Steps
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
Using Microsoft Office Icons on a Ribbon
Adding Custom Icon Images to a Ribbon
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
Using a Keyboard Shortcut to Run a Macro
Attaching a Macro to a Command Button
Attaching a Macro to a Shape
Attaching a Macro to an ActiveX Control
Running a Macro from a Hyperlink
Next Steps
26. Creating Add-ins
Characteristics of Standard Add-ins
Converting an Excel Workbook to an Add-in
Using Save As to Convert a File to an Add-in
Using the VB Editor to Convert a File to an Add-in
Having a Client Install an Add-in
Closing Add-ins
Removing Add-ins
Using a Hidden Workbook as an Alternative to an Add-in
Next Steps
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 Tags
Adding Buttons
Using CSS Files
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
If It Has Changed in the Front End, It Has Changed in VBA
The Ribbon
Single Document Interface (SDI)
Quick Analysis Tool
Charts
Pivot Tables
Slicers
SmartArt
Learning the New Objects and Methods
Compatibility Mode
Using the Version Property
Using the Excel8CompatibilityMode Property
Next Steps
Index
Code Snippets
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
About This E-Book
Next
Next Chapter
Copyright Page
Excel® 2016
VBA and Macros
Bill Jelen
Tracy Syrstad
800 E. 96th Street
Indianapolis, Indiana 46240
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset