Excel 2016 VBA and Macros
6/15/2020 - 6/17/20207/13/2020 - 7/15/20208/31/2020 - 9/2/2020COURSE LENGTH:
9:00am - 4:30pm
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.
FOLLOW UP COURSES
At Course Completion
You will be able to:
Automate worksheet functions.
Work with multiple workbooks.
Import and export data.
*Course cost listed does not include the cost of courseware or lunch . Please contact us at firstname.lastname@example.org or 207-775-0244 for additional pricing information, or if you have any questions. Course is subject to minimum enrollment.
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 ` RecorderLesson 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 CodeLesson 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 CaseLesson 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 FormulasLesson 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 NameLesson 7: Event Programming Levels of Events Using Events Workbook Events Worksheet Events Chart Events Application-Level EventsLesson 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 ArrayLesson 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 PropertiesLesson 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 StepsLesson 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 OnlyLesson 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 FeaturesLesson 13: Excel Power File Operations Combining and Separating Workbooks Working with Cell Comments Selecting Cells Techniques for VBA Pros Cool ApplicationsLesson 14: Sample User-Defined Functions Creating User-Defined Functions ............................................................................286Lesson 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 CompatibilityLesson 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 MethodsLesson 17: Dashboarding with Sparklines in Excel 2016 Creating Sparklines Scaling Sparklines Formatting Sparklines Creating a DashboardLesson 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 PageLesson 19: Text File Processing Importing from Text Files Writing Text FilesLesson 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 WordLesson 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 ExamplesLesson 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 FormsLesson 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 ExamplesLesson 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 VersionsLesson 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