Excel with VBA Training
- Course Content
- Drop us a Query
Excel with VBA training imparts the skills to build proficiency in coming up with solutions for handling repetitive tasks and run in response on occurrence of specific events. The training curriculum is comprised of four sub-sections:
- MS Excel (Advance
- MS Excel (Macros)
- Visual Basic for Applications (VBA)
- MS Access (Advance)
Participants will learn the effective ways of using advance features of MS-Excel and macros. Candidates will learn how to create macros using VBA, a programming language used in Excel. The VBA classes will build the skills for developing automated procedures, customized menus, and easy user interface. The learning curve is extended by delivering knowledge and skills on using MS-Access for managing and accessing data in the database.
By the end of this training, the participant will be able to meet the following objectives:
- Use MS-Access for data and conditional formatting
- Group, ungroup, and protect data
- Create, record, edit, and link macros
- Create procedures, functions, methods, and Excel objects
- reate Visual Basic application by adding controls and menus to the form
- Write program to handle errors and events
- Create, encrypt, and package a database
- Import and export data in MS-Access
- Perform synchronization and sharing of database
- PhD scholars
- Data analyst
- Decision makers
- Report creators
The experienced MS-Excel users and professionals dealing with large amount of data and have strong interest in developing programs in VBA are the ideal candidates for this training.
- 1. MS Excel (Advance)
- 2. MS Excel - Macros
- 3. MS Visual Basic for Applications (VBA)
- 4. Microsoft Access 2010
MS Excel - Advance
1. Excel Quick Overview
- Use of Excel, its boundaries & features
2. Data Formatting & Custom settings
- Number, Text, Date, Currency, Custom settings. Data formatting & cleaning
3. Conditional Formatting
- Once defined, it will automatically change color of values e.g. up or down, high or low, pass or fail, profit or loss etc.
4. Filters, Queries & Data Sorting
- Drill down your data to your desired level. Sort data based on your choice e.g. like North, East, West, South rather than alphabetically.
5. Formula Writing & Fixing Errors
- Financial, Logical, Text, Day and Time, Statistical, Mathematical etc Sum, Average, Count,Minimum, Maximum, Absolute, Concatenate, Count, CountA, CountBlank, CountIf, Day,Today, Even, Exact, Exp, Find, Int, IsBlank, Left, Len, Lower, Upper, Proper, Now, Power, Rank, Right, Round, Trim etc.
6. If‐Then‐Else & Nested If commands
- Produce different set of results based on slabs, conditions.
7. Vlookup / Hlookup
- You have large amount of data placed at different locations & you want to merge it based on common values & it’s relevance.
8. Graphs & Charts
- In this workshop you will learn how to create, modify & update graphs / charts like Column, Line, Pie, Bar, Area, Scatter, 3D etc.
9. Hyper / Data Linking
- Hyper & data linking, within or outside Worksheet / Excel File is an important feature. This helps update data automatically.
10. Grouping ‐ Ungrouping
- Sometime data needs to be grouped for summarized results & crisp view. Grouping & ungrouping feature will help you do so.
11. Pivot Tables
- Summarize your data as per your desire. You need to select data in a range & produce consolidated results in user defined reporting formats.
12. Macros Recording, use, editing, linking:
- Record, edit & use Macros with little or no programming knowledge.
13. Security & Protection
- It will help you protect your cells, worksheet or an excel file from unauthorized use.
14. Data Validation
- Prevent wrong data entry in your specified format.
MS Excel - Macros
1. Creating & Recording a Macro
- First step towards learning Macros programming is to learn how to create & record macros. This is the 1st step towards macro programming.
2. Executing Macros through shortcuts and command buttons
- This is using such features based on user requirements.
3. Editing recorded syntax
- How to edit or write your own Macro programs
- Learn about modules which is an excellent feature of VBA programming.
- Write & practice programming procedures. Understand their flow.
6. Sub Procedures
- Write & practice programming procedures.
- There are certain inbuilt or user defined functions. Explore those functions
8. Objects and collections
- An object is a special type of variable that contains both data and codes. A collection is a group of objects of the same class. The most used Excel objects in VBA programming are Workbook, Worksheet, Sheet, and Range.
9. Workbook and workbook objects
- A workbook is the same as an Excel file. The Workbook collection contains all the workbooks that are currently opened. Inside of a workbook contains at least one worksheet.
10. Range object & cells property
- Range represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3‐D
11. Methods & properties
- Each object contains its own methods & properties. A Property represents a built‐in or user‐defined characteristic of object.
12. Object variables and arguments
- Sometime a method takes more than one argument. For example, the Open method for the Workbook object, takes 12 arguments. To open a workbook with password protection, you would need to write the code.
13. If Case, Loop Case & Select Case
- Looping, Sub looping, looping through a range, For, Next loop etc.
14. Excel VBA tutorial
- VBA tutorial will help you proceed in a step by step approach
MS Visual Basic for Applications (VBA)
1. Your First Visual Basic Program
- Welcome To Visual Basic
- A Simple Project
- Using the Application Wizard
2. Adding Controls to Forms
- Visual Basic Controls
- Creating and Manipulating New Controls
- Changing Design Time Control Properties
- Visual Basic Built‐In Controls
- Control Design Tips
3. Understanding Events
- Introducing Events
- Event Procedures
- The Form Load Event
- The Form Resize Event
- Command Button Click Event
- Text Box Change Event
- The Timer Control's Timer Event
- GotFocus / LostFocus Events
4. Working with Forms and Controls
- Setting Properties at Run Time
- Setting Form and Control Properties
- Using ActiveX Controls
- Adding ActiveX Controls to a Project
- Using the Monthview ActiveX Control
- Extra ActiveX Samples
5. Creating and Using Menus
- Menus in Visual Basic
- Using the Menu Editor
- Working with Menus
- Manipulating Menus at Run Time
- Reusing Menus
- Creating Popup Menus
6. Compiling and Distributing Applications
- Finishing Touches
- Project Properties
- Native Code vs. p‐code
- Creating Your Executable
- Using the Package and Deployment Wizard
7. Working with VBA
- Creating a Simple Procedure
- Variables and Parameters
- Data Types
- Using Constants
- Investigating Built‐In Functions
- Branching Structures and Looping Structures
8. Handling Errors
- Handling Syntax Errors
- Handling Run‐Time Errors
- The Error Handling Standard
- Taking a Closer Look
- Who Handles Errors?
9. Debugging Applications
- Handling Logic Errors
- Watch Expressions
- The Call Stack
- The Debugging Process
10. More VBA Issues, Form and Control Issues
- Scope, Lifetime, and Precedence
- Passing by Value and by Reference
- Optional Arguments
- Using Multiple Forms
- List Boxes Revisited
- Control Arrays
11. Adding Simple Database Support
- Support for Data Access in Visual Basic 6.0
- Using the ADO Data Control
- Binding Controls to Data
- Writing Code for the Data Control
Microsoft Access 2010
- Creating an Execute‐Only Database
- Encrypting a Database
- Using the Access 2010 Runtime Program
- Packaging a Database
- Synchronizing a Database with a SharePoint List
- Importing Data from and Exporting Data to SharePoint Lists
- Sharing a Database
The training was good covering the topics with right pace. The trainer was good, calm and gentle with good knowledge of Primavera. This training will help me in my career growth and providing effective solutions.Siddharth Kumar, India Primavera Training
Attending CEH training at Xpert Group fulfills my objective completely. Training starting from the basics of networking to the advanced level of hacking skills gives me great exposure to the ethical hacking domain. Now, I’m confident enough to meet the security talent crisis of my organization.Sagar Khajuria CEH Training
Working for an investigation agency, I always realize the skills lacking in computer forensic. With the fire to overcome this hurdle I attended CHFI training at Xpert Group in Noida. Learning forensic skills from an expert was really exciting. Listening to his case studies provides surplus information on the investigation process and tricks. Thank you Xpert Group for this great learning experience and preparing me for clearing the certification exam.Anand Khosla CHFI Training
Great training experience at Xpert Group. The trainer with a highly practical approach makes the training very informative and interesting. His interpersonal skills make me feel comfortable in the sessions. Good place to take up SAS training.Dr. Deepak Puri, Australia SAS Training
The knowledge and the teaching quality provided by the trainer was exceptional. The way he introduced electronics and relate it to the practical applications had changed my way of thinking. The facilities available helped in improving the learning experience. I honestly appreciate the trainer and Xpert Group.Akshay Puri Delhi Technological University, Verilog, CMOS and FPGA Training
My SAS training at Xpert Group was great and the trainer was excellent. I am happy because they not just gave training, but also provided motivation and career guidance. Due to their efforts, I got a great job and I am happy to have chosen Xpert Group. Many of my friends have also completed trainings from here and they are glad too.Anil Thakur, Delhi Base & Advance SAS Training
I attended cisco, microsoft, CompTIA and CEH trainings here. My training experience at Xpert Group was really great! The staff is friendly and ready to help. Most importantly, all the trainers are ready to answer all your queries. They had extreme patience and cooperated with me at all times. They covered the complete course content and also gave the right path to prepare for exams. They made my visit to India successful.
I’d surely suggest Xpert Group to my friends here. Cheers!
I would like to thank you and your team for providing me "CBAP Exam Prep Training Workshop" successfully. I really appreciate everyone for being so responsive, professional and kind enough to help me in all aspects. The training happened remotely, however I really did not get the feel of distance gap. The trainer helped me to related the course concepts with the IT industry that honed my creative thinking skills.
Thank you to everyone.
Attending RHCSA training from Xpert Group was a complete package of learning, fun, and problem solving. I appreciate the teaching style of the trainer and his involvement into sessions.
I’m sure Xpert Group can be the right training destination for training on any technology.
Learning Website designing from Xpert Group was very productive. The skills I gained here helped me to get a job shortly after training. I’m very thankful to the trainer and administration staff there for helping me out in various ways.Ankur bansal, Ghaziabad Web site designing Training
Xpert Group team is great in providing visionary counseling. I’m highly benefited and can recommend my friends and others to once visit Xpert Group before closing any training related decision. I attended both Base SAS and Advance SAS trainings. The trainer was an expert in his domain and solved all my queries in a very satisfactory manner. The study material provided here helps me to a great extent in preparing for the examination and qualifying it in the first attempt only. Now I’m on the way to my dream jobAnand Prakash Sahu Binary Semantics Ltd., Sr. Data Analyst Base & Advance SAS Training
Xpert Group is a good organization providing specialized training in all the demanding technologies and management areas. I attended ITIL® training for clearing the certification exam. I’m thankful to the trainer for imparting quality training. I congratulate Xpert Group for maintaining the standards in training.Manoj Kumar Sharma, HCL Application Support Manager
Xpert Group is a pool of professional courses. I learned the concepts of Core-Java and Android using various tools. The trainers with sound domain knowledge made the learning sessions interesting. THE LMS provided here as part of course was an additional help and a complete trainer in itself.Maninder Pal Singh Core-Java and Android Training
Few days back, I found the reviews for Xpert Group to be better as compared to other institutes in Delhi/NCR for ITIL V3 Foundation certificate exam preparation. I enrolled here and found the training sessions to be amazing, full of examples. LMS is really an useful tool covering detailed explanation of the topics and practice mock tests. The program managers are very supportive and sincere towards the trainee requirements. Overall, I’m very happy and satisfied on clearing my exam and getting knowledge from Xpert Group.Vinay Kumar Verma, Aircel Ltd., Assistant Manager ITIL® training