Introduction
9:39
Excel VBA Course Preview
First Steps
2:55
How to Use the Course
Your First Macro! (The VBA/Macro Intro.)
Quick Tip to Better Understand VBA Code
The Visual Basic Editor Interface (VBE)
The Basics of How VBA Works (Objects & The Object Browser)
The Macro Recorder (Good & Evil)
Working with Cells and Ranges
Reference Cells and Ranges
Learn New Commands on Your Own & Do Something Useful with Ranges (Macro Recorder + Object Browser)
Reference Rows and Columns
Practical Use of Better Range References and Number Formatting (On Imported Data)
Activate vs. Select - The Important Difference
User Selected Cells and Ranges
Powerful Range Methods You Must Know - Union, Resize, CurrentRegion and Intersect
Assignment 1 - Ranges
Assignment Task - Ranges & Assignment Section Explanation
Assignment Answer - Ranges
Practical Tasks - Ranges
Find the Next Empty or Last Row/Column (The Complete Guide)
Select Cells, Ranges, Rows and Columns within Other Ranges
The Proper Way to Work with Ranges (Advanced)
Get & Input Data into the Worksheet
Get, Input, Clear, and Move Values Around a Worksheet
Storing Data in the Macro
Insert/Delete Cells, Ranges, Rows & Columns
Additional Ways to Get/Input Data into the Worksheet
Assignment 2 - Get & Input Data
Assignment Task - Get & Input Data
Assignment Answer - Get & Input Data
Working with Worksheets
Additional Method to Reference Worksheets
Navigate to and Select Worksheets
Work on the Currently Visible Worksheet
Reference Cells and Ranges on Other Worksheets
Adding and Removing Worksheets
Work on New Worksheets - Rename, Add Data, etc.
Common Worksheet Properties
The Proper Way to Work with Worksheets
Cross-Worksheet (and Workbook) Range Reference Concept - Very Important
Assignment 3 - Worksheets
Assignment Task - Worksheets
Assignment Answer - Worksheets
Add Logic and Control to Macros
IF Statement - The Foundation of Decision Making
Select Case Statement - Make More Readable Decisions
With Statement - Easier Code Writing
GoTo Statement - The correct way to use this!
Simplify Complex IF Statements
Case Insensitive Checks - Control How VBA Compares Text
Arithmetic Operators - Let's Do Math!
Logical Operators - Allow Multiple Conditional Checks
Operator Precedence - Control What Occurs When
Assignment 4 - Logic & Control
Assignment Task - Logic & Control
Assignment Answer - Logic & Control
Variables Introduction
What are Variables and How to Use Them
Option Explicit - Forced Declarations - When, How, and Why to Use It
How to Name Variables - Helpful Tips and Common Pitfalls
Declaring Variables - Rules, Tips, and Shortcuts
Object Variables - Part 1 - Ranges, Worksheets, and Workbooks
Object Variables - Part 2 - Set to Nothing or Not
Helpful Shortcuts for Working with Variables
Assignment 5 - Variables Introduction
Assignment Task - Variables Introduction
Assignment Answer - Variables Introduction
Popup Windows & User Input
Message Boxes with Buttons, Formatting, and Style
Message Box Button Clicks - Making Decisions from Message Boxes
InputBox - Simple User Input
Get Input - The Better Way - Part 1 - Introduction
Get Input - The Better Way - Part 2 - More Features and Caveats
Select a Cell or Range from a Prompt
Get Multiple Inputs from the User
Assignment 6 - Popup Windows & Input
Assignment Task - Popup Windows & Input
Assignment Answer - Popup Windows & Input
Practical Tasks - Searching, Worksheet Functions & More
Validate Data & User Input with VBA
Search for Values in Excel - Single Match or All Matches
Advanced Searching for Values - Entire Workbook Search, Wildcards, Formatting Search and Last Row Search
Using Worksheet Functions in Macros (Simple & Advanced)
Special Cell Magic - Finding/Deleting Empty Rows, Working with Filtered Data & More
Calling Other Macros and Passing Values - An Introduction
Working with Workbooks
Reference the Current Workbook
Create a New Workbook - 3 Ways
Open Workbooks - Including Password Protected and Read Only
Close Workbooks - Including All of Excel and With and Without Saving
Work on New, Opened, and Other Workbooks - The Proper Way!
Check if a Workbook Exists and is Open - Prevent Errors
Assignment 7 - Workbooks
Assignment Task - Workbooks
Assignment Answer - Workbooks
Loops
What are Loops and How are they Useful?
Do Loops - Do While, Do Until, & Infinite
Nesting Loops - Controlling Loops within Loops
Stop Loops Early - User Controlled, Automatic, and Nested
Advanced Loop Condition Checks
Loop through Cells, Worksheets, and Workbooks using Collections - The Proper Way
Assignment 8 - Loops
Assignment Answer - Loops
Practical Tasks - Tables & Shapes (The Ultimate Guide)
Tables - Advanced Guide (Everything You Need to Know)
Shapes - Advanced Techniques & Working with Multiple Shapes
Shapes - Looping and Legacy Form Controls
Project: The Task Manager
Introduction to the Project
Planning the Project's Coding and Visuals (A Design Framework)
Visuals Creation - Professional Tables, Rounded Corners, Stylish Buttons
The First Code - How to Build More Complex Macros and Code Structures
How to Save Time by Reusing Code Safely
User-Friendly Support Functions - Viewing, Refreshing, Clearing Values and More
Adding Dynamic Buttons to Entries in a Table to Manage Them - Edit and Delete
Protecting Everything using VBA
Practical Tasks - User Input Applications & File Selections
Simple System for Using a Default Input Value and Validating It
Force the User to Input Something - 2 Ways
Require Single Cell Selection - And How to Structure More Advanced Code
Zero and "False" Inputs - Avoiding Common Errors
Let the User Choose Which Files to Work With - Including Filtered Selections
Multiple File Selection - How to Allow it and Work with The Files
Let the User Choose Where to Save Files and Under What Name and Type
Variables - Unlocking Their Power
Variable Scope - Project Variables, Module Variables, and Procedure Variables
Precedence - Controlling Variables with the Same Name
Constants - Hardcoding for Strength
Static - Keep My Variables Alive
Variable Default Values - How to Check for Them and Reset Them
Determine Variable Type - Enables Better Coding
Sheets vs. Worksheets - What You Need to Know
Assignment 9 - Variables
Assignment Task - Variables
Assignment Answer - Variables
Practical Tasks - Debugging Your Macros & VBA
Breaking Apart Macros for Debugging
Analyzing Variables with the Locals and Watch Windows
Quick and Powerful Checks with the Immediate Window
Option Explicit and Compile VBA Project
Stop a Long-Running or Frozen Macro
Arrays in VBA - The Power Variable
What is an Array in VBA and How to Make Your First Array
Static Arrays - You Decide the Size
Dynamic Arrays Part 1 - Increased Flexibility
Dynamic Arrays Part 2 - Dynamic Resizing
Multi-Dimensional Arrays - First Step to Working with Ranges in Arrays
Arrays & Ranges Part 1 - Super Fast Range Evaluation
Arrays & Ranges Part 2 - Dynamic Ranges & Single Cell Selection
Easy Array Looping - Look but Don't Touch
Helpful Array Features - Clearing, Checking, Splitting, Joining and Option Base
Assignment 10 - Arrays
Assignment Answer - Arrays
Error Handling
Make Better Decisions with Errors
Elegant Custom Errors and Messages
Assignment 11 - Error Handling
Assignment Task - Error Handling
Assignment Answer - Error Handling
Automating Macros
How to Make Macros Run Automatically
Workbook Automation Events - Opening, Closing, Saving and Navigating Worksheets
Worksheet Automation Events - Activating, Selecting, Changing Data and Navigating Worksheets
ME to the Rescue - Making Life Easier
Run a Macro When a Specific Cell, Range, Row or Column Changes
Prevent Endless Loops with Error Handling in Events - The Better Way
Highlight the Selected Cell
Assignment 12 - Automation
Assignment Task - Automation
Assignment Answer - Automation
Advanced Macro Control & Custom Functions
Calling Other Macros - Passing Required, Optional, and Default Arguments, Values, and Arrays
Create Custom VBA Functions that Return Values to a Macro
ByRef vs ByVal - Learn How to Keep Your Data Safe
Add More Power with Advanced Arguments & Result Control
Create Custom Excel Worksheet Functions - User Defined Functions (UDF)
Public vs Private Procedures - How to Hide Your Macros
Enums - Manage Custom Lists and Macro Argument Inputs with Ease
Large Project Management, Documentation & When to Make New Procedures (Code Abstraction)
Assignment 13 - Advanced Macro Control
Assignment Task - Advanced Macro Control
Assignment Answer - Advanced Macro Control
Practical Tasks - Pivot Tables, Filtering & Sorting
PivotTables - What You Need to Know!
PivotTables - Filtering and Loops (Dates Included)
PivotTables - Slicers and Timelines (The Guide)
Filtering and Sorting - The How To Guide
Filtering and Sorting - Date Filters (Basic to Advanced - Rolling Dates & More)
Security in VBA & Excel
Password Protect Macros and VBA Projects
Protect a Worksheet - Advanced Options & Avoiding Errors
Making Admin Worksheets (With Password Protection)
Verify User Input or Undo It
Don't Let the User Leave a Worksheet Until They Complete a Task
Force the User to Enable Macros to use the Workbook
Assignment 14 - Security
Assignment Task - Security
Assignment Answer - Security
Practical Tasks - Import, Export & Email Ultimate Guide
Importing into Excel - The Easy Way
Advanced File Import - Super Fast for Smaller Files
Advanced File Import - Method Required for Large Files
Advanced File Import - Using the File System Object (FSO)
Data Import Analysis Guide
Building a Robust Data Import Parser (Filtering Before Import, Excluding Rows, & More)
Export to PDF & Managing Print Settings - Entire Workbook, Selected Worksheets or Specific Ranges
Export TXT, CSV & Worksheets (A Professional System that Ensures Data Integrity)
Export PivotTables (The Proper Way) + Unique File Generation Trick
Email from Excel (The Full Guide) - With Multiple Recipients, Attachments, HTML, Signatures, Email Error
Handling & More
Project: Bringing It All Together - Data Import, Visualization, and Export
Introduction to the Project
Sample Import Files to Use for this Project
How to Manage Large Project Creation & Planning
Create Visual Interfaces for Better Programming
Advanced Code Structures for Larger Projects & Address Variable Usage
How to Build & Test Larger Macros with Ease
Robust Function Development & Real-World Debugging Examples
Building Helper Features for Your Project
Advanced Date Programming
Using Wrapper Functions to Make More Maintainable Code
Dynamic File Creation, Deletion & Cleaning Procedures
Create and Manage Custom Reports, Worksheet Configurations, and Exports
Protecting Against Bad User Inputs
Automatically Email Auto-Generated Custom Reports to Lists of People with the Click of a Button
Professional Integration of the Visual Interface with the Backend Programming
Error Handling System for Large Projects
Security - Building a Password Protection Control System & Protected Access Container
Adding Professional Documentation - The 4 Pillars of Documenting Your Code
Congratulations Rockstar!
(Bonus) UserForms - Build a Fully Featured Form for Easy Data Entry
More UserForm Controls & Require/Validate Form Input
Advanced ComboBox and ListBox Usage - Multiple Selections & Columns & Dependent Menus
Multiple Tabs, Scrollable Forms, & Paragraph Style Inputs
Usability Tips for Professional UserForms - Control Protection, Default Values and More
ActiveX & Form Controls in the Worksheet