Excel 2016 VBA and Macros

Course Details

• Lesson 1: Unleashing the Power of Excel with VBA
• The Power of Excel
• Barriers to Entry
• Macro Security
• Notification
• Running a Macro
• Understanding the VB Editor
• Understanding Shortcomings of the Macro Recorder `
• Recorder
• Lesson 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
• Lesson 3. Using the Offset Property to Refer to a Range
• 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
• Lesson 4: Looping and Flow Control
• For...Next Loops
• Do Loops
• The VBA Loop: For Each
• Flow Control: Using If...Then...Else and Select Case
• Lesson 5: Style Formulas
• Referring to Cells: A1 Versus R1C1 References
• Toggling to R1C1-Style References
• Witnessing the Miracle of Excel Formulas
• Understanding the R1C1 Reference Style
• Using R1C1 Formulas with Array Formulas
• Lesson 6: Creating and Manipulating Names in VBA
• Global Versus Local Names
• Adding Names
• Deleting Names
• Adding Comments
• Types of Names
• Hiding Names
• Checking for the Existence of a Name
• Lesson 7: Event Programming
• Levels of Events
• Using Events
• Workbook Events
• Worksheet Events
• Chart Events
• Application-Level Events
• Lesson 8: Arrays
• Declaring an Array
• Declaring a Multidimensional Array
• Filling an Array
• Retrieving Data from an ArrayUsing Arrays to Speed Up Code
• Using Dynamic Arrays
• Passing an Array
• Lesson 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
• Lesson 10: Userforms: An Introduction
• 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
• Lesson 11: 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
• Lesson 12: Using VBA to Create Pivot Tables
• Understanding How Pivot Tables Evolved Over Various Excel Versions
• While Building a Pivot Table in Excel VBA
• Using Advanced Pivot Table Features
• Filtering a Data Set
• Using the Data Model in Excel 2016
• Using Other Pivot Table Features
• Lesson 13: Excel Power
• File Operations
• Combining and Separating Workbooks
• Working with Cell Comments
• Selecting Cells
• Techniques for VBA Pros
• Cool Applications
• Lesson 14: Sample User-Defined Functions
• Creating User-Defined Functions
• ............................................................................286
• Lesson 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
• Creating a Combo Chart
• Exporting a Chart as a Graphic
• Considering Backward Compatibility
• Lesson 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
• Lesson 17: Dashboarding with Sparklines in Excel 2016
• Creating Sparklines
• Scaling Sparklines
• Formatting Sparklines
• Creating a Dashboard
• Lesson 18: Reading from and Writing to the Web
• Getting Data from the Web
• Using Application.OnTime to Periodically Analyze Data
• Publishing Data to a Web Page
• Lesson 19: Text File Processing
• Importing from Text Files
• Writing Text Files
• Lesson 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
• Lesson 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
• SQL Server Examples
• Lesson 22: Advanced Userform Techniques
• Using the UserForm Toolbar in the Design of Controls on Userforms
• More Userform Controls
• Controls and Collections
• Using Hyperlinks in Userforms
• Adding Controls at Runtime
• Adding Help to a Userform
• Creating Transparent Forms
• Lesson 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
• Lesson 24: Handling Errors
• What Happens When an Error Occurs?
• Basic Error Handling with the On Error GoTo Syntax
• Generic Error Handlers
• Training Your Clients
• Errors While Developing Versus Errors Months Later
• The Ills of Protecting Code
• More Problems with Passwords
• Errors Caused by Different Versions
• Lesson 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
• Other Ways to Run a Macro