Automate Excel with VBA and Macros

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)

Reference Cells and Ranges

Advanced Range Selection

Never Select Cells Again

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

Offset Ranges

Powerful Range Methods You Must Know - Union, Resize, CurrentRegion and Intersect

Assignment Task - Ranges & Assignment Section Explanation

Assignment Answer - 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, Clear, and Move Values Around a Worksheet

Storing Data in the Macro

Copy and Paste Data

Working with Formulas

Insert/Delete Cells, Ranges, Rows & Columns

Additional Ways to Get/Input Data into the Worksheet

Assignment Task - Get & Input Data

Assignment Answer - Get & Input Data

Reference 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.

Move and Copy Worksheets

Common Worksheet Properties

The Proper Way to Work with Worksheets

Cross-Worksheet (and Workbook) Range Reference Concept - Very Important

Assignment Task - Worksheets

Assignment Answer - Worksheets

IF Statement - The Foundation of Decision Making

IF Statement Advanced

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!

Comparison Operators

Logical Operators - Allow Multiple Conditional Checks

Operator Precedence - Control What Occurs When

Assignment Task - Logic & Control

Assignment Answer - Logic & Control

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

Variable Types - A Guide

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 Task - Variables Introduction

Assignment Answer - Variables Introduction

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

Formula Input

Date Input

Select a Cell or Range from a Prompt

Get Multiple Inputs from the User

Assignment Task - Popup Windows & Input

Assignment Answer - Popup Windows & Input

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

Reference Workbooks

Reference the Current Workbook

Create a New Workbook - 3 Ways

Open Workbooks - Including Password Protected and Read Only

Save Workbooks - 4 Ways

Close Workbooks - Including All of Excel and With and Without Saving

Delete a Workbook

Work on New, Opened, and Other Workbooks - The Proper Way!

Check if a Workbook Exists and is Open - Prevent Errors

Assignment Task - Workbooks

Assignment Answer - Workbooks

What are Loops and How are they Useful?

Do Loops - Do While, Do Until, & Infinite

For Next Loop

For Each Loop

While Wend Loop

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 Task - Loops

Assignment Answer - Loops

Tables - The Simple Way

Tables - Advanced Guide (Everything You Need to Know)

Shapes - The Basic Guide

Shapes - Advanced Techniques & Working with Multiple Shapes

Shapes - Looping and Legacy Form Controls

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

The Completed Project

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

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 Task - Variables

Assignment Answer - Variables

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

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 Task - Arrays

Assignment Answer - Arrays

Error Handling

Make Better Decisions with Errors

Elegant Custom Errors and Messages

Assignment Task - Error Handling

Assignment Answer - Error Handling

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 Task - Automation

Assignment Answer - Automation

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 Task - Advanced Macro Control

Assignment Answer - Advanced Macro Control

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)

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 Task - Security

Assignment Answer - Security

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

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!

Your First UserForm

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