Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Unlock Excel VBA & Excel Macros
Introduction & Scope
Course Structure & Content (4:19)
⚙️How to Use the Platform for Best Learning Experience
Important Compatibility & Setup Information (4:00)
🔻Course Resources: Download Course Project Files (1:56)
Course Expectations (1:34)
🔍 Looking for a Specific Topic? Use the Transporter!
🤔 Quick Check-in
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 in Excel Comments to Notes
Activity: Record a Macro that Deletes all Notes/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)
🆕 Enjoying the Course so far? Refer a friend and get 30% off your next XelPlus purchase!
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 overivew: 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
🎁 BONUS! CODE BOOK
🔻Download VBA CODE BOOK
💪 More Real-Life Projects & Solutions
Challenge 1 - Restrict Editing: Insert/Delete Columns in Protected Worksheet
Challenge 2 - Populate and Consolidate BOM
Challenge 3 - Web Scraping
Summary & Final Words
🎆 Your Next Steps & more Learning (2:12)
✍️ Leave a Review
🚩 Share Your Certificate
My Favorite Resources
Access to Bonus Items
For Each to Loop Through Collections (sheets, ranges etc.) in one go
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock