Unlock Excel VBA and Excel Macros
4.8 /5- Offered byUDEMY
Unlock Excel VBA and Excel Macros at UDEMY Overview
Duration | 23 hours |
Total fee | ₹599 |
Mode of learning | Online |
Difficulty level | Intermediate |
Official Website | Go to Website  |
Credential | Certificate |
Unlock Excel VBA and Excel Macros at UDEMY Highlights
- Compatible on Mobile and TV
- Earn a Cerificate on successful completion
- Get Full Lifetime Access
- Course Instructor: Leila Gharani
Unlock Excel VBA and Excel Macros at UDEMY Course details
- Business Professionals who'd like to simplify & automate work flow.
- Data analysts.
- You currently use Excel and like to improve your skills.
- 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.
- 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: 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. 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. Create 3 Complete Excel VBA Tools. We go from idea to final product and cover each step. Over 100 pages of documentation (including useful code) so you can easily refer to it when in need. Simple explanations for complex concepts. High quality video and different delivery methods to keep things interesting. 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: The WHY - why does the code work in one situation but not another. Structure - topic sequence, real-case examples, workbooks, documentation. Don???? 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
Unlock Excel VBA and Excel Macros at UDEMY Curriculum
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?
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
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
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
Other courses offered by UDEMY
Student Forum
Anything you would want to ask experts?