Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Unlock Excel VBA & Excel Macros
Introduction & Scope
Course Structure & Content (4:19)
Important Compatibility & Setup Information (4:00)
Course Resources: Download Course Project Files (1:56)
Course Expectations (1:34)
Section 2: Your First Excel Macro
Overview: Macros & Visual Basic Editor (VBE) (1:40)
Steps to Your First Macro Recording (6:01)
Visual Basic Editor: What You Need to Know (11:56)
Project: Dynamic Cell Selection (Special Cells) - Perfect for Flexible Macros (11:28)
Project: Copy (Consolidate) Data - Absolute versus Relative Macro Recording (9:33)
7 Ways to Run Macros / VBA code (incl. creative & modern buttons) (12:24)
OFFICE 365 Update: Change from Excel Comments to Notes
Activity: Record a Macro that Deletes all Comments
What's the Difference Between Macros and VBA?
Quiz: Test Your Knowledge on Macros
Section 3: The Object Model
Overview: VBA Object Model (What you need to become an expert in VBA) (1:39)
VBA's Code Reference Object Library (4:12)
VB Color Guidelines & Keyboard Shortcuts (12:09)
Excel VBA Object Model (6:17)
Object Properties (4:36)
Object Methods (5:54)
How to Find the Object, Property & Method (8:16)
Conclusion: Key Takeaways & Message from Me (2:58)
Quiz: Test Your Knowledge about the Object Model
Section 4: Referencing Ranges, Worksheets & Workbooks with VBA
Overview: Referencing Ranges, Worksheets & Workbooks (1:12)
Referring to Ranges & Writing to Cells in VBA (15:37)
Most Useful Range Properties & Methods (13:19)
4 Methods to Find the Last Row of your Range (15:34)
Copying & resizing a variably sized range (9:28)
Properly Referencing Worksheets (14:50)
Properly Referencing Workbooks (10:21)
Activity: Save a Hard-coded Copy of Workbook
Project: Save Hard-coded Copy & Macro-free version of workbook (12:24)
Key Takeaways for Referencing Ranges (3:03)
Section 5: Working with Variables
Overview: Role of Assignment & Variables (2:54)
Data Types for Better Performance (3:33)
Declaring Variables, Arrays & Constants (Role of Option Explicit) (12:52)
Using Object Variables (Set statement) (6:50)
Scoping Variables (6:58)
Activity: Revisit Hard-coded Workbook Project & Use Variable Assignment
Quiz: Test Your Knowledge on Variables and data types
Key Takeaways for Working with Variables (2:15)
Section 6: Looping through Collections & Making Decisions
Overview: Why Collections are a VBA Essential (1:27)
With & End With for Easier Code Writing (6:21)
For Each to Loop Through Collections (sheets, ranges etc.) in one go (10:14)
IF Then (Else, ElseIF) for Conditional Outcomes (10:20)
Select Case as Alternative for Many IF Statements (5:21)
Goto Statement to Change Program Flow (7:37)
Activity: Get the Number of Formulas on the worksheet
Project: Get the Number of Formulas on the Worksheet (6:59)
Bonus: Un-hiding All Sheets in One Go (3 Methods) (10:09)
Key Takeaways for Collections & Decision Making in VBA
Section 7: Useful Built-in Functions
Overview: VBA versus Worksheet Functions (13:38)
Most Useful VBA Functions (10:05)
Message Box (also with Yes, No buttons) (8:43)
Input Box (VBA InputBox Function) (7:27)
Input Box that can Select Ranges (Excel InputBox Method) (12:28)
Activity: Show Top 3 Values in a Message Box Based on Range Selected
Project: Show Top 3 Values in a Message box based on Range Selected (11:19)
Key Takeaways Built-in Functions
Section 8: Debugging, Error Handling & Procedure Scope
Overview: Debugging, Handling Errors & Procedure Scope (2:00)
Debugging Options: Watch, Locals, Immediate Windows & More (13:10)
Error Handling: Different Methods (10:47)
Faster Code by Suppressing Pop-ups & Flickering Screen (10:39)
Procedure Scope & Running one Sub from Another (6:23)
Passing Arguments to Sub Procedures (By Ref, By Val) (9:50)
Activity: Get the Total Number of Formulas (or Comments) Used in a Workbook
Project: Get the Total Number of Formulas (or Comments) Used in a Workbook (11:43)
Key Takeaways Debugging, Error Handling & Course Map Status (1:57)
Main Project #1: Create a Tool to Automate Table of Contents (TOC)
Project overview: Table of Contents (TOC) with one Click (2:01)
TOC Project: The Basics (10:33)
TOC Project: Adding Hyperlinks (6:11)
TOC Project: Testing the VBA Code (4:29)
TOC Project: Adding Error Handling & More Checks (13:29)
TOC Project: Adding to Personal Macro Workbook (Make it Available to all Files) (7:09)
First Milestone Completed!
Section 10: Looping in VBA (Controlling the Flow of Code)
Section Overview: Looping in VBA (2:28)
For Next Counter Loops (Simple Example) (8:49)
For Next Nested Loop (Loop through Text & Cells) (15:35)
For Next Reverse Loop (Delete Filtered/Hidden Rows) (8:37)
Do Until & Do While Loop (9:57)
Practical Example of Do Loop (with Inputbox) (3:22)
Find Method for Quicker Lookup Results (9:02)
Find Method with Do Loop - Multiple Matches (6:24)
Add a Timer to Test & Speech to Inform (7:56)
Activity: Document all Comments in Workbook in a Sheet
Project: Document all Comments in Workbook in a Sheet (12:31)
Useful VBA Statements & Key takeaways for Looping in VBA (3:30)
Section 11: Working with Arrays
Overview: Working with Arrays in VBA (1:44)
One Dimensional Arrays (and transferring back to sheet) (13:42)
Dynamic Arrays (size adjusts depending on a condition) (6:40)
Preserving Dynamic Arrays (adding more elements to the existing array) (3:56)
Two Dimensional Arrays (7:35)
Variant Arrays: Quick Method to Write to Cells (8:39)
Activity: Create a New Worksheet per Company with Specific Information
Project: Create a New Worksheet per Company with Specific Information (9:36)
Key Takeaways Arrays
Section 12: Working with Files, Folders & Text Files
Overview: Working with Folders & Files (2:46)
DIR Function: Check if Files or Folders Exist (9:12)
GetOpenFileName Method: User Selects a File (9:27)
FileDialog Property: Loop Inside a Folder (16:50)
Create CSV File from Data in Excel (7:36)
Printing & Writing to Text Files for More Control (6:47)
Activity: Export Data to Text file (Fix Delimiter to Semicolon)
Project: Export Data to Text file (Fix Delimiter to Semicolon) (13:10)
Key Takeaways Files & Folders
Section 13: Excel Tables, Formulas & Pivot Tables
Overview: (Pivot) Tables & Formulas (2:10)
Using Excel Formulas in VBA (13:46)
Excel Tables: Use the Right Syntax (14:19)
Pivot Tables & VBA: What you Need to Know (18:30)
Activity: Update Existing Formulas with VBA by Adding IFERROR
Project: Update Existing Formulas with VBA by Adding IFERROR (8:14)
Key Takeaways (Pivot) Tables & Formulas
Main Project #2: Sales Regional Reporting Tool
What You'll Learn (& Should be Aware of..) (3:58)
Project Overview: Regional Sales Reporting Tool (14:10)
Importing Data from Multiple Files using MultiSelect (16:55)
Setting up a Template as the Basis for Different Reports (25:20)
Exporting Reports as Excel files (Pivot Table & Hard Coded Reports) (17:01)
Fine-tuning Report Export Procedure (Double-check if user wants to overwrite) (11:05)
Setting up Automatic Export of Text File (Fix delimiter in CSV to Semicolon) (8:30)
Track the List of Tasks Completed with VBA (11:16)
Final Touches & Creating Interface to Hide Specific Tabs with a Password (11:06)
Key Takeaways & Tips for VBA based Excel Tools (2:30)
Second Milestone Completed!
Section 15: Interacting with Other Applications (Word, PowerPoint etc.)
Overview & Important Concepts when Interacting with Other Applications (5:02)
PDF: Save Specific Sheets as a PDF File (14:13)
Email: Automatically Create Personalized Emails with Attachments (9:08)
Microsoft Word: Create Personalized Letters in Word from Excel (16:10)
Microsoft PowerPoint: Export Excel Sheets to PowerPoint Presentation (19:08)
Key Takeaways when Working with other Applications
Section 16: Workbook and Worksheet Events
Overview & What are Event Procedures? (3:08)
Workbook Events, Toggle Full Screen (Activate, Deactivate, Open, Close) (12:57)
Worksheet Selection Change: Dynamic Font Color of Selected Range (5:52)
Refresh PivotTables Automatically & Concept of Range Intersection (12:45)
Resetting a Dependent Drop-down when the first Drop-down is Changed (10:57)
Activity: Link Page Header to Cell Value (BeforePrint Event)
Project: Link Page Header to Cell Value (BeforePrint Event) (8:19)
2 Useful Tips when Working with Events & Key Takeaways (3:27)
Section 17: Working with Userforms & ActiveX Controls
Overview: UserForms & ActiveX Controls (1:56)
ActiveX Controls: What you Need to Know (7:32)
ActiveX Check Box to Fix Scroll Area & Toggle Screen Settings (7:54)
ActiveX to Show and Hide Help (6:17)
ActiveX Combo Box for Easy Sheet Navigation (Great for Larger Workbooks) (10:30)
ActiveX to Show or Hide Other ActiveX controls (eg. Actual & Budget reporting) (13:50)
UserForm Basics: What You Need to Get Started (20:17)
UserForm for Data Entry (Input & Check Boxes) (14:06)
Practical UserForm to Document External Links & Comments (Combo Box & Option button) (20:37)
Bonus: ListBox to Print Multiple Pages in ONE GO (10:56)
UserForm Checklist & Course Map Status
Main Project #3: Invoice Generation Tool
Tool Overview: What You Will Learn (14:08)
Setting up an Input UserForm to Collect Master Data (17:34)
Working with Multi-Column ListBoxes (10:18)
Edit Master Data with UserForm (7:47)
Searchable ListBox (with Selection Change event) (17:23)
Enhanced Searchable ListBox: Write Selection back to Excel Table (16:36)
MultiPage UserForm to Create Invoices & Email (Setup) (18:35)
Export PDF & Excel Workbook for Each Invoice based on ListBox Selection (23:48)
Create Email in Outlook & Attach PDF Invoice (14:31)
Final Updates & Debugging (19:24)
Last Milestone Completed!
Section 19: Create your own Custom Functions
Section Overview: Function Procedures (2:02)
Setting up a Simple Function (including Optional Arguments) (5:49)
Function to Calculate Age (Result in Years & Months) (6:04)
Functions that Split Text and Numbers from Cell Value (8:31)
Using Functions in Sub Procedures (6:18)
Activity: Function That Sums based on Cell Color
Project: Function That Sums based on Cell Color (11:02)
Key Takeaways Function Procedures in VBA & Course Map Status
Section 20: Working with Charts
The Chart Object Library & Section Overview (3:21)
Create and Modify a Chart with VBA (12:59)
Animated Charts: Different Methods (12:47)
Show Charts inside UserForms (13:21)
Key Takeaways: Charts
Summary & Final Words
Your Next Steps & more Learning (2:12)
More from Maven Analytics
Faster Code by Suppressing Pop-ups & Flickering Screen
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock