Excel VBA Exercises and Real-World Projects
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
- Preview04:01
- 02:25Displaying the Developer Tab
- 06:47Writing Our First Macro
- 02:37VBA Naming Rules
- 07:12Debugging and Commenting
- 04:16Referencing Multiple Cells
- 07:08Intellisense
- 04:02Properties
- 02:34Methods
- 10:51Objects, Properties and Methods
- 05:53Macro Security Settings
- 10:34Worksheets Collection
- 08:34Workbooks Collection
- 02:42No "Undo" (CTRL+Z) After Macros
- 04:49Fully Qualified References
- 07:44Declaring Object Variables
- Preview11:04
- 6 questionsQuiz for Section 1
Instructor
![Karen Tateosyan](data:image/svg+xml,%3Csvg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 64 64"%3E%3C/svg%3E){kind=avatar}
- 4.6 Instructor Rating
- 204 Reviews
- 1,537 Students
- 2 Courses
My name is Karen and I am from Bulgaria.
I work at one of the world’s leading online payment providers but it is the VBA programming in Excel which I would describe as my hobby. During my career I have developed many Excel tools for the companies I have worked for.
I believe that Excel is one of the most used programs in the business world. Probably, we spend more time only on Facebook, Instagram and YouTube these days.
My mission is to share practical knowledge in Excel VBA which goes beyond knowing how to write a few lines of VBA code.