Advanced Excel, VBA and Automation

No Reviews Yet

Course type: Online Instructor led Course

Platform: Skype

Course ID: 41915

Course type: Online Instructor led Course

Platform: Skype

Students Interested 0 (Seats Left 0)

No Reviews Yet

Date and Time

Not decided yet.

Arunendra Singh Gaharwar picture

About Arunendra Singh Gaharwar

10 years of experience in MIS and Automation industry working on SAS, SQL, Excel and VBA.
No reviews currently Be the First to Review

About the Course

Excel VBA course is designed for audience who have prior knowledge of Excel, and are keen to learn Excel automation. This Program is designed for beginners in VBA programming, and covers programming concepts from basic to advanced level of programming.


COURSE DESCRIPTION:


Excel VBA course is designed for audience who have prior knowledge of Excel, and are keen to learn Excel automation. This Program is designed for beginners in VBA programming, and covers programming concepts from basic to advanced level of programming.


LEARNING OBJECTIVE:


At the end of this training, you will be able to: 
*Create automation for manual tasks performed through Excel
*Generate reports from data sets
*Organize & Managing data easily using the tool
*Organizing data into multiple workbooks/worksheets
*Create dynamic Pivot Tables


COURSE PREREQUISITE:
A good working knowledge knowledge on excel is must.
COURSE DURATION: 20 hrs


COURSE OUTLINE:
Section 1: Basic of Macros in Excel


Lesson 1:Visual Basic Editor (VBE) in Excel
Operations and manipulations with VBE, Visual Basic (writing procedure/Functions, Modules, User Forms), Projects window/Code Window/Property window, Toolbars/Object Browser (Library Functions)


Lesson 2:The Project Window in the Visual Basic Editor of Excel
Detailed Explanation of project Window, where you see all the opened Workbooks; where you add modules and user forms.


Lesson 3:The Properties Window in the Visual Basic Editor of Excel
The properties of the Workbooks; the names of the modules and the properties of all the components of the VBE.


Lesson 4:The Code Window in the Visual Basic Editor of Excel
Write and test your first code, see the dropdown lists in code Window.


Lesson 5:Developing Macros in the VBE 
Start with creating new VBA procedures from scratch. Learn about different types of variables, operators, Loops, Functions, and other features.


1.Examples of creating variable with different data types, its limitations and uses
2.How to declare variables in Visual Basic. - Different data types. - Scope of the variable.
3.VARIANT Keyword
4.CamelCasing for naming variables - adding comments in the code
5.Difference between functions and procedures, example of functions and calling the same function inside the excel sheet.
6.Introduction to loops and control structures.
7.Examples on control structures and loops (examples where the user will generate numbers using different loops).
8.The output numbers will be printed in the debug window.


Lesson 6:Testing Macros in the VBE
Use the F5/F8/F9 keys and see what you procedure does in Excel step by step. Back up, correct and re-test.
1.Will be introduced to recording macros and to execute the existing macros.
2.Executing the macros step by step using the f8 key adding break points at necessary places and executing the code continuously by using f5 key.
3.Printing test messages


Lesson 7:Excel Macros Recorder 
Use the recorder to develop macros rapidly, if you want to do more the Macro recorder is the best teacher and will be great assistant even when you become an expert at programming in VBA. It writes the code for you. In this chapter you will learn about and try the Macro recorder. You will run the macro that you have recorded.
1.Will record macros and learn how to record macros
2.Participants will be introduced to two types of recording macros Absolute and Relative Reference.
3.Also show how to call these macros from the VBA code


Lesson 8:Modifying Macros in the VBE
Modify the recorded and existing manually created macros.
1.Recorded will be static - if we record a macro for 10 rows it will work only for these 10 rows.
2.In this session we will customize this in such a way that it will work for any number of rows depending on the data
Gem


Lesson 9:VBA Security and protection.
Disable Macros/Enable macros, password protection for a VBA code, Excel file. Protect your code, your sheets and your computer.
1.Excel VBA protection will be introduced to the participants.
2.Normally viruses to Office files are written using VBA, we will show how to bypass these.


Lesson 10:VBA Events
For a procedure to start it must be triggered, clicking on a button can close a work book, also the value of cell can be changed.
1.Normally Visual Basic Code will not be executed as normal programming languages like C and C++ from the first line to the last line.
2.VB code is Event Driven that is it waits for an event to occur and then it executes the code the related code.
3.There are events associated to different scenarios, events associated to controls that we use and events with file.


Section 2: Programming Macros in Excel


Lesson 11: Code in VBA for Excel
Coding tips and tricks, How to select the cell how to enter the value into the cell, how to modify, how to change the text background etc. basic...
1.Example of loops and control structures will be recalled once again. All these numbers will be printed in an excel sheet from top to bottom, left to right and bottom to top.
2.In this place the participants will be introduced to two ways of writing code one is Range keyword and the other keyword is Offset.


Lesson 12:Working with the Application, Workbooks, worksheets
Application events using VBE like, Open (), workbook_changed (), Quit () List of function to operate the excel workbook, sheet (cut/copy/paste), Chart
1.Introducing the user to Application, Workbook and Worksheet Events and their hierarchies.
2.Working with chart control from scratch.


Lesson 13:Playing around the Worksheet
Different function to operate the worksheet, calculation and more.
1.Introduce the participant to WorksheetFunction and show different ways of calculation.


Lesson 14:Working with Message and Input Boxes
Interacting with the user with minimal effort using dialog boxes.


1.Using Message boxes to display messages to the user
2.Accepting data from the user using input box.


Lesson 15:Dealing with Errors
How to deal with errors in VBA code, how to add error handler to handle the run time error.
1.Handling exceptions in Visual Basic


Lesson 16:Working with External Data and SQL.
When you connect to outside source of data (large database, text files, other excel workbooks, Access etc.) the computer is using SQL structure Query language) a specialized language to work with data. You can use Excel's functionalities to connect and extract data but you can also use directly the SQL language. It is much faster.
1.Importing data from the Database. - overview level only
2.Importing data from the web. - use the ribbon option and the code option available - basic level knowledge only
3.Importing data from a Text /csv file.
4.Importing data from OLAP cube.


Lesson 17:Working with Windows and other Microsoft Programs.
With VBA for Excel you can develop VBA procedures (macros) to work within in Excel while calling other Microsoft programs like Access, Notepad, Word, Project and even Windows.
1.How to start an new instance of different applications
2.How to interact with these programs.


Section 3: User Forms and Controls in Excel


Lesson 18:Forms (User forms) in VBA for Excel.
Develop User Forms; Manage and Set the Control Properties, access the control with VBA code.


Lesson 19:Properties and VBA code for Command Buttons.
The "CLICK" control where most of the code resides in a user form.


Lesson 20:Properties and VBA code for Labels & Text Boxes.
The user just sees it and cannot submit any information with it.


Lesson 21:Properties and VBA code for Combo Boxes
The BIG control, It is a drop-down list.


Lesson 22:Properties and VBA code for List Boxes.
When multiple choices are a necessity.


Lesson 23:Properties & VBA code for Check Boxes, Option Buttons & Frames.
The "True/False" controls to be used as a group with a frame.


Lesson 24:Properties and VBA code for Spin Buttons.
When you need to set up a spin box


Lesson 25:Excel VBA Controls' Properties and VBA Code.
They are the controls to build catalogs and shopping carts.
1.Examples which shows how to use different control at different scenarios.
2.Working with the properties of these controls.
3.How to use these controls on forms and embed it on an excel sheet.

Reviews

No reviews currently Be the First to Review

Discussions

Students Interested 0 (Seats Left 0)

Post your requirement and let us connect you with best possible matches for Microsoft Excel Training classes Post your requirement now

Enquire

Submit your enquiry for Advanced Excel, VBA and Automation

Please enter valid question or comment

Please enter your name.

Please enter valid Phone Number

Please enter the Pin Code.

By submitting, you agree to our Terms of use and Privacy Policy

Connect With Arunendra

You have reached a limit!

We only allow 20 Tutor contacts under a category. Please send us an email at support@urbanpro.com for contacting more Tutors.

You Already have an UrbanPro Account

Please Login to continue

Please Enter valid Email or Phone Number

Please Enter your Password

Please enter the OTP sent to your registered mobile number.

Please Enter valid Password or OTP

Forgot Password? Resend OTP OTP Sent

Sorry, we were not able to find a user with that username and password.

We have sent you an OTP to your register email address and registered number. Please enter OTP as Password to continue

Further Information Received

Thank you for providing more information about your requirement. You will hear back soon from the trainer

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 25 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 6.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more