Section 1 : Introduction & Scope

Lecture 1 Course Structure & Content 4:6
Lecture 2 Important Compatibility & Setup Information 3:40
Lecture 3 Course Resources Download Course Project Files 1:33
Lecture 4 Course Expectations 2:32
Lecture 5 Course Outline for Quick Reference Text

Section 2 : Your First Macro

Lecture 6 Overview Macros & Visual Basic Editor (VBE) 1:26
Lecture 7 Steps to Your First Macro Recording 5:45
Lecture 8 Visual Basic Editor What You Need to Know 11:41
Lecture 9 Project Dynamic Cell Selection (Special Cells) - Perfect for Flexible Macros 11:13
Lecture 10 Project Copy (Consolidate) Data - Absolute versus Relative Macro Recording 9:18
Lecture 11 7 Ways to Run Macros VBA code (incl 12:11
Lecture 12 OFFICE 365 Update Change from Excel Comments to Notes Text
Lecture 13 Activity Record a Macro that Deletes all Comments Text
Lecture 14 What's the Difference Between Macros and VBA Text

Section 3 : The Object Model

Lecture 15 Overview VBA Object Model (What you need to become an expert in VBA) 1:22
Lecture 16 VBA's Code Reference Object Library 3:57
Lecture 17 VB Color Guidelines & Keyboard Shortcuts 11:44
Lecture 18 Excel VBA Object Model 6:1
Lecture 19 Object Properties 4:21
Lecture 20 Object Methods 5:39
Lecture 21 How to Find the Object, Property & Method 8:1
Lecture 22 Conclusion Key Takeaways & Message from Me 2:24

Section 4 : Referencing Ranges, Worksheets & Workbooks with VBA

Lecture 23 Overview Referencing Ranges, Worksheets & Workbooks 0:55
Lecture 24 Referring to Ranges & Writing to Cells in VBA 15:22
Lecture 25 Most Useful Range Properties & Methods 13:13
Lecture 26 4 Methods to Find the Last Row of your Range 15:16
Lecture 27 Copying & resizing a variably sized range 9:13
Lecture 28 Properly Referencing Worksheets 14:31
Lecture 29 Properly Referencing Workbooks 10:6
Lecture 30 Activity Save a Hard-coded Copy of Workbook Text
Lecture 31 Project Save Hard-coded Copy & Macro-free version of workbook 12:8
Lecture 32 Key Takeaways for Referencing Ranges 2:44

Section 5 : Working with Variables

Lecture 33 Overview Role of Assignment & Variables 2:40
Lecture 34 Data Types for Better Performance 3:18
Lecture 35 Declaring Variables, Arrays & Constants (Role of Option Explicit) 12:35
Lecture 36 Using Object Variables (Set statement) 6:35
Lecture 37 Scoping Variables 6:43
Lecture 38 Activity Revisit Hard-coded Workbook Project & Use Variable Assignment Text
Lecture 39 Key Takeaways for Working with Variables 2:0

Section 6 : Looping through Collections & Making Decisions

Lecture 40 Overview Why Collections are a VBA Essential 1:12
Lecture 41 With & End With for Easier Code Writing 6:6
Lecture 42 For Each to Loop Through Collections (sheets, ranges etc 9:59
Lecture 43 IF Then (Else, ElseIF) for Conditional Outcomes 10:2
Lecture 44 Select Case as Alternative for Many IF Statements 5:6
Lecture 45 Goto Statement to Change Program Flow 7:21
Lecture 46 Activity Get the Number of Formulas on the worksheet Text
Lecture 47 Project Get the Number of Formulas on the Worksheet 6:45
Lecture 48 Bonus Unhide All Sheets in One Go 9:40
Lecture 49 Key Takeaways for Collections & Decision Making in VBA Text

Section 7 : Useful Built-in Functions

Lecture 50 Overview VBA versus Worksheet Functions 13:18
Lecture 51 Most Useful VBA Functions 9:47
Lecture 52 Message Box (also with Yes, No buttons) 8:28
Lecture 53 Input Box (VBA InputBox Function) 7:13
Lecture 54 Input Box that can Select Ranges (Excel InputBox Method) 12:12
Lecture 55 Activity Show Top 3 Values in a Message Box Based on Range Selected Text
Lecture 56 Project Show Top 3 Values in a Message box based on Range Selected 11:2
Lecture 57 Key Takeaways Built-in Functions Text

Section 8 : Debugging, Error Handling & Procedure Scope

Lecture 58 Overview Debugging, Handling Errors & Procedure Scope 1:42
Lecture 59 Debugging Options Watch, Locals, Immediate Windows & More 12:56
Lecture 60 Error Handling Different Methods 10:30
Lecture 61 Faster Code by Suppressing Pop-ups & Flickering Screen 10:23
Lecture 62 Procedure Scope & Running one Sub from Another 6:8
Lecture 63 Passing Arguments to Sub Procedures (By Ref, By Val) 9:34
Lecture 64 Activity Get the Total Number of Formulas (or Comments) Used in a Workbook Text
Lecture 65 Project Get the Total Number of Formulas (or Comments) Used in a Workbook 11:27
Lecture 66 Key Takeaways Debugging, Error Handling & Course Map Status 1:40

Section 9 : Main Project #1 Create a Tool to Automate Table of Contents

Lecture 67 Project overview Table of Contents (TOC) with one click
Lecture 68 TOC Project The Basics 10:17
Lecture 69 TOC Project Adding Hyperlinks 5:53
Lecture 70 TOC Project Testing the VBA Code 4:15
Lecture 71 TOC Project Adding Error Handling & More Checks 13:12
Lecture 72 TOC Project Adding to Personal Macro Workbook (Make it Available to all Files) 6:54
Lecture 73 First Milestone Completed! Text

Section 10 : Looping in VBA (Controlling the Flow of Code)

Lecture 74 Section Overview Looping in VBA 2:16
Lecture 75 For Next Counter Loops (Simple Example) 8:33
Lecture 76 For Next Nested Loop (Loop through Text & Cells) 15:20
Lecture 77 For Next Reverse Loop (Delete FilteredHidden Rows) 8:21
Lecture 78 Do Until & Do While Loop 9:40
Lecture 79 Practical Example of Do Loop (with Inputbox) 3:7
Lecture 80 Find Method for Quicker Lookup Results 8:47
Lecture 81 Find Method with Do Loop - Multiple Matches 6:9
Lecture 82 Add a Timer to Test & Speech to Inform 7:37
Lecture 83 Activity Document all Comments in Workbook in a Sheet Text
Lecture 84 Project Document all Comments in Workbook in a Sheet 12:14
Lecture 85 Useful VBA Statements & Key takeaways for Looping in VBA 3:14

Section 11 : Working with Arrays

Lecture 86 Overview Working with Arrays in VBA 1:22
Lecture 87 One Dimensional Arrays (and transferring back to sheet) 13:26
Lecture 88 Dynamic Arrays (size adjusts depending on a condition) 6:25
Lecture 89 Preserving Dynamic Arrays (adding more elements to the existing array)
Lecture 90 Two Dimensional Arrays 7:21
Lecture 91 Variant Arrays Quick Method to Write to Cells 8:23
Lecture 92 Activity Create a New Worksheet per Company with Specific Information Text
Lecture 93 Project Create a New Worksheet per Company with Specific Information 9:20
Lecture 94 Key Takeaways Arrays Text

Section 12 : Working with Files, Folders & Text Files

Lecture 95 Overview Working with Folders & Files 2:30
Lecture 96 INTRODUCTION TO BRAINMEASURES PROCTOR SYSTEM Pdf
Lecture 97 GetOpenFileName Method User Selects a File 9:10
Lecture 98 FileDialog Property Loop Inside a Folder 16:31
Lecture 99 Create CSV File from Data in Excel 7:21
Lecture 100 Printing & Writing to Text Files for More Control 6:32
Lecture 101 Activity Export Data to Text file (fix Delimiter to Semicolon) Text
Lecture 102 Project Export Data to Text file (fix Delimiter to Semicolon) 12:55
Lecture 103 Key Takeaways Files & Folders Text

Section 13 : Excel Tables, Formulas & Pivot Tables

Lecture 104 Overview (Pivot) Tables & Formulas 1:53
Lecture 105 Using Excel Formulas in VBA 13:34
Lecture 106 Excel Tables Use the Right Syntax 14:4
Lecture 107 Pivot Tables & VBA What you Need to Know 18:15
Lecture 108 Activity Update Existing Formulas with VBA by Adding IFERROR Text
Lecture 109 Project Update Existing Formulas with VBA by Adding IFERROR 8:0
Lecture 110 Key Takeaways (Pivot) Tables & Formula Text

Section 14 : Main Project #2 Sales Regional Reporting Tool

Lecture 111 What You'll Learn (& Should be Aware of 3:42
Lecture 112 Project Overview Regional Sales Reporting Tool 13:55
Lecture 113 Importing Data from Multiple Files using MultiSelect 16:39
Lecture 114 Setting up a Template as the Basis for Different Reports 25:5
Lecture 115 Exporting Reports as Excel files (Pivot Table & Hard Coded Reports) 16:47
Lecture 116 Fine-tuning Report Export Procedure (Double-check if user wants to overwrite) 10:51
Lecture 117 Setting up Automatic Export of Text File (Fix delimiter in CSV to Semicolon) 8:15
Lecture 118 Track the List of Tasks Completed with VBA 11:2
Lecture 119 Final Touches & Creating Interface to Hide Specific Tabs with a Password 10:52
Lecture 120 Key Takeaways & Tips for VBA based Excel Tools 2:24
Lecture 121 Second Milestone Completed! Text

Section 15 : Interacting with Other Applications (Word, PowerPoint etc

Lecture 122 Overview & Important Concepts when Interacting with Other Applications 4:46
Lecture 123 Project PDF Save Specific Sheets as a PDF File 13:58
Lecture 124 Project Email Automatically Create Personalized Emails with Attachments 8:52
Lecture 125 Project Microsoft Word Create Personalized Letters in Word from Excel 15:55
Lecture 126 Project Microsoft PowerPoint Export Excel Sheets to PowerPoint Presentation 18:53
Lecture 127 Key Takeaways when Working with other Applications Text

Section 16 : Workbook and Worksheet Events

Lecture 128 Overview & What are Event Procedures 2:51
Lecture 129 Workbook Events, Toggle Full Screen (Activate, Deactivate, Open, Close) 12:40
Lecture 130 Worksheet Selection Change Dynamic Font Color of Selected Range 5:37
Lecture 131 Refresh PivotTables Automatically & Concept of Range Intersection 12:27
Lecture 132 Resetting a Dependent Drop-down when the first Drop-down is Changed 10:41
Lecture 133 Activity Link Page Header to Cell Value (BeforePrint Event) Text
Lecture 134 INTRODUCTION TO BRAINMEASURES PROCTOR SYSTEM Pdf
Lecture 135 About Certification Pdf

Section 17 : Working with Userforms & ActiveX Controls

Lecture 136 Overview UserForms & ActiveX Controls 1:38
Lecture 137 ActiveX Controls What you Need to Know 7:18
Lecture 138 ActiveX Check Box to Fix Scroll Area & Toggle Screen Settings 7:40
Lecture 139 ActiveX to Show and Hide Help
Lecture 140 ActiveX Combo Box for Easy Sheet Navigation (Great for Larger Workbooks) 10:16
Lecture 141 ActiveX to Show or Hide Other ActiveX controls (eg 13:33
Lecture 142 UserForm Basics What You Need to Get Started 20:1
Lecture 143 UserForm for Data Entry (Input & Check Boxes) 13:56
Lecture 144 UserForm to Document External Links & Comments (Combo Box & Option button) 20:21
Lecture 145 Bonus ListBox to Print Multiple Pages in ONE GO 10:41
Lecture 146 UserForm Checklist & Course Map Status Text

Section 18 : Main Project #3 Invoice Generation Tool

Lecture 147 Tool Overview What You Will Learn 13:52
Lecture 148 Setting up an Input UserForm to Collect Master Data 13:52
Lecture 149 Working with Multi-Column ListBoxes
Lecture 150 Edit Master Data with UserForm 7:31
Lecture 151 Searchable ListBox (with Selection Change event) 17:6
Lecture 152 Enhanced Searchable ListBox Write Selection back to Excel Table 16:21
Lecture 153 MultiPage UserForm to Create Invoices & Email (Setup) 18:17
Lecture 154 Export PDF & Excel Workbook for Each Invoice based on ListBox Selection 23:32
Lecture 155 Create Email in Outlook & Attach PDF Invoice 14:15
Lecture 156 Final Updates & Debugging 19:4
Lecture 157 Last Milestone Completed! Text

Section 19 : Create your own Custom Functions

Lecture 158 Section Overview Function Procedures 1:51
Lecture 159 Setting up a Simple Function (including Optional Arguments) 5:34
Lecture 160 Function to Calculate Age (Result in Years & Months) 5:48
Lecture 161 Functions that Split Text and Numbers from Cell Value 8:15
Lecture 162 Using Functions in Sub Procedures 6:5
Lecture 163 Activity Function That Sums based on Cell Color Text
Lecture 164 Project Function That Sums based on Cell Color 10:46
Lecture 165 Key Takeaways Function Procedures in VBA & Course Map Status Text

Section 20 : Working with Charts

Lecture 166 The Chart Object Library & Section Overview 3:5
Lecture 167 Create and Modify a Chart with VBA 12:46
Lecture 168 Animated Charts Different Methods
Lecture 169 Show Charts inside UserForms 13:6
Lecture 170 Key Takeaways Charts Text

Section 21 : Summary & Final Words

Lecture 171 Your Next Steps & more Learning 1:45