Excel VBA - What They Don't Teach You Part I
3.3 (22 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
2,277 students enrolled

Excel VBA - What They Don't Teach You Part I

Learn How to Use Excel VBA to Automate Manual Tasks
3.3 (22 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
2,277 students enrolled
Last updated 3/2019
English
English [Auto]
Current price: $23.99 Original price: $39.99 Discount: 40% off
2 days left at this price!
30-Day Money-Back Guarantee
This course includes
  • 1.5 hours on-demand video
  • 80 articles
  • 6 downloadable resources
  • 1 Practice Test
  • Full lifetime access
  • Access on mobile and TV
  • Assignments
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Be able to learn how to perform tasks quickly by using the VBA tools
  • Grasp how VBA works and it's fundamental concepts
  • Write solid, good VBA code
  • Solve Real World Problems
  • Understand how to create count, sum, and text functions in VBA
  • Drastically improve your ability to debug problems in VBA.
  • Learn how to create UserForms and work with UserForm controls
  • Build your own VBA Applications. Why? To save time! I have seen many cases where people are unnecessarily manually updating data.
  • Significantly improve your Excel skills to create more powerful applications that communicate your information in the best manner
  • Impress your co-workers and managers
  • 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 Excel VBA & macros courses? It includes: Over 60 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.
  • Learn creative & simple techniques that allow you to create your own Excel applications from scratch
  • Create several ideas to automate your current tasks.
Requirements
  • Microsoft Excel 2010 or higher
  • Be familiar with working with Microsoft Excel
Description

What will I learn from this course?

This course will show you the various techniques on how you can use VBA to get tasks created quickly. This course will show you how to automate manual tasks so that you can do your job more efficiently than  before.

Go beyond the basics

Unlike other Excel VBA courses, this course will show you Excel functions that are not normally taught and also explain how they can be used in a real world scenario. Most Excel VBA courses only scratch the surface and explain the very basics. They will talk about functions like the Offset function, but won't explain why it is used or how powerful it can be used to automate a manual task such as to populate missing data on several rows and columns. This course will explain this function and technique in depth along with many other functions.

What is the Course Structure?

This course will walk you step by step at your own pace showing you the fundamentals of Excel VBA. This course is broken down into 6 sections. Each section will begin with a lecture followed by a demonstration of that lecture. There will be a quizzes throughout each section and an assignment at the end of each section that will test your knowledge and understanding of the material covered.

What is in this course?

This course is packed with over 50 lectures, 50 demonstrations, tons of quizzes, 6 assignments, and bonus and resource material that will help you better understand the concepts of Excel VBA.

What level should I be at after taking this course?

After you complete this course, you will be at the Intermediate level with Excel VBA. This of course assumes that you will consistently practice the concepts that will be discussed.

Who this course is for:
  • Anyone who wants to learn how to automate routine tasks
  • Business Professionals who work with Excel on a daily basis
  • Data Analyst
  • Business Analyst
  • Information Specialist
  • People that want to improve their Excel skills
Course content
Expand all 128 lectures 01:33:58
+ Introduction to Excel VBA
16 lectures 13:07
Variables and Data Types Quiz
2 questions
Option Explicit
00:06
Static Keyword
00:19




Input Box Quiz
1 question
Line Breaks
00:54
Saving Your File
01:21
Introduction to Excel VBA Quiz
8 questions
This assignment will demonstrate your knowledge from Section 1.
Introduction to Excel VBA Assignment
1 question

Here is the solution to the first assignment.

Introduction to VBA - Assignment Solution
00:01
+ Creating and Formatting Worksheets
19 lectures 09:25
Creating and Renaming a New Worksheet
02:03
Creating a New Worksheet from an Input Box
00:09
Counting Worksheets
00:04
What Does Set Do?
00:46
Hiding/Displaying a Worksheet
01:15
Moving a Worksheet
00:23
Copying a Worksheet
00:55
The Range Object
00:19
The Cells Property
00:18
Writing to a Worksheet
00:30
Formatting a Worksheet
00:40
Changing Background Colors
00:11
The Font Property
00:20
Excel RGB Colors
00:27
Excel VB Colors
00:10
Formatting a Worksheet Quiz
1 question
Deleting a Worksheet
00:32
Display Alerts
00:15
Working with Worksheets Quiz
5 questions
Working with Worksheets Asssignment Info
00:05
This assignment will demonstrate your knowledge from Section 2.
Working with Worksheets Assignment
1 question

Here is the solution to the second assignment.

Working with Worksheets - Assignment Solution
00:01
+ Navigating Within the Cells
13 lectures 12:48
Selecting Cells
00:14
Selecting Cells Quiz
3 questions
The Offset Function
03:19
Offset Quiz
2 questions
The Last Used Row
02:15
The Next Blank Row
01:53
The Next Blank Row Quiz
1 question
Creating a For Next Loop
00:56
More For Next Loop Examples
00:16
Step Keyword
00:18
For Next Loop Quiz
1 question
Using The Last Used Row with a For Next Loop
00:31
Creating a For Each Loop
00:53
Creating an If Statement
01:41
Checking Blank Cells
00:08
If Statement Example Inside of a For Next Loop
00:20
If Statement Quiz
1 question
Navigating Within the Cells Quiz
10 questions
This assignment will demonstrate your knowledge from Section 3.
Navigating Within the Cells Assignment
1 question

Here is the solution to the third assignment.

Navigating Within the Cells - Assignment Solution
00:01
+ Using Count and Sum Functions
7 lectures 18:36
CountA Function
01:07
CountBlank Function
01:13
CountIfs Function
02:11
Offset and Sum Function
07:06
Indirect Function
04:38
SumIfs Function
02:20
Count and Sum Quiz
4 questions
This assignment will demonstrate your knowledge from Section 4.
Count and Sum Functions Assignment
1 question

Here is the solution to the forth assignment.

Using Count and Sum Functions - Assignment Solution
00:01
+ Working with Text Functions
10 lectures 11:35
Right Function
01:17
Left Function
00:51
Mid Function
02:32
LEN Function
00:59
Trim Function
01:30
Join Strings
00:05
Text Function
01:51
The Rept Function
01:11
Substitute Function
01:18
Working With Text Functions Quiz
5 questions
This assignment will demonstrate your knowledge from Section 5.
Working with Text Functions Assignment
1 question

Here is the solution to the fifth assignment.

Working With Text Functions - Assignment Solution
00:01
+ Putting it All Together with a UserForm
8 lectures 18:16
UserForm Controls
00:50
Using a Case Statement
03:12
Excel VBA Events
00:47
Workbook Open Event
00:39
Workbook NewSheet Event
01:03
Displaying a UserForm when Excel Opens
01:37
Executing the Sub Procedures from the UserForm
10:06
This assignment will demonstrate your knowledge from Section 6.
UserForm and UserForm Controls Assignment
1 question

Here is the solution to the sixth assignment.

UserForm and UserForm Controls - Assignment Solution
00:01
+ Course Summary and Closing Remarks
2 lectures 00:23
Course Review Test
25 questions
Course Summary and Closing Remarks
00:06

Thank you for taking the course!!!!

Thank You for Taking This Course
00:17
+ Bonus Excel VBA Code
42 lectures 04:42
List all Worksheets in a Workbook
00:04
Count the Number of Sheets in a Workbook
00:01
How to Pass a Value in Message Box
00:02
Find and Highlight All Duplicates in Range with VBA
00:04
Text to Speech
00:02
Delete File From Drive with VBA
00:04
Loop Through Entire Column
00:05
Loop through Cells and Clear Formats
00:06
Loop Through Entire Row
00:05
Count Rows in a Selection
00:01
Count Columns in a Selection
00:01
Copy the Entire Data from One Worsheet to Another Worksheet
00:03
Union – Join Multiple Ranges
00:10

You can use the Instr Function in VBA to test if a string contains certain text.  The result is the number of times the specified text appears in the string.

INSTR Function
00:02
Union – Join Multiple Ranges
00:04
Delete all Named Ranges From a Workbook
00:02
Launch Spell Check
00:01
Code a Data Validation List in VBA
00:06
Unhide All Hidden Worksheets
00:03
Rename a File
00:03
Add a Chart Title
00:04
Close all Workbooks at Once
00:03
Insert Multiple Columns
00:12
Insert Multiple Rows
00:12
Highlight Unique Values
00:07
Delete all Blank Worksheets
00:10
Count Open Unsaved Workbooks
00:10
Highlight Cells with Misspelled Words
00:12
Highlight Difference in Columns
00:04
Active Workbook in an Email
00:16
Hide all Worksheets except the Active Worksheet
00:10
Insert Multiple Worksheets
00:04
Add Serial Numbers
00:17
Auto Fit Rows
00:08
Change Background Color of Cell Range
00:02
Auto Fit Columns
00:06
Insert A Row After Every Other Row in the Selection
00:08
Loop Through Each Character in a String
00:13
Calculate the Area of a Rectangle
00:12
Convert All Formulas into Values
00:03
Delete a workbook
00:18
Save Workbook With TimeStamp in Its Name
00:06
+ Resource Material
11 lectures 05:03
VBA Message Functions
00:04
VBA Text Functions
00:47
VBA Program Flow Functions
00:11
VBA Information Functions
00:18
VBA Conversion Functions
00:56
VBA Date & Time Functions
00:52
VBA Math & Trig Functions
00:31
VBA Financial Functions
00:43
VBA Error Handling Functions
00:05
VBA Array Functions
00:13
VBA File Management Functions
00:19