Excel 2019 VBA and Macros

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