Udemy

Platform

English

Language

Microsoft

Category

Unlock Excel VBA and Excel Macros

Automate Complex Tasks with Microsoft Excel VBA & Excel Macros (Real-World Projects included). Achieve More. Save time.

4.65 (24660 reviews)

106658

Students

22.5 hours

Content

Jan 2021

Last Update
$174.99
Regular Price

What you will learn

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.




Description

Go from Beginner to Microsoft Excel VBA Expert!

I'll take you step-by-step through engaging video tutorials and teach you everything you need to know to be able to use VBA to improve your work flow, save time and automate your current Excel projects. 

What differentiates this course from the other Microsoft Excel VBA & macros courses? It includes:

  1. Over 50 Microsoft Excel VBA & macro examples. These are solutions my clients have needed through the years. They are likely to be useful for you too.

  2. Using the right VBA syntax is the most difficult part. An important topic which isn't covered enough in other VBA courses. In this course you understand why you write things in certain ways.

  3. Create 3 Complete Excel VBA Tools. We go from idea to final product and cover each step.

  4. Over 100 pages of documentation (including useful code) so you can easily refer to it when in need.

  5. Simple explanations for complex concepts.

  6. High quality video and different delivery methods to keep things interesting.

  7. Lots of ideas to automate your current tasks.


Learn from Google or from this Course?

I've been there! I first learnt VBA from Google.

It was truly a light-bulb moment, when I realized that Google has some answers - it could give me some of the code I was looking for - but there two things missing:

  1. The WHY - why does the code work in one situation but not another.

  2. Structure - topic sequence, real-case examples, workbooks, documentation.

Don’t waste your time

Don't waste your time on poor quality videos and confusing explanations. In this Excel VBA course, complex topics are taught in a simple way by an instructor with 17 years of experience. The content has been carefully crafted and clearly organized. The videos are high quality and include animations to emphasize points. Most importantly you learn VBA by doing. 

"I have already tried some VBA classes but so far I was never able to apply what I learnt to my own work. This course is different. I have already used some of the techniques Leila is teaching and applied it to my work files. I was able to reduce manual work for me and my colleagues by creating my own macros from scratch. Highly recommended!!!" -- Randy Taylor

"Leila is the real deal... there are many VBA courses on Udemy (some of which I've taken) that merely demonstrate the functionalities. Leila explains the important foundation concepts and how the model is set up allowing you to truly create VBA code from thought rather than copying and pasting from various places. Her class is professionally constructed, with resources and content for each lecture, practice content for you to try things yourself and good Q&A opportunities to answer specific questions. Trust me, this is the course you want to take."  -- Justin Fazzio

What you can look forward to in this course:

  • Understand how to use the right syntax in VBA

  • Useful Excel VBA codes you can directly use for your work

  • Use the macro recorder for simpler tasks and improve the code

  • Working with the Visual Basic Editor

  • Debugging tools and error handling methods

  • Different ways to reference ranges and cells

  • Different methods to loop

  • How to work with data types

  • PivotTables in Excel using VBA

  • Importing data from other files and exporting data to text files

  • Interacting with other applications (Word, PowerPoint, Outlook)

  • ActiveX and Userforms to enhance the Excel interface

  • Basically all the knowledge you need to automate tasks in Excel


Screenshots

Unlock Excel VBA and Excel Macros
Unlock Excel VBA and Excel Macros
Unlock Excel VBA and Excel Macros
Unlock Excel VBA and Excel Macros

Content

Introduction & Scope

Course Structure & Content

Important Compatibility & Setup Information

Course Resources: Download Course Project Files

Course Expectations

Course Outline for Quick Reference

Your First Macro

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

The Object Model

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

Referencing Ranges, Worksheets & Workbooks with VBA

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

Working with Variables

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

Looping through Collections & Making Decisions

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

Useful Built-in Functions

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

Debugging, Error Handling & Procedure Scope

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

Main Project #1: Create a Tool to Automate Table of Contents

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!

Looping in VBA (Controlling the Flow of Code)

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

Working with Arrays

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

Working with Files, Folders & Text Files

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

Excel Tables, Formulas & Pivot Tables

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

Main Project #2: Sales Regional Reporting Tool

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!

Interacting with Other Applications (Word, PowerPoint etc.)

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

Workbook and Worksheet Events

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

Working with Userforms & ActiveX Controls

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

Main Project #3: Invoice Generation Tool

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!

Create your own Custom Functions

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

Working with Charts

The Chart Object Library & Section Overview

Create and Modify a Chart with VBA

Animated Charts: Different Methods

Show Charts inside UserForms

Key Takeaways: Charts

Summary & Final Words

Your Next Steps & more Learning

BONUS Lecture: More from Leila



Reviews

A
Angel8 October 2020

Plenty of course material is provided which encompasses theory and practice. Technical concepts are clearly explained by the instructor.

D
Dastgeer7 October 2020

This course covers writing VBA language in detail right from the beginning, it starts with how to write simple macros then to complex one.

P
Prashant6 October 2020

Often uses the word "This" and "That". Difficult to figure out what object or property she is referring to when she uses the phrase e.g. "I am gonna change it "here" and "this" will get updated. Please refrain yourself from using the words "this", "that", "here" and "there" during the training.

J
Jessica4 October 2020

Great and clear... very happy with the way Leila explains and goes through every task. This course is being extremely helpful for my current work projects.

M
Mara3 October 2020

I am still taking this course, but I have been very impressed with how well structured these classes are. Leila is really patient and takes baby steps with how everything works and uses analogies to explain certain logics, so anyone who is starting at this topic (like me) is able to understand. I am not even halfway through the course and I've already learned SO MUCH and I am very sure I will be able to use this at my job. This was so worth it. Nice job, Leila. You were born to teach this. Thank you!

J
James27 February 2020

Step by Step. Included small pearls and also covers all alternatives. Teacher and lessons are very, very organized.

J
Joy27 February 2020

I've watched Leila Gharani's YouTube channel and was delighted to find her on Udemy. She's the BEST online Excel instructor out there. I appreciate how carefully she explains everything and how detailed her information and files are. As an Excel instructor teaching for over 30 years, I find that I learn more from Leila than I've learned from any other instructor and it's always a very pleasant and enjoyable process. Thanks, Leila!

M
Matthew26 February 2020

This course is great so far. I've only done a few sections, and I've already learned a few tricks I applied at work today! God bless anyone who shares their knowledge like you, Leila and thank you.

A
Adam26 February 2020

Yes, but the instructor does move at a very quick pace. Would have been better if there is more repetition.

P
Pasan26 February 2020

My first language is not English. The instructor use a basic language so it is easy for me to understand the content. The visualization is clear.

M
Matthew25 February 2020

This course was great, I already have a lot of this knowledge from a few years of using google, as a result, it was almost a confirmation of what i knew and even picked up a few things along the way.

W
Washington23 February 2020

Thank you for sharing your knowledge on VBA for Excel and Macro. I've been using VBA and macros for many years by experience in Stack Overflow and forums but did not really "learn" the basics. After going to section 4, I'm so amazed that I've learned so much which I wished I could've learned them years before. Am so happy to go through the course and so happy to have invested in this. Thanks again Leila!

N
Nelson22 February 2020

The lecture is eloquent and the way she describes the course and the plan of the course is motivating.

A
Almudena21 February 2020

I'm Cost Controller and I have to improve the files where I'm working. I have started Excel formulas limitations and I hope that this course will help me with that. :)

E
Elisabeta21 February 2020

Very well-structured course, goes directly to the point wich helps a lot not wasting precious time, and Leila explains things so clearly. I definetly recommend it.



1506888

Udemy ID

1/12/2018

Course created date

7/31/2019

Course Indexed date
Bot
Course Submitted by

Twitter
Telegram
Android PlayStore
Apple Appstore