Excel 2019 VBA and Macros
CLASS DATE(s):
Request a Class

COURSE LENGTH: 3 Days

COURSE COST: $1095.00

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

Printable version of this course
print

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. You must have good Excel skills and understand key concepts of spreadsheets or equivalent. It is not necessary to have prior programming knowledge. We cover VBA Macro programming concepts from the very basic level.

At Course Completion you will be able to:
Automate worksheet functions.
Audit worksheets.
Analyze data.
Work with multiple workbooks.
Import and export data.

PREREQUISITE COURSES  

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

COURSE TOPICS:


Lesson 1. Unleashing the power of Excel with VBA
Barriers to entry
Knowing your tools: The Developer tab
Understanding which file types allow macros
Macro Security
Overview of recording, sorting, and running a macro
Running a macro
Understanding the VB Editor
Understanding shortcomings of the macro recorder

Lesson 2: This sounds like Basic
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: Referring to ranges
The Range object
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 Columns and Rows properties to specify a range
Using the Intersect method to create a new range from overlapping ranges
Using the Is Empty function to check whether a cell is empty
Using the Current Region property to select a data range
Using the Areas collection to return a noncontiguous range
Referencing tables

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: R1C1-style formulas
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, Formulas, Strings, Numbers, Tables, Using arrays in names, Reserved names
Hiding names
Checking the existence of a name

Lesson 7: Event programming
Levels of events
Using events, parameters, enabling 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 array
Using arrays to speed up code
Using dynamic arrays

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: User forms - An Introduction
Input boxes
Message boxes
Creating a userform
Calling and hiding a userform
Programming user forms
Programming controls
Using basic form controls
Verifying field entry
Illegal window closing
Getting a file name

Lesson 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 a place in Advanced Filter
The real workhouse: xFilterCopy 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 2019
Using other pivot table features

Lesson 13: Excel Power
File operations
Combining and separating workbooks
Working with cell comments
Tracking user changes
Techniques for VBA pros
Creating a custom sort order
Creating a cell progress indicator
Using a protected password box
Changing case
Resetting a table's format

Lesson 14: Sample user-defined functions
Creating user-defined functions
Sharing UDFs
Useful custom Excel functions
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

Lesson 15: Creating Charts
Using .AddChart2 to create a chart
Understanding chart styles
Formatting a chart
Creating a combo chart
Creating map charts
Creating waterfall charts
Exporting a chart as a graphic
Considering backsword 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
Formatting cells that are above or below average
Formatting cells in the top 10 or bottom 5
Formatting unique or duplicate cells
Using a formula to determine which cells to format

Lesson 17: Dashboarding with sparklines in Excel 2019
Creating sparklines
Scaling sparklines
Formatting sparklines
Creating a dashboard

Lesson 18: Reading from and writing to the web
Getting data from the web
Building multiple queries with VBA
Finding results from retrieved data
Using Application OnTime to periodically analyze data
Publishing data to a web page
Using VBA to create custom web pages
Using Excel as a content management system
FTP from Excel

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 the 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 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 utilites via ADO
SQL Server examples

Lesson 22: Advanced userform techniques
Using the UserForm toolbar in the design of controls on userforms
More userform controls
Control's and collections
Modeless userforms
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
Retrieving the computer name
Checking whether an Excel file is open on a network
Disabling the X for closing a userform
Playing sounds

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 that won't show up in debug mode
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
Lesson 26: Creating add-ins
Lesson 27: An introduction to creating Office add-ins
Lesson 28: What's new in Excel 2019 and what's changed