Unlock Excel VBA and Excel Macros by Udemy
Automate Complex Tasks with Microsoft Excel VBA & Excel Macros (Real-World Projects included). Achieve More. Save time.
Course Highlights
- Enhance your current Excel files & automate your work with VBA.
- Master Excel VBA & Macros
- Directly apply the Excel VBA example files included to your own work.
- Reduce manual routine tasks to the click of a button.
- Make your work-life easier.
- Create simple solutions to complex problems by levering Excel functionality with Macros.
- Formulas & Excel core functionality can't do everything. Use VBA when you reach a limitation.
- Easily adjust VBA code you find online to fit your purpose.
Skills you will learn!
Curriculum
5 Topics
Course Structure & Content
Important Compatibility & Setup Information
Course Resources: Download Course Project Files
Course Expectations
Course Outline for Quick Reference
10 Topics
Overview: Macros & Visual Basic Editor (VBE)
Steps to Your First Macro Recording
Visual Basic Editor: What You Need to Know
Project: Dynamic Cell Selection (Special Cells) - Perfect for Flexible Macros
Project: Copy (Consolidate) Data - Absolute versus Relative Macro Recording
7 Ways to Run Macros / VBA code (incl. creative & modern buttons)
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
9 Topics
Overview: VBA Object Model (What you need to become an expert in VBA)
VBA's Code Reference Object Library
VB Color Guidelines & Keyboard Shortcuts
Excel VBA Object Model
Object Properties
Object Methods
How to Find the Object Property & Method
Test Your Knowledge about the Object Model
Conclusion: Key Takeaways & Message from Me
10 Topics
Overview: Referencing Ranges Worksheets & Workbooks
Referring to Ranges & Writing to Cells in VBA
Most Useful Range Properties & Methods
4 Methods to Find the Last Row of your Range
Copying & resizing a variably sized range
Properly Referencing Worksheets
Properly Referencing Workbooks
Activity: Save a Hard-coded Copy of Workbook
Project: Save Hard-coded Copy & Macro-free version of workbook
Key Takeaways for Referencing Ranges
8 Topics
Overview: Role of Assignment & Variables
Data Types for Better Performance
Declaring Variables Arrays & Constants (Role of Option Explicit)
Using Object Variables (Set statement)
Scoping Variables
Activity: Revisit Hard-coded Workbook Project & Use Variable Assignment
Test Your Knowledge on Variables and data types
Key Takeaways for Working with Variables
10 Topics
Overview: Why Collections are a VBA Essential
With & End With for Easier Code Writing
For Each to Loop Through Collections (sheets ranges etc.) in one go
IF Then (Else ElseIF) for Conditional Outcomes
Select Case as Alternative for Many IF Statements
Goto Statement to Change Program Flow
Activity: Get the Number of Formulas on the worksheet
Project: Get the Number of Formulas on the Worksheet
Bonus: Unhide All Sheets in One Go
Key Takeaways for Collections & Decision Making in VBA
8 Topics
Overview: VBA versus Worksheet Functions
Most Useful VBA Functions
Message Box (also with Yes No buttons)
Input Box (VBA InputBox Function)
Input Box that can Select Ranges (Excel InputBox Method)
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
Key Takeaways Built-in Functions
9 Topics
Overview: Debugging Handling Errors & Procedure Scope
Debugging Options: Watch Locals Immediate Windows & More
Error Handling: Different Methods
Faster Code by Suppressing Pop-ups & Flickering Screen
Procedure Scope & Running one Sub from Another
Passing Arguments to Sub Procedures (By Ref By Val)
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
Key Takeaways Debugging Error Handling & Course Map Status
7 Topics
Project overview: Table of Contents (TOC) with one click
TOC Project: The Basics
TOC Project: Adding Hyperlinks
TOC Project: Testing the VBA Code
TOC Project: Adding Error Handling & More Checks
TOC Project: Adding to Personal Macro Workbook (Make it Available to all Files)
First Milestone Completed!
12 Topics
Section Overview: Looping in VBA
For Next Counter Loops (Simple Example)
For Next Nested Loop (Loop through Text & Cells)
For Next Reverse Loop (Delete Filtered/Hidden Rows)
Do Until & Do While Loop
Practical Example of Do Loop (with Inputbox)
Find Method for Quicker Lookup Results
Find Method with Do Loop - Multiple Matches
Add a Timer to Test & Speech to Inform
Activity: Document all Comments in Workbook in a Sheet
Project: Document all Comments in Workbook in a Sheet
Useful VBA Statements & Key takeaways for Looping in VBA
9 Topics
Overview: Working with Arrays in VBA
One Dimensional Arrays (and transferring back to sheet)
Dynamic Arrays (size adjusts depending on a condition)
Preserving Dynamic Arrays (adding more elements to the existing array)
Two Dimensional Arrays
Variant Arrays: Quick Method to Write to Cells
Activity: Create a New Worksheet per Company with Specific Information
Project: Create a New Worksheet per Company with Specific Information
Key Takeaways Arrays
9 Topics
Overview: Working with Folders & Files
DIR Function: Check if Files or Folders Exist
GetOpenFileName Method: User Selects a File
FileDialog Property: Loop Inside a Folder
Create CSV File from Data in Excel
Printing & Writing to Text Files for More Control
Activity: Export Data to Text file (fix Delimiter to Semicolon)
Project: Export Data to Text file (fix Delimiter to Semicolon)
Key Takeaways Files & Folders
7 Topics
Overview: (Pivot) Tables & Formulas
Using Excel Formulas in VBA
Excel Tables: Use the Right Syntax
Pivot Tables & VBA: What you Need to Know
Activity: Update Existing Formulas with VBA by Adding IFERROR
Project: Update Existing Formulas with VBA by Adding IFERROR
Key Takeaways (Pivot) Tables & Formula
11 Topics
What You'll Learn (& Should be Aware of..)
Project Overview: Regional Sales Reporting Tool
Importing Data from Multiple Files using MultiSelect
Setting up a Template as the Basis for Different Reports
Exporting Reports as Excel files (Pivot Table & Hard Coded Reports)
Fine-tuning Report Export Procedure (Double-check if user wants to overwrite)
Setting up Automatic Export of Text File (Fix delimiter in CSV to Semicolon)
Track the List of Tasks Completed with VBA
Final Touches & Creating Interface to Hide Specific Tabs with a Password
Key Takeaways & Tips for VBA based Excel Tools
Second Milestone Completed!
6 Topics
Overview & Important Concepts when Interacting with Other Applications
Project PDF: Save Specific Sheets as a PDF File
Project Email: Automatically Create Personalized Emails with Attachments
Project Microsoft Word: Create Personalized Letters in Word from Excel
Project Microsoft PowerPoint: Export Excel Sheets to PowerPoint Presentation
Key Takeaways when Working with other Applications
8 Topics
Overview & What are Event Procedures?
Workbook Events Toggle Full Screen (Activate Deactivate Open Close)
Worksheet Selection Change: Dynamic Font Color of Selected Range
Refresh PivotTables Automatically & Concept of Range Intersection
Resetting a Dependent Drop-down when the first Drop-down is Changed
Activity: Link Page Header to Cell Value (BeforePrint Event)
Project: Link Page Header to Cell Value (BeforePrint Event)
2 Useful Tips when Working with Events & Key Takeaways
11 Topics
Overview: UserForms & ActiveX Controls
ActiveX Controls: What you Need to Know
ActiveX Check Box to Fix Scroll Area & Toggle Screen Settings
ActiveX to Show and Hide Help
ActiveX Combo Box for Easy Sheet Navigation (Great for Larger Workbooks)
ActiveX to Show or Hide Other ActiveX controls (eg. Actual & Budget reporting)
UserForm Basics: What You Need to Get Started
UserForm for Data Entry (Input & Check Boxes)
UserForm to Document External Links & Comments (Combo Box & Option button)
Bonus: ListBox to Print Multiple Pages in ONE GO
UserForm Checklist & Course Map Status
11 Topics
Tool Overview: What You Will Learn
Setting up an Input UserForm to Collect Master Data
Working with Multi-Column ListBoxes
Edit Master Data with UserForm
Searchable ListBox (with Selection Change event)
Enhanced Searchable ListBox: Write Selection back to Excel Table
MultiPage UserForm to Create Invoices & Email (Setup)
Export PDF & Excel Workbook for Each Invoice based on ListBox Selection
Create Email in Outlook & Attach PDF Invoice
Final Updates & Debugging
Last Milestone Completed!
8 Topics
Section Overview: Function Procedures
Setting up a Simple Function (including Optional Arguments)
Function to Calculate Age (Result in Years & Months)
Functions that Split Text and Numbers from Cell Value
Using Functions in Sub Procedures
Activity: Function That Sums based on Cell Color
Project: Function That Sums based on Cell Color
Key Takeaways Function Procedures in VBA & Course Map Status
5 Topics
The Chart Object Library & Section Overview
Create and Modify a Chart with VBA
Animated Charts: Different Methods
Show Charts inside UserForms
Key Takeaways: Charts
2 Topics
Your Next Steps & more Learning
SPECIAL BONUS: Discover Exciting Offers!
Unlock Excel VBA and Excel Macros
Related Excel VBA Courses
Thank You!
Your review has been submitted successfully.