Udemy
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
Development
Web Development Data Science Mobile Development Programming Languages Game Development Database Design & Development Software Testing Software Engineering Development Tools No-Code Development
Business
Entrepreneurship Communications Management Sales Business Strategy Operations Project Management Business Law Business Analytics & Intelligence Human Resources Industry E-Commerce Media Real Estate Other Business
Finance & Accounting
Accounting & Bookkeeping Compliance Cryptocurrency & Blockchain Economics Finance Finance Cert & Exam Prep Financial Modeling & Analysis Investing & Trading Money Management Tools Taxes Other Finance & Accounting
IT & Software
IT Certification Network & Security Hardware Operating Systems Other IT & Software
Office Productivity
Microsoft Apple Google SAP Oracle Other Office Productivity
Personal Development
Personal Transformation Personal Productivity Leadership Career Development Parenting & Relationships Happiness Esoteric Practices Religion & Spirituality Personal Brand Building Creativity Influence Self Esteem & Confidence Stress Management Memory & Study Skills Motivation Other Personal Development
Design
Web Design Graphic Design & Illustration Design Tools User Experience Design Game Design Design Thinking 3D & Animation Fashion Design Architectural Design Interior Design Other Design
Marketing
Digital Marketing Search Engine Optimization Social Media Marketing Branding Marketing Fundamentals Marketing Analytics & Automation Public Relations Advertising Video & Mobile Marketing Content Marketing Growth Hacking Affiliate Marketing Product Marketing Other Marketing
Lifestyle
Arts & Crafts Beauty & Makeup Esoteric Practices Food & Beverage Gaming Home Improvement Pet Care & Training Travel Other Lifestyle
Photography & Video
Digital Photography Photography Portrait Photography Photography Tools Commercial Photography Video Design Other Photography & Video
Health & Fitness
Fitness General Health Sports Nutrition Yoga Mental Health Dieting Self Defense Safety & First Aid Dance Meditation Other Health & Fitness
Music
Instruments Music Production Music Fundamentals Vocal Music Techniques Music Software Other Music
Teaching & Academics
Engineering Humanities Math Science Online Education Social Science Language Teacher Training Test Prep Other Teaching & Academics
AWS Certification Microsoft Certification AWS Certified Solutions Architect - Associate AWS Certified Cloud Practitioner CompTIA A+ Cisco CCNA Amazon AWS AWS Certified Developer - Associate CompTIA Security+
Photoshop Graphic Design Adobe Illustrator Drawing Digital Painting InDesign Character Design Canva Figure Drawing
Life Coach Training Neuro-Linguistic Programming Mindfulness Personal Development Personal Transformation Meditation Life Purpose Coaching Neuroscience
Web Development JavaScript React CSS Angular PHP WordPress Node.Js Python
Google Flutter Android Development iOS Development Swift React Native Dart Programming Language Mobile Development Kotlin SwiftUI
Digital Marketing Google Ads (Adwords) Social Media Marketing Google Ads (AdWords) Certification Marketing Strategy Internet Marketing YouTube Marketing Email Marketing Retargeting
SQL Microsoft Power BI Tableau Business Analysis Business Intelligence MySQL Data Analysis Data Modeling Big Data
Business Fundamentals Entrepreneurship Fundamentals Business Strategy Online Business Business Plan Startup Freelancing Blogging Home Business
Unity Game Development Fundamentals Unreal Engine C# 3D Game Development C++ 2D Game Development Unreal Engine Blueprints Blender
2020-11-30 16:24:03
30-Day Money-Back Guarantee

This course includes:

  • 4.5 hours on-demand video
  • 4 articles
  • 42 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
Office Productivity Microsoft Excel VBA

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
Rating: 4.3 out of 54.3 (5,725 ratings)
85,117 students
Created by Gopal Prasad Malakar
Last updated 11/2020
English
English
30-Day Money-Back Guarantee

What you'll 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

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

Workout Example 10 - Running SQL command through VBA. Read Excel sheet as a table inside VBA

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 Example 11 - Voice based reminder tool

You will learn how to remind speaker about how much time is left through automated announcement.

Workout Example 12 - Running VBA code in automated manner

You will learn what is takes to used Windows scheduler to schedule the VBA code to run in automated manner with desired frequency.

Workout Example 13 - Recursive file and folder size within a folder

Many a times we need a tool, which should recursively put file names and file sizes within a folder.

I mean to say that, if you pass name of a folder and say in that folder, it has

  1. 11 files

  2. 2 folders


    Then it should again go into the 2 folders and if it finds a folder again say in any of the folder, it should go within that folder too and should create result, with name and size of all the files & folders.

Workout Example 14 - Combine all the files inside a folder through VBA

Tasks

1. Combine Multiple files - having same layout (same number of fields and fields are always in the same column) but not neessarily same number of rows

2. Also put file name as first column in the final data set


Assumption:

  1. All the data files are in the same folder

  2. There is nothing other than data files in that folder

  3. Usually data files should have a way to know, which file was referred

  4. All the data files are excel files only


Who this course is for:

  • Microsoft Excel users who wants to learn automation and VBA programming
  • Someone who wants to learn by seeing workout examples

Course content

4 sections • 35 lectures • 4h 31m total length

  • Preview03:57
  • Automate without coding - recording macro with relative reference
    09:49
  • FAQ - for recording macro
    00:58

  • Preview12:00
  • Understand Excel VBA syntax
    15:42
  • Understand Excel VBA syntax continued ....
    16:54
  • Understand conditional execution (if else conditions)
    11:30

  • Preview05:32
  • Workout Example 1 - Part B 01- Design your first Excel VBA form
    10:15
  • Workout Example 1 - Part B 02- Design your first Excel VBA form
    09:28
  • Workout Example 1 - Part C - Explanation of demo Excel VBA form & code
    09:08
  • Preview11:17
  • 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
  • Preview10:22
  • Workout Example 4 - Part B - Coupon Assignment and Inventory management
    09:02
  • Workout Example 5 - Reading microsoft access data and writing into a text file
    05:51
  • Preview09:53
  • Workout Example 6 - Part B- Design survey form in excel VBA (deal with list etc.
    07:29
  • Workout Example 7 - Storing Excel VBA Form data into MS Access database
    06:40
  • Preview14:04
  • Workout Example 8 - Part B- Using Pivot Table, VLOOKUP etc. inside a VBA code
    10:30
  • Preview05:03
  • Workout Exp 09 - 02- Windows based user authentication and Voice notification
    01:10
  • Workout Exp 10 - Reading worksheet data into a table and Running SQL inside VBA,
    06:59
  • Preview01: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
  • Workout Exp 13 - Recursive file size of all the files n folders within a folder
    10:00
  • Workout Exp 14 : Combine multiple files through VBA
    10:36

  • FAQ (Will keep growing based on Studen't questions)
    00:18
  • Automatic update of Excel Graphs / Charts and color coding (in fact without VBA)
    00:26
  • Bonus Topic - Analytics / Data Science / Machine Learning Interview questions
    06:02
  • Closing note
    01:30

Instructor

Gopal Prasad Malakar
Trains Industry Practices on data science / machine learning
Gopal Prasad Malakar
  • 4.2 Instructor Rating
  • 9,239 Reviews
  • 104,816 Students
  • 16 Courses

       I am a seasoned Analytics professional with 18+ years of professional experience. I have industry experience of impactful and actionable analytics, data science, decision strategy and enterprise wise data strategy. 

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, business intelligence systems like tableau /SAS Visual Analytics, MS access based database application development,  Enterprise wide big data framework and streaming analysis. 

Please refer to my course for 

- SAS / R program details (syntax and options)

- SAS / R output deep dive

- Practical usage in Industrial situation

  • Udemy for Business
  • Teach on Udemy
  • Get the app
  • About us
  • Contact us
  • Careers
  • Blog
  • Help and Support
  • Affiliate
  • Terms
  • Privacy policy
  • Cookie settings
  • Sitemap
  • Featured courses
Udemy
© 2021 Udemy, Inc.