Course Code: 5771

Excel 2019 VBA and Macros

Class Dates:
10/23/2019
11/25/2019
12/17/2019
Length:
3 Days
Cost:
$1095.00
Class Time:
Technology:
office,business,microsoft
Delivery:
Instructor-Led Training

Overview

  • 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
  • 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.

Prerequisites

  • 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.
  • Recommended Courses:

  • Excel 2016 Level 1
  • Excel 2016 Level 2
  • Excel 2016 Level 3

Course Details

  • 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