Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

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

Name: Excel VBA - What They Don't Teach You Part I
Rating: 3.8 (28 reviews)

Learn How to Use Excel VBA to Automate Manual Tasks

Created byJessica Vickery Dabbs

Last updated 3/2019

English

English [Auto],

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.

Course content

9 sections • 128 lectures • 1h 33m total length

Course Structure and Content2:11
Master excel vba to automate tasks and build applications, exploring practical scenarios, tips, tools not normally taught, count and sum, text functions, cell navigation and worksheets, user forms and controls.
What is Excel VBA?1:12
Discover how Excel VBA automates manual tasks, updates large data and charts, and formats data by accessing the VBA environment via the Developer tab and inserting a module.
What Does Sub Mean?0:29
Learn what sub means in VBA, where sub is short for a sub procedure. See how macros record steps and run as a sub procedure.
Variables and Data Types1:34
Variables and Data Types Quiz
Option Explicit0:06
Static Keyword0:19
Adding Comments in VBA0:22
How to get to the Immediate Window?0:11
The Immediate Window1:14
Debug.Print Statement1:12
Creating a Message Box0:33
Creating an InputBox0:55
Create an input box to prompt for user values, capture the entered text (ok or enter), and place it in cell A1, returning an empty string on cancel.
Input Box Quiz
Code Indentation0:31
Apply generally accepted code indentation rules to VBA code to improve readability and debugging. Contrast the left, bad indentation with the right, good indentation to reinforce best practices.
Line Breaks0:54
Improve readability of VBA code by inserting line breaks midline with a space and underscore before the break, signaling the VBA compiler that lines continue.
Saving Your File1:21
Introduction to Excel VBA Quiz
Introduction to Excel VBA Assignment
Introduction to VBA - Assignment Solution0:01
Here is the solution to the first assignment.

Creating and Renaming a New Worksheet2:03
Learn to create and rename worksheets in VBA using worksheet.add or sheets.add. Rename via activeSheet.name or sheets.name, and use the debug steps (F8) to practice.
Creating a New Worksheet from an Input Box0:09
Counting Worksheets0:04
What Does Set Do?0:46
Hiding/Displaying a Worksheet1:15
Moving a Worksheet0:23
Copying a Worksheet0:55
Copy the active worksheet to three workbook positions: end, before the outline worksheet, and after the outline worksheet.
The Range Object0:19
The Cells Property0:18
Writing to a Worksheet0:30
Formatting a Worksheet0:40
Learn practical worksheet formatting in Excel VBA by applying bold fonts, blue and red font colors, and underlining text, then run a program to see the results.
Changing Background Colors0:11
The Font Property0:20
Excel RGB Colors0:27
Excel VB Colors0:10
Formatting a Worksheet Quiz
Deleting a Worksheet0:32
Display Alerts0:15
Working with Worksheets Quiz
Working with Worksheets Asssignment Info0:05
Working with Worksheets Assignment
Working with Worksheets - Assignment Solution0:01
Here is the solution to the second assignment.

Selecting Cells0:14
Learn how to select cells in Excel VBA, including concepts like the active cell and examples showing different selection methods.
Selecting Cells Quiz
The Offset Function3:19
Master the Excel VBA offset function, which moves a starting cell by specified rows and columns using positive and negative directions, illustrated by A4 to A5 and B7 to D4.
Offset Quiz
The Last Used Row2:15
The Next Blank Row1:53
The Next Blank Row Quiz
Creating a For Next Loop0:56
More For Next Loop Examples0:16
Step Keyword0:18
For Next Loop Quiz
Using The Last Used Row with a For Next Loop0:31
Creating a For Each Loop0:53
Master the for each loop in Excel VBA by iterating through every worksheet in a workbook with a wsheet variable and displaying each sheet name in a message box.
Creating an If Statement1:41
Checking Blank Cells0:08
If Statement Example Inside of a For Next Loop0:20
If Statement Quiz
Navigating Within the Cells Quiz
Navigating Within the Cells Assignment
Navigating Within the Cells - Assignment Solution0:01
Here is the solution to the third assignment.

CountA Function1:07
CountBlank Function1:13
CountIfs Function2:11
Countifs counts cells that meet one or more criteria, using dates, numbers, text, and wildcards. Use two ranges, optional second criteria, across data and summary worksheets.
Offset and Sum Function7:06
Explore how the Excel VBA sum function computes a range total by offset from a starting point, retrieving east and west values, and summing them into a total variable.
Indirect Function4:38
SumIfs Function2:20
Explore the Excel VBA sumifs function to sum a range using criteria, with the sum range and criteria range matching in size, illustrated by a school supplies example.
Count and Sum Quiz
Count and Sum Functions Assignment
Using Count and Sum Functions - Assignment Solution0:01
Here is the solution to the forth assignment.

Right Function1:17
The right function extracts characters from the right of a text string. Use it to get last character of each name in column a and place initial in column b.
Left Function0:51
Master the left function to extract characters from the left of text. See how the first initial from each name in column A is placed in column B.
Mid Function2:32
Use the mid function to extract characters from the middle of a text, with text, start, and length arguments, including locating the first space to get the middle initial.
LEN Function0:59
Use len function to measure text length by counting characters, including numbers but excluding formatting, apply to city names in column b and place results in column d.
Trim Function1:30
Join Strings0:05
Text Function1:51
Use the text function to convert dates and numbers into formatted text, a string formula, and display month, day, and year in separate columns, or convert telephone numbers.
The Rept Function1:11
Substitute Function1:18
Learn how the substitute function replaces old text with new text in a string, using original text, old text, and new text, with a practical example and results.
Working With Text Functions Quiz
Working with Text Functions Assignment
Working With Text Functions - Assignment Solution0:01
Here is the solution to the fifth assignment.

UserForm Controls0:50
Learn how the Excel VBA tool box adds and manages user form objects—labels, text boxes, and command buttons—to display or edit data and run macros.
Using a Case Statement3:12
Use the select case statement to route code based on the combo box value, displaying a message box when apples, oranges, or grapes are selected.
Excel VBA Events0:47
Workbook Open Event0:39
Workbook NewSheet Event1:03
Displaying a UserForm when Excel Opens1:37
Executing the Sub Procedures from the UserForm10:06
Build a user form in Excel VBA, configure a combo box caption and name, add label with font size, set a background color, and run sub procedures from a button.
UserForm and UserForm Controls Assignment
UserForm and UserForm Controls - Assignment Solution0:01
Here is the solution to the sixth assignment.

List all Worksheets in a Workbook0:04
Count the Number of Sheets in a Workbook0:01
How to Pass a Value in Message Box0:02
Find and Highlight All Duplicates in Range with VBA0:04
Text to Speech0:02
Delete File From Drive with VBA0:04
Loop Through Entire Column0:05
Loop through Cells and Clear Formats0:06
Loop Through Entire Row0:05
Count Rows in a Selection0:01
Count Columns in a Selection0:01
Copy the Entire Data from One Worsheet to Another Worksheet0:03
Union – Join Multiple Ranges0:10
INSTR Function0:02
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.
Union – Join Multiple Ranges0:04
Delete all Named Ranges From a Workbook0:02
Launch Spell Check0:01
Code a Data Validation List in VBA0:06
Unhide All Hidden Worksheets0:03
Rename a File0:03
Add a Chart Title0:04
Close all Workbooks at Once0:03
Insert Multiple Columns0:12
Insert Multiple Rows0:12
Highlight Unique Values0:07
Delete all Blank Worksheets0:10
Count Open Unsaved Workbooks0:10
Highlight Cells with Misspelled Words0:12
Highlight Difference in Columns0:04
Active Workbook in an Email0:16
Hide all Worksheets except the Active Worksheet0:10
Insert Multiple Worksheets0:04
Add Serial Numbers0:17
Auto Fit Rows0:08
Change Background Color of Cell Range0:02
Auto Fit Columns0:06
Insert A Row After Every Other Row in the Selection0:08
Loop Through Each Character in a String0:13
Calculate the Area of a Rectangle0:12
Convert All Formulas into Values0:03
Delete a workbook0:18
Save Workbook With TimeStamp in Its Name0:06

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

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

What you'll learn

Explore related topics

Course content

Introduction to Excel VBA16 lectures • 13min

Creating and Formatting Worksheets19 lectures • 9min

Navigating Within the Cells13 lectures • 13min

Using Count and Sum Functions7 lectures • 19min

Working with Text Functions10 lectures • 12min

Putting it All Together with a UserForm8 lectures • 18min

Course Summary and Closing Remarks2 lectures • 1min

Bonus Excel VBA Code42 lectures • 4min

Resource Material11 lectures • 5min

Requirements

Description

Who this course is for: