Excel 2016 VBA and Macros
CLASS DATE(s):
7/13/2020 - 7/15/2020
8/31/2020 - 9/2/2020
9/28/2020 - 9/30/2020
10/13/2020 - 10/15/2020

COURSE LENGTH: 3 Days

COURSE COST: $1095.00

COURSE TIMES: 9:00am - 4:30pm

Printable version of this course
print
Register for this course
register

COURSE OVERVIEW

By taking this 3 day class, a student will learn to record macros, edit code, and learn how to write their own VBA sub procedures. This course provides many examples of macro development that can help manage excel spreadsheets. Students will become familiar with the visual basic editor window and the immediate window. The course also studies many styles of code i.e. with statements, if then- else statements, and loops along with event driven code.

AUDIENCE AND PREREQUISITES

This course is intended for a student who has experience working with Excel and would like to learn more about creating macros, working with shared documents, analyzing data, and auditing worksheets.

At Course Completion

You will be able to:

Automate worksheet functions.
Audit worksheets.
Analyze data.
Work with multiple workbooks.
Import and export data.

FOLLOW UP COURSES  

*Course cost listed does not include the cost of courseware or lunch . Please contact us at learn@vtec.org or 207-775-0244 for additional pricing information, or if you have any questions. Course is subject to minimum enrollment.

COURSE TOPICS:


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