Self-Paced Course
Microsoft Excel: VBA & Macros
Automate tasks, build interactive tools, and develop your own custom Excel applications using Visual Basic for Applications (VBA).
Course Hours38 hours
Skills Learned
Data Prep
Data Analysis
Tools
Excel
Course Level
Advanced
Credentials
Course Description
For Excel users looking to push the limits, VBA (Visual Basic for Applications) is an incredibly valuable tool. This programming language allows you to automate tasks, integrate custom forms and controls, and even interact with other Microsoft applications like Word, Outlook and Powerpoint -- all from within Excel.
But VBA isn't just for programmers or hardcore developers; in this course, we'll teach you the core principles of VBA from the ground up. We'll review basic syntax, practice generating code using the macro recorder, and work through more than 50 real-world use cases, from importing/exporting data to generating automated reports and invoices.
If you're looking to automate your workflow, develop fully custom solutions, and become a certified Excel power user, this is the course for you.
COURSE CURRICULUM:
Course Outline
- Course Structure & Outline
- Important Compatibility & Setup Information
- Getting to Know the Project Files
- DOWNLOAD: Course Resources
- Setting Expectations
- 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)
- Project: Absolute vs. Relative Macro Recording
- 7 Ways to Run Macros / VBA code
- Activity: Record a Macro that Deletes all Comments
- What's the Difference Between Macros and VBA?
- QUIZ: Intro to Macros
- Overview: VBA Object Model
- 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
- Conclusion: Key Takeaways
- QUIZ: The Object Model
- 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 a 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: Referencing Ranges
- Overview: Role of Assignment & Variables
- Data Types for Better Performance
- Declaring Variables, Arrays & Constants
- Setting Object Variables
- Scoping Variables
- Activity: Revisit Hard-coded Workbook Project
- Key Takeaways: Working with Variables
- QUIZ: Variables and Data Types
- Overview: Why Collections are a VBA Essential
- With & End With for Easier Code Writing
- For Each to Loop Through Collections
- 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: Un-hiding All Sheets in One Go (3 Methods)
- Key Takeaways: Collections & Decision Making in VBA
- Overview: VBA versus Worksheet Functions
- Most Useful VBA Functions
- Message Box Options
- Input Box (VBA InputBox Function)
- Input Box that can Select Ranges
- Activity: Show Top 3 Values in a Message Box Based on Range
- Project: Show Top 3 Values in a Message box based on Range
- Key Takeaways Built-in Functions
- 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: Number of Formulas or Comments Used in a Workbook
- Project: Number of Formulas or Comments Used in a Workbook
- Key Takeaways: Debugging & Error Handling
- Project overview: Table of Contents (TOC)
- 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
- First Milestone Completed!
- Overview: Looping in VBA
- For Next Counter Loops
- For Next Nested Loop
- 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
- Key Takeaways: Looping in VBA
- Overview: Working with Arrays in VBA
- One Dimensional Arrays
- Dynamic Arrays
- Preserving Dynamic Arrays
- Two Dimensional Arrays
- Variant Arrays: Quick Method to Write to Cells
- Activity: Create a New Worksheet per Company
- Project: Create a New Worksheet per Company
- Key Takeaways: Arrays
- 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
- Project: Export Data to Text file
- Key Takeaways: Files & Folders
- 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 with IFERROR
- Project: Update Existing Formulas with VBA with IFERROR
- Key Takeaways: Tables, Pivots & Formulas
- What You'll Learn
- 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
- Fine-tuning Report Export Procedure
- Setting up Automatic Export of Text File
- Track the List of Tasks Completed with VBA
- Final Touches & Creating Interface to Hide Tabs with a Password
- Key Takeaways: VBA-based Excel Tools
- Second Milestone Completed!
- Overview & Important Concepts
- PDF: Save Specific Sheets as a PDF File
- Email: Automatically Create Personalized Emails with Attachments
- Word: Create Personalized Letters in Word from Excel
- PowerPoint: Export Excel Sheets to PowerPoint Presentation
- Key Takeaways: Working with other Applications
- Overview: 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 Dependent Drop-down Lists
- Activity: Link Page Header to Cell Value
- Project: Link Page Header to Cell Value
- Key Takeaways: Workbook & Worksheet Events
- 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
- ActiveX to Show or Hide Other ActiveX controls
- UserForm Basics: What You Need to Get Started
- UserForm for Data Entry (Input & Check Boxes)
- Practical UserForm to Document External Links & Comments
- UserForm Checklist & Course Map Status
- 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 Invoice based on ListBox Selection
- Create Email in Outlook & Attach PDF Invoice
- Final Updates & Debugging
- Last Milestone Completed!
- Section Overview: Function Procedures
- Setting up a Simple Function
- Function to Calculate Age
- Functions that Split Text and Numbers from Cell Value
- Using Functions in Sub Procedures
- Activity: Sum based on Cell Color
- Project: Sum based on Cell Color
- Key Takeaways: Function Procedures in VBA
- Overview: The Chart Object Library
- Create and Modify a Chart with VBA
- Animated Charts: Different Methods
- Show Charts inside UserForms
- Key Takeaways: VBA Charts
- Course Feedback Survey
- Share the love!
- Next Steps
WHO SHOULD TAKE THIS COURSE?
- Excel users hoping to automate tedious and time-consuming tasks (formatting workbooks, transforming data, producing files, etc)
- Analysts seeking to improve productivity, minimize human error, and produce reports and analyses more efficiently
- Programmers looking to develop their own custom Excel solutions
WHAT ARE THE COURSE REQUIREMENTS?
- Microsoft Excel for PC/Windows, compatible with VBA (ideally 2007+)
- Familiarity with core Excel functionality is strongly recommended
Start learning for FREE, no credit card required!
Every subscription includes access to the following course materials
- Interactive Project files
- Downloadable e-books
- Graded quizzes and assessments
- 1-on-1 Expert support
- 100% satisfaction guarantee
- Verified credentials & accredited badges
Ready to become a
data rockstar?
Start learning for free, no credit card required!