- VBA is an integral aspect of any analytics related course. This is because VBA finds multiple applications in Excel related tasks such: 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. Throughout the course, you’ll be given hands-on assignments and case-studies to put the theoretical knowledge into practical application.
Microsoft Excel VBA Certification Training
Instructor
Mike
- Description
- Curriculum
- Reviews
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
- Advanced VBA Course will help you master your VBA coding skills and take it to the level required to crack VBA interviews. This course is designed to equip you with skills to create and work on VBA capabilities like:
- Understand VBA coding syntax and familiarizing with VBA development
- Master the concepts of Events, form controls, shapes, user-defined functions and utilizing the different logic building capabilities of Excel
- Use advanced charts, graphs, and Pivot tables through VBA to provide greater control over User Interface and calculation logic
- Understand the importance of loops and conditional statements while using VBA to build projects
- Basic data handling and manipulation by the use of Arrays, collections, and dictionaries
- The following professionals can take up this VBA in Excel course:
- Data Analyst / Business Analyst / Assistant Manager
- Financial Analyst
- Advanced Excel User
- Fresher seeking jobs in Analytics/Consulting job streams
- 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.
-
1
Overview of Advanced VBALearning Objectives: In this module, you will get introduced to why VBA is a necessary aspect of any analytics function. The goal is to ensure that students become acquainted with the VBA environment, coding basics, and functionalities of VBA.
Topics:
- VBA introduction - Overview
- Variable definition and scoping
- Understand the object model of VBA
- Protection and event handling
Hands-On:
- Hands-on on basic VBA to get you started
- How to link Excel front-end to VBA code
-
2
Loops, Conditional Statements, and Data Handling in VBA
Learning Objectives: In this module, you will learn about the loops and conditional statements in VBA. This module covers topics on different types of data storage and handling techniques in VBA. It will be a good starting point to write complex VBA code to automate repetitive tasks and handle data structures.
Topics:
- Different Loops in VBA – FOR, DO WHILE, WHILE
- Conditional Statements – IF, IF-ELSE, SELECT CASE
- Data handling methods – ARRAYS, COLLECTIONS, DICTIONARIES
Hands-On:
- Exercise to demonstrate the loops implementation – definition of criteria for loop execution, number of runs and difference between the different loops
- Explanation of the conditional statement through complex business logic to demonstrate capability in real time
- Live dataset storage, access, and manipulation using all the points defined above plus VBA specific data techniques
-
3
Working with Charts and Shapes
Learning Objectives: In this module, you will learn how to handle the various shapes and charts using VBA. This module covers the topics for data manipulation, setting source for chart data and accessing different chart properties like series color, legends, axis formatting, overall general formatting. Then the focus will shift to the different form controls in VBA like dropdowns, list boxes, option buttons, etc.
Topics:
- Charts – Data manipulation, setting source data, properties modification
- Form controls – Understand the uses of form controls
- Handling shapes and their properties in VBA
Hands-On:
- Live exercise to demonstrate the functionalities of all the listed items above
-
4
Application Interaction using VBA
Learning Objectives: In this module, you will learn how to work with the advanced interaction between different workbooks and office applications. This module covers topics like opening different files, reading data from them and writing data through a single workbook. Also, there will be an overview of generating PDF and PPT reports from within Excel using VBA.
Topics:
- File handling through VBA
- Data manipulation between different workbooks
- Consolidation of data into a single workbook
- Export data from the workbook in PDF, PPT, and Excel formats
Hands-On:
- Hands-on guidance to walk through the file handling, data manipulation and export functionalities of VBA
- Understand best practices to implement the above functionalities
-
5
User Forms in Excel
Learning Objectives: In this module, you will learn how to use user forms in Excel and VBA to create a great user experience in your workbooks. This module will serve as a base for the final project which will utilize all the topics described above. User forms provide a dynamic way to select, filter and enter data into the workbook.
Topics:
- User form design and properties
- User form events
- Data manipulation using the form
Hands-On:
- User form setup and data handling through the same
- Live project to connect the user form to front-end Excel
Please, login to leave a review
