Visual Basic Application

Case Studies & Assignments

  • Class Study Material (Shared with all participant, Workshop Live Case Studies and Multiple sample databases with live case studies along with real-life situations

Delivery Mode

  • Classroom Sessions & Online Sessions

Students Benefits:-

  • Participants get 500+ real-life scenarios and Complimentary & Exclusive 1 Month Post Workshop Support
  • Key focus on Hands-on, practical, and projects
  • After every class, you will get assignments & session recordings
  • Flexibility to re-attend the Live Sessions
  • Almost every industry is hiring employees with 1 or 2 years of experience in VBA (Marcos) than just only MS Excel, You will create a lot of opportunities for yourself after completing this course
  • Companies are offering an average salary for a Data Analyst with VBA (Marco) skill is 4 lacs / Year according to Payscale.

Topics to be Covered

 

Duration

Module

1

Getting Started with VBA

02

Hours

Module

2

VBA Nuts & Bolts

02

Hours

Module

3

Working with Loops, Conditional Statements &

Arrays

03

Hours

Module

4

Formula Writing in VBA

03

Hours

Module

5

User Form & GUI

02

Hours

Module

6

Working with Excel Features

02

Hours

Module

7

Interaction with Other Applications

01

Hour

VBA Program Overview

Module 1

Module 2

Getting Started with VBA

VBA Nuts & Bolts

Quick Recap of Microsoft Excel

  • Introduction to Developer Ribbon Tab

üIntroduction to VBA

  • What is VBA / Macro in Excel?
  • Difference between VBA and Macros

Introduction to Visual Basic Editor Window

  • Open Visual Basic Editor (VBE)

Getting Familiar with Visual Basic Editor (VBE)

  • Project Explorer
  • Immediate Window
  • Run Button
  • Reset Button

Customizing the VBE Environment

  • The Macro Recorder
  • Recording Your First Macros in Excel
  • Steps to record a Macro
  • Relative References vs Absolute Reference in Macro Recording
  • Ways to Run Macros
  • Live Project: Dynamic Cell Selection (Using Excel Go to Feature)

Executing / Modifying Recorded Macros

  • Saving Macro Workbook

Introduction to Sub and Function Procedures

  • Declaring Procedures
  • Procedures’ Scope (Public/Private Procedures)

 Introducing the Excel VBA Object Model (A must know thing to become Pro in VBA)

  • Object Hierarchy (Objects and Collections)

Properties and Methods

  • Working with Object Properties
  • Working with Object Methods
  • Live Case Study –Working with Comment Object

Important and Useful Properties of Application Object

  • Working with Ranges Objects
  • Selecting, Copying, Pasting ranges

Working with Comments

  • Tips to make a best use of comments

Variable, Data Types and Constant

  • Rules for Naming Variable
  • Declaring & Initializing Variables
  • VBA built-in data types
  • Variable Scope and Working with Constant

Finding Last or Next available Row/Column

  • Selecting Rows/Columns/Table (Normally & Dynamically)

Module 3

Module 4

Working with Loops, Conditional Statements & Arrays

Formula Writing in VBA

Introduction to VBA Loops & Conditional Statements

  • Working with Conditional Statements

Types of Conditional Statements

  • Use of Simple and Nested IF conditions
  • IF with AND, OR & NOT
  • Alternative of IF, a Select CASE statement

Working with Loops

  • For Next Loop (aka Counter Loop)
  • For Each Loop
  • Normal Do-Loop
  • Do Until
  • Do While Loop
  • While Wend Loop

Working with Message & Inbox Box

  • With-End with Constructs

Controlling Program Flow

  • Use of GoTostatements

Introduction to Arrays

  • Declaring arrays

Declaring Multidimensional arrays

  • Static Arrays vs Dynamic Arrays

 

  • Working with Dynamic Arrays

Introduction to Excel VBA Functions and Worksheet Functions

  • Difference between R1C1 and A1 style referencing

Difference between Worksheet Functions Vs Active.cell Formula

  • Getting started with VBA Excel Functions
  • IIf, UCase, LCase, Date, StrReverse, Split, Remove Vowels, InStr, Date

Date Serial, Format, Month Name etc.

  • Getting started with Worksheet Function

Working with Function Procedures

  • Getting started with User Defined Functions (UDF)
  • UDF with No, One, & Multiple arguments
  • UDF to Sum Each Digit in a Cell
  • UDF to extract only numbers from alphanumeric cell
  • Function with an Array argument

Fixing arguments of Formulas in VBA

  • Working and Creating Customize Functions

Error Handling & Debugging Techniques

  • Trapping Errors (Ingoing Error or Jumping to specific section)

Module 5

Module 6

User Form & GUI

Working with Excel Features

üUser Form Introduction

  • Basics of UserForm
  • Creating, displaying and unloading UserForms
  • Understanding the settings of UserForm’sProperties

 

üExploring Form Controls & ActiveX Controls

  • List Box Control
  • Scroll Bar Control
  • Check Box

 

üExploring UserFormControls

  • Customizing control toolbox
  • Filling Data into controls
  • Adjusting UserFormcontrols

 

üCycle/Sequence to follow to create UserForm

  • Validating User Inputs

üTesting a UserForm

  • User Form Tricks

Understanding User Form Events

  • Relevant UserFormEvents
  • Control Events

Working with Pivot Tables

  • Recording & Modifying macro to create Pivot Table
  • Writing codes to create Pivot Table Report
  • Working with Pivot Table Fields and Items
  • Updating Pivot Tables
  • Generating multiple Pivot Table reports using VBA

Charts

  • Difference between embedded chart and chart sheets
  • Understanding the Chart object model
  • Creating an Excel Chart using VBA
  • Changing Chart Type and Series Type
  • Modifying Charts
  • Creating Spark-line Chart in Excel using VBA
  • Looping through all charts available in worksheet
  • Adjusting Size and Aligning Chart Objects

Sorting & Filtering

  • Recording a macro to sort the table
  • Understanding and modifying the recorded macro
  • Performing Simple sorting
  • Dynamic and multilevel sorting
  • Filter and display relevant information

Module 7

Interaction with Other Applications

Add-Inns

  • Introduction to Add-Ins
  • Add-Inn vs COM Add-Ins
  • How to Create Add-Inn
  • Installing an Add-Ins

Type of different Excel events

  • Workbook Events (Open, Activate, Sheet Activate, Before Save, New Sheet, Deactivate )
  • Worksheet Events (Activate, Before Right Click, Calculate, Pivot Table Update )

Starting an application from Excel

  • VBA Shell function to launch other programs
  • Creating a new Word Document

Interacting with Microsoft Office Apps

  • Opening a Word Document
  • Opening Outlook
  • Launching PowerPoint

Controlling Excel from another application

  • Sending Personalized emails via Outlook

Sending e-mail attachments from Excel

  • Working with Files & Folders
  • Converting / Saving Excel File into PDF

Learning Outcomes

  • Proficient in working on Visual Basic & Marcos
  • Having good command on the keyboard
  • Skilled in number and different formatting types
  • Capable of handling the large data
  • Design your own User Defined Functions in easy steps using VBA
  • Plotting various charts & tables using Excel & VBA
  • Creating various reports using different features & functions
  • Having strong knowledge of Advanced Formulas
  • Able to analyze any data with Excel VBA
  • Ability to create Good Looking and World Class Dashboards
  • Recording and Cleaning Macro