Overview:
VBA is an integral aspect of any analytics related course. This is because VBA finds multiple applications in Excel related tasks such as automation, interactions between Office applications and implementation of business logic in Excel. VBA significantly improves workbook performance and enables tasks which might have been impossible using simple MS – Excel Formulas.
Course Objective:
After the completion of this training, you will be able to
- Create/update/robust macros using VBA coding to create custom solutions, automate repetitive tasks, and increase performance
- Handle/Create Spreadsheets, PowerPoint, Outlook Emails, File and database objects using VBA code
- Access VBA development environment and write generic, documented, easy-to-understand code
- Learn about the different data types in VBA and their specific uses
- Understand the concepts of conditional statements and loops and integrate them into your code
- Learn how to apply formatting via VBA
- Implement basic data handling and storage through the use of arrays, collections, and dictionaries
- Write reusable code for repetitive tasks and call them during normal coding usage
- Learn the various methods to access charts, shapes, form controls like dropdowns, list boxes, etc.
- Learn the basics of error handling in VBA
- Learn about user forms and their applications in VBA
- Implement classes and Object-oriented programming in VBA
Audience:
- Data Analyst / Business Analyst / Assistant Manager
- Financial Analyst
- Advanced Excel User
- Fresher seeking jobs in Analytics/Consulting job streams
Pre-Requisites:
- A thorough understanding of Microsoft Excel will be extremely helpful while going through this course. At the very least, the candidate should have basic knowledge of formulas in MS – Excel.
Course Curriculum
| Overview of Advanced VBA | |||
| VBA introduction – Overview Details | 00:00:00 | ||
| Variable definition and scoping Details | 00:00:00 | ||
| Understand the object model of VBA Details | 00:00:00 | ||
| Protection and event handling Details | 00:00:00 | ||
| Hands-On | |||
| Hands-on on basic VBA to get you started Details | 00:00:00 | ||
| How to link Excel front-end to VBA code Details | 00:00:00 | ||
| Loops, Conditional Statements, and Data Handling in VBA | |||
| Different Loops in VBA – FOR, DO WHILE, WHILE Details | 00:00:00 | ||
| Conditional Statements – IF, IF-ELSE, SELECT CASE Details | 00:00:00 | ||
| Data handling methods – ARRAYS, COLLECTIONS, DICTIONARIES Details | 00:00:00 | ||
| Hands-On | |||
| Exercise to demonstrate the loops implementation – definition of criteria for loop execution, number of runs and difference between the different loops Details | 00:00:00 | ||
| Explanation of the conditional statement through complex business logic to demonstrate capability in real time Details | 00:00:00 | ||
| Live dataset storage, access, and manipulation using all the points defined above plus VBA specific data techniques Details | 00:00:00 | ||
| Working with Charts and Shapes | |||
| Charts – Data manipulation, setting source data, properties modification Details | 00:00:00 | ||
| Form controls – Understand the uses of form controls Details | 00:00:00 | ||
| Handling shapes and their properties in VBA Details | 00:00:00 | ||
| Hands-On | |||
| Live exercise to demonstrate the functionalities of all the listed items above Details | 00:00:00 | ||
| Application Interaction using VBA | |||
| File handling through VBA Details | 00:00:00 | ||
| Data manipulation between different workbooks Details | 00:00:00 | ||
| Consolidation of data into a single workbook Details | 00:00:00 | ||
| Export data from the workbook in PDF, PPT, and Excel formats Details | 00:00:00 | ||
| Hands-On | |||
| Hands-on guidance to walk through the file handling, data manipulation and export functionalities of VBA Details | 00:00:00 | ||
| Understand best practices to implement the above functionalities Details | 00:00:00 | ||
| User Forms in Excel | |||
| User form design and properties Details | 00:00:00 | ||
| User form events Details | 00:00:00 | ||
| Data manipulation using the form Details | 00:00:00 | ||
| Hands-On | |||
| User form setup and data handling through the same Details | 00:00:00 | ||
| Live project to connect the user form to front-end Excel Details | 00:00:00 | ||
Course Reviews
No Reviews found for this course.
0 STUDENTS ENROLLED