Excel VBA programming by Examples (MS Excel 2016)
4.3 (896 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
12,838 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel VBA programming by Examples (MS Excel 2016) to your Wishlist.

Add to Wishlist

Excel VBA programming by Examples (MS Excel 2016)

Recording Macro, Creating Excel VBA form, Fetching data from MS Access, Working with multiple sheets and workbook
4.3 (896 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
12,838 students enrolled
Last updated 9/2017
English
English [Auto-generated]
Price: Free
Includes:
  • 4 hours on-demand video
  • 2 Articles
  • 37 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Learn to automate their time consuming repeatitive tasks for more accuracy and less time consuming
  • Understand VBA syntax for Excel
  • Understand usage of tools available in VBA environment
  • See several worked out examples
  • Learn to fetch access data using Excel as a front end
  • Learn to develop VBA froms and interact with the same, load charts on forms
  • See inventory management workout
  • And many other workout which i am adding step by step
View Curriculum
Requirements
  • Basic Excel 2016
  • Understanding of basic excel functions and features (pivot table, sort, filter, vlookup) etc
Description

Understand what you are going to achieve through the VBA by seeing demo and then see step by step explanation of VBA code. Learn about Excel VBA syntax, Excel VBA form and control, methods of  using forms and controls, several workout examples to see usage of Excel VBA for automation.

In this course, you will learn following stuff in step by step manner

Level 01 – start without coding – Automate tasks using Excel Macro recording

  • Demo of an excel macro
  • What is excel macro
  • When to use it
  • How to record a macro/create a shortcut action
  • How to run a recorded macro
  • How to create a shortcut for a macro action
  • How to run a recorded macro on a new dataset (excel workbook)
  • How to record a relative macro
  • What is the difference between a relative macro and a general macro.
  • How to understand what was recorded as macro.
  • How to delete a macro

Level 02 A – Understand Excel VBA integrated development environment

  • How to reach VBA window
  • What are different component of the window
  • What is use of those components
  • How to use breakpoint, properties window, edit tools etc.

Level 02 B – Understand Excel VBA syntax

  • How to define a variable
  • Different types of variables
  • How to write a for loop
  • How to display output in an interactive way
  • How to write output in a different worksheet
  • How to take user input through a prompt
  • How to use user input
  • How to use record macro to know VBA syntax
  • How to use breakpoint
  • How to run macro through click of a button
  • When you need to write do while / do until loop
  • Syntax of do while / until loop
  • How to take input from excel sheet for program execution
  • How to ensure variable names are correct before execution of program
  • If else command, If elseif else command
  • Using mod function (for remainder)
  • Showing status bar

Workout Examples 01 – Using Forms for user entry, chart display etc.

  • See a worked out example of a VBA form
  • Learn about various control, design aspects of Excel VBA form
  • Learn about why will need form, and such controls
  • Hide Data sheet and format other sheet to make it look professional
  • Ensure proper data type
  • Ensure value selection from combo box only
  • Learn to define level, text, combobox and button command
  • Learn to pass dropdown data in combobox
  • Learn to use form entry into VBA
  • Learn to write back on Excel form
  • Learn how to load form while getting excel started
  • Learn to change properties of control through VBA

Workout Examples 02 – fetching data from MS Access using Excel VBA

  • How to use Excel as front end and fetch data from microsoft Access database
  • Where to use this à Greatly useful when many users have Excel but don't have MS access database in the PC
  • Where Reference is needed
  • Watch window - how to use it
  • How to edit the code for many fields and different databases

Workout Examples 03 – One sheet per product or agent

  • How to use do while loop to let it run for as many records as it has got
  • How to find block size (starting and ending row for each product)
  • How to add sheet using VBA and give it a name
  • How to ensure that the tool remains intact with multiple runs and even a mistake can't cause issue
  • How to repeat header in each tab or worksheet

Workout Examples 04 – Inventory management, coupon assignment and customer communication using Excel VBA

  • Traverse through various sheets and workbooks using VBA          
  • Formatting date              
  • Writing derived information from one sheet to another
  • Passing several parameters to VBA for conditional traversal         
    • 3 should mean three coupons to get reserved
    • [Coupon code : Validity] will need comma if there are multiple vouchers
  • Error handling : alert, if there are no coupons     
  • Protecting Excel tool for further usage   

Workout Examples 05 – Reading data from a microsoft access database and writing it into a text file

  • Reading Microsoft Access data using VBA
  • directly writing output into a text file
  • Making the output comma separated 

Workout Example 06 - Designing survey form in Excel VBA with option buttons / list box etc.

Workout Example 07 - Insert Excel VBA form data in MS Access database 

Workout Example 08 - Using pivot table, vlookup and several other formula for a sampling work 

Workout Example 09 - Windows based user authentication and Voice notification of execution of VBA


Who is the target audience?
  • Microsoft Excel users who wants to learn automation and VBA programming
  • Someone who wants to learn by seeing workout examples
Students Who Viewed This Course Also Viewed
Curriculum For This Course
31 Lectures
04:03:35
+
Course Introduction and automation without coding
3 Lectures 14:14

Understand what you are going to achieve through the VBA by seeing demo and then seeing step by step explanation of VBA code.Please note all excel files along with VBA code is available for download.

What to expect in this Excel VBA programming by example?
03:57

  • Demo of an excel macro
  • What is excel macro
  • When to use it
  • How to record a macro/create a shortcut action
  • How to run a recorded macro
  • How to create a shortcut for a macro action
  • How to run a recorded macro on a new dataset (excel workbook)
  • How to record a relative macro
  • What is the difference between a relative macro and a general macro.
  • How to understand what was recorded as macro.
  • How to delete a macro
Automate without coding - recording macro with relative reference
09:49

FAQ - for recording macro
00:28
+
Understand VBA development environment and VBA syntax
4 Lectures 56:06
  • How to reach VBA window
  • What are different component of the window
  • What is use of those components
  • How to use breakpoint, properties window, edit tools etc.
Understand Excel VBA integrated development environment
12:00

  • How to define a variable
  • Different types of variables
  • How to write a for loop
  • How to display output in an interactive way
  • How to write output in a different worksheet
  • How to take user input through a prompt
  • How to use user input
  • How to use record macro to know VBA syntax
  • How to use break point
  • How to run macro through click of a button

Understand Excel VBA syntax
15:42

  • When you need to write do while / do until loop
  • Syntax of do while / until loop
  • How to take input from excel sheet for program execution
  • How to ensure variable names are correct before execution of program
Understand Excel VBA syntax continued ....
16:54

  • If else command, If elseif else command
  • Using mod function (for remainder)
  • Showing status bar
Understand conditional execution (if else conditions)
11:30
+
Excel VBA workout examples
22 Lectures 02:51:25

See a demo of Excel VBA form. It helps you learn about
- various control, design aspects of Excel VBA form
- why will need form, and such controls
- Hide Data sheet and format other sheet to make it look professional
- Ensure proper data type
- Ensure value selection from combo box only
- How to define level, text, combobox and button command
- How to pass dropdown data in combobox
- How to use form entry into VBA
- How to write back on Excel form
- How to load form while getting excel started
- How to change properties of control through VBA
- How to close excel when form is closed

Workout Example 1- PartA- Introduction to Excel VBA forms
05:32

  • Know about various controls available for form
  • Formatting and alignment of controls
  • Providing list for combobox
  • Ensuring data selection through combobox only
  • setting action for button
  • image control


Workout Example 1 - Part B 01- Design your first Excel VBA form
10:15

  • Know about various controls available for form
  • Formatting and alignment of controls
  • Providing list for combobox
  • Ensuring data selection through combobox only
  • setting action for button
  • image control
Workout Example 1 - Part B 02- Design your first Excel VBA form
09:28

Learn
1. How accuracy of user entry was checked before execution of program
2. How form entry was taken inside VBA
3. How Control property was changed using VBA
4. How charts were updated using VBA
5. How charts were saved as gif using VBA
6. How images were loaded using VBA
7. How sheets are getting unhide and hide using VBA
8. How screen update has been put as false, so that end user don't see much changes happening

Workout Example 1 - Part C - Explanation of demo Excel VBA form & code
09:08

- How to use Excel as front end and fetch data from microsoft Access database
- Where to use this : Greatly useful when many users have Excel but don't have MS access database in the PC
- Where Reference is needed
- Watch window - how to use it
- How to edit the code for many fields and different databases

Workout Example 2 - Fetching data from MS Access database using Excel
11:17

- How to use do while loop to let it run for as many records as it has got
- How to find block size (starting and ending row for each product)
- How to add sheet using VBA and give it a name
- How to ensure that the tool remains intact with multiple runs and even a mistake can't cause issue
- How to repeat header in each tab or worksheet

Workout Example 3 - Part A - One sheet per agent or product
12:03

Workout Example 3 - Part B - One sheet per agent or product
11:24

Traverse through various sheets and workbooks using VBA
Formatting date
Writing derived information from one sheet to another
Passing several parameters to VBA for conditional traversal 3 should mean three coupons to get reserved
[Coupon code : Validity] will need comma if there are multiple vouchers
Error handling : alert, if there are no coupons
Protecting Excel tool for further usage

Workout Example 4 - Part A - Coupon Assignment and Inventory management
10:22

Workout Example 4 - Part B - Coupon Assignment and Inventory management
09:02

  • This is specially useful, when the output is more than 1 million
  • Here you are passing the criteria from excel and fetching the record
  • Then you are directly writing the output into a text file directly means without bringing data into Excel at all
  • Please note this is an extension of workout example 2
  • Please use MS Access data base available in workout 2 for this workout as well
Workout Example 5 - Reading microsoft access data and writing into a text file
05:51

Listbox

  • How you can create list box, so that user can comfortably select some choice available
  • How to ensure that users do not abuse the system - for example ensure that user selects at least one choice and at max three choice before submission
  • How to read user's selection

Options within a frame

  • How to create options so that only one of the choice can be selected
  • How to ensure that user don't proceed without making at least one selection

Message box

  • How to create new line in message box text
  • How to provide a title to a prompt to help user understand what is the purpose
  • How to take user entry and make progress accordingly
Workout Example 6 - Part A- Design survey form in excel VBA (deal with list etc.
09:53

Listbox

  • How you can create list box, so that user can comfortably select some choice available
  • How to ensure that users do not abuse the system - for example ensure that user selects at least one choice and at max three choice before submission
  • How to read user's selection

Options within a frame

  • How to create options so that only one of the choice can be selected
  • How to ensure that user don't proceed without making at least one selection

Message box

  • How to create new line in message box text
  • How to provide a title to a prompt to help user understand what is the purpose
  • How to take user entry and make progress accordingly
Workout Example 6 - Part B- Design survey form in excel VBA (deal with list etc.
07:29

  • How to create a connection to a database
  • How to create an insert into query
  • How to execute an insert into command from access form Excel VBA
  • Some precautions - like setting reference, removing connection
Workout Example 7 - Storing Excel VBA Form data into MS Access database
06:40

This is workout 8. In this one you will learn how to use

  • VLOOKUP
  • pivot table
  • If Else condition
  • Sorting
  • Visible cells only etc. inside a VBA code
Workout Example 8 - Part A- Using Pivot Table, VLOOKUP etc. inside a VBA code
14:04

This is workout 8. In this one you will learn how to use

  • VLOOKUP
  • pivot table
  • If Else condition
  • Sorting
  • Visible cells only etc. inside a VBA code
Workout Example 8 - Part B- Using Pivot Table, VLOOKUP etc. inside a VBA code
10:30

  • How to use windows based user authentication through Excel VBA
  • Syntax for writing case statement
  • Syntax for voice notifications
Workout Exp 09 - 01 Windows based user authentication and Voice notification
05:03

  • How to use windows based user authentication through Excel VBA
  • Syntax for writing case statement
  • Syntax for voice notifications
Workout Exp 09 - 02- Windows based user authentication and Voice notification
01:10

You will learn 

  • Reading complete worksheet data into a table
  • Managing named as well as unnamed headers
  • Running SQL command inside VBA
  • Little advance SQL coding as well (by product)
    • A-B
    • B-A


Workout Exp 10 - Reading worksheet data into a table and Running SQL inside VBA,
06:59

Workout Exp 11 - 01 Voice based reminder to make sure people stick to schedule
01:06

Workout Exp 11 - 02 Voice based reminder to make sure people stick to schedule
01:12

Workout Exp 11 - 03 Voice based reminder to make sure people stick to schedule
04:50

Workout Exp 12 - Schedule a VBA macro to run as per schedule
08:07
+
Special queries
2 Lectures 01:56
FAQ (Will keep growing based on Studen't questions)
00:26

Closing note
01:30
About the Instructor
Gopal Prasad Malakar
4.3 Average rating
1,798 Reviews
22,293 Students
16 Courses
Credit Card Analytics Professional- Trains Machine Learning

I am a seasoned Analytics professional with 16+ years of professional experience. I have industry experience of impactful and actionable analytics. I am a keen trainer, who believes that training is all about making users understand the concepts. If students remain confused after the training, the training is useless. I ensure that after my training, students (or partcipants) are crystal clear on how to use the learning in their business scenarios. My expertise is in Credit Card Business, Scoring (econometrics based model development), score management, loss forecasting and MS access based database application development.