 |
| Excel VBA programming by Examples (MS Excel 2016) |
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 this course is for:
- Microsoft Excel users who wants to learn automation and VBA programming
- Someone who wants to learn by seeing workout examples
- Get the course
