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.
- Lectures 22
- Quizzes 0
- Duration 50 hours
- Skill level All levels
- Language English
- Students 0
- Assessments Yes
OVERVIEW OF ADVANCED VBA
- Learning 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.
- VBA introduction – Overview
- Variable definition and scoping
- Understand the object model of VBA
- Protection and event handling
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.
- Different Loops in VBA – FOR, DO WHILE, WHILE
- Conditional Statements – IF, IF-ELSE, SELECT CASE
- Data handling methods – ARRAYS, COLLECTIONS, DICTIONARIES
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.
- Charts – Data manipulation, setting source data, properties modification
- Form controls – Understand the uses of form controls
- Handling shapes and their properties in VBA
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.
- 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
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.
- User form design and properties
- User form events
- Data manipulation using the form