Excel VBA Exercises and Real-World Projects
4.6 (138 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.
1,216 students enrolled

Excel VBA Exercises and Real-World Projects

Create your own tools from scratch with Excel VBA!
4.6 (138 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.
1,216 students enrolled
Created by Karen Tateosyan
Last updated 2/2019
English
English [Auto]
Current price: $86.99 Original price: $124.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 15.5 hours on-demand video
  • 5 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • 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
  • Learn how to manipulate text using functions like LEFT, RIGHT, LEN, INSTR, SEARCH, FIND, etc.
  • Learn how to match data with ease using VLOOKUP, INDEX and MATCH functions, etc.
  • Learn how to analyze and aggregate data using COUNTIF, SUMIF, MAX functions, etc.
  • Learn important data manipulation techniques in VBA such as finding the last used row, sorting data, removing duplicate values, deleting empty rows, etc.
  • Create a better user experience using MS Form Controls.
  • Save your Excel files and protect them with a password with just a single click.
  • Send Outlook emails directly from Excel.
  • Respond to worksheet events such as updating a cell or making a double-click.
  • Boost your Excel skills by learning VBA and macros.
  • Design your own Excel tools from scratch to automate your daily tasks.
Requirements
  • Basic knowledge of Excel (programming knowledge NOT required).
Description

What am I going to learn from this course?

The purpose of this course is to learn practical skills and knowledge in using Excel VBA.

By the end of this course you will be able to create intermediate to advanced level Excel VBA real-world projects from scratch. You will be able to manipulate raw data in Excel with ease by designing Excel tools which save time, increase productivity and minimize the risk of making mistakes.

What is the target audience for this course?

This training program is for Excel users with no or little knowledge in Excel VBA programming.

What is the course structure organized?

This course consists of 6 sections.

The first section is called “Introduction to VBA” - we’ll have to get familiar with the basic concepts of VBA before we dive into the more advanced stuff.

The second section is called “Speed, Logic and VBA Exercises” – we’ll get familiar with using Excel worksheet and VBA functions, declaring variables, using loops, if-then statements, and also many different data manipulation techniques such as sorting data, removing blank rows, removing case sensitivity, applying borders or colors to a range of cells, etc. There will be many Excel exercises and examples in this section.

Sections 3 to 6 including are all about using Excel VBA to create real-world projects from scratch:

· Creating dynamic report of sales / payments in different currencies

· Data matching project (by multiple criteria)

· Sending emails project (using MS Outlook)

· Creating a case tracking system (using MS Form Controls)

What do I need in order to enroll in this course?

You need a Microsoft Excel version installed on your device. I have used Excel 2016 for this course, but the examples in it should run on earlier versions of Excel as well. The newer version you have, the better.

I am using Excel for Mac. Will this make any difference?

There are certain differences between Excel for Windows and Excel for Mac, so some of the codes may not work properly on Mac.

Do I need to have advanced level of using Excel before learning VBA?

No, it's not necessary.


Who this course is for:
  • Excel users who would like to get familiar and advance quickly in VBA coding
  • Excel users who would like to improve their practical skills by solving exercises and reviewing real-world projects
Course content
Expand all 126 lectures 15:28:05
+ Introduction to Excel VBA
17 lectures 01:43:13
Displaying the Developer Tab
02:25
Writing Our First Macro
06:47
VBA Naming Rules
02:37
Debugging and Commenting
07:12
Referencing Multiple Cells
04:16
Intellisense
07:08
Properties
04:02
Methods
02:34
Objects, Properties and Methods
10:51
Macro Security Settings
05:53
Worksheets Collection
10:34
Workbooks Collection
08:34
No "Undo" (CTRL+Z) After Macros
02:42
Fully Qualified References
04:49
Declaring Object Variables
07:44
Quiz for Section 1
6 questions
+ Speed, Logic and VBA Exercises
24 lectures 05:00:33
Worksheet Functions
15:31
VBA Functions
13:13
VBA Data Types
03:12
User Defined Functions
17:14
Finding the Last Row (1)
05:29
Finding the Last Row (2)
06:13
For...Next Loop
22:04
Adding Logic
11:50
Creating Dynamic Range
15:15
Counting
19:46
Summing
13:08
InputBox and MsgBox
08:31
Finding Max Value
21:32
Deleting Rows Which Meet a Condition
12:42
Sorting
18:43
Vlookup
14:59
Index and Match
18:32
For Each Loop
14:35
Macro Recorder
09:00
Removing Duplicates
21:36
VBA Projects Guidelines
07:43
Conclusion
00:55
Quiz for Section 2
4 questions
+ Currency Converter Project
30 lectures 03:35:47
Worksheet.Delete Method
09:23
Columns.ColumnWidth Property
06:19
Web Queries
05:44
Web Queries in VBA
08:36
Finding the Last Row
05:08
Left and Vlookup Functions
12:07
Creating a Function to Convert Currencies
09:58
Testing the Function
04:15
Looping to Convert
10:12
On Error Resume Next
05:05
Range.NumberFormat Property
03:07
Workbooks.Add Method
07:45
Removing Duplicates
12:10
CountIf and CountIfs Functions
05:01
CountIf and CountIfs Functions in VBA
09:21
Looping to Calculate Count of Orders
05:18
SumIf and SumIfs Functions
05:05
SumIf and SumIf Functions in VBA
07:12
Looping to Calculate Sum of Orders
04:08
Sort Method
07:21
Sum Grand Total
08:42
Freeze First Row
05:45
Application.ScreenUpdating Property
03:35
Worksheet SelectionChange Event
08:17
Worksheet Change Event
10:01
Inserting a Picture
04:17
Shape.Visible Property
11:08
Application.CopyObjectsWithCells Property
05:38
+ Data Matching Project
12 lectures 01:32:23
Prepare Deposits Matching Data
09:43
Right, Len and Search Functions
05:25
Right, Len and Search Functions in VBA
05:17
Looping to Extract Transaction IDs
08:34
Range.Replace Method
10:08
Preparing to Match Payments
06:54
Application.WorksheetFunction.Vlookup
08:38
Application.Vlookup
05:31
Testing the Program
10:45
MsgBox Function
10:55
+ Sending Emails Project
14 lectures 01:07:46
File System Objects
01:31
FolderExists and CreateFolder Functions
04:31
Workbook.SaveAs Method
04:59
Protect Your Workbooks
06:37
Early Binding
06:03
Late Binding
05:08
MsgBox vbYesNo 1
08:27
MsgBox VbYesNo 2
02:56
Basic MailItem Properties
06:42
Adding Attachments
05:31
Send Method
02:01
Switching to Late Binding
03:35
+ Creating a Case Tracking System
28 lectures 02:27:17
Visual Basic Object Naming Convention
03:56
Label and TextBox Controls
02:52
ComboBox Controls
04:31
CheckBox Controls
05:11
Command Button Controls
04:39
Tab Order of Controls
01:56
UserForm.Show Method
03:42
UserForm Initialize Event
04:25
InStr Function
08:33
Populating a ComboBox Control
07:05
Controls Change Event
04:16
Worksheet.Visible Property
08:09
Controls BeforeUpdate Event
05:57
TextBox.EnterKeyBehavior Property
02:41
Command Button Click Event
09:20
ComboBox DropButtonClick Event
04:32
UserForm.Hide Method
08:58
Formatting Added Information
05:19
New UserForm Design
05:11
Worksheet BeforeDoubleClick Event
06:50
Label Caption Property
06:01
Command Button Enabled Property
02:35
vbModal vs vbModeless
05:23
Deleting Rows in VBA
03:57
Worksheet Activate Event
03:16
Showing Stats
06:33
Testing the Program
02:13