VBA

VBA

About

Data Analysis, Task Automation, Make Repetitive Reports, Interactive Dashboard etc.

Prerequisites

Working Knowledge of excel up to Advanced User Level, Little / No prior Experience of Programming Knowledge.

Faculty Profile

• 15+ Years of Industry Experience in BPO, KPO, Data Analysis and Research Field
• Specialized in Report Automation, Business Dashboard, MIS Reports, Visual Analytics, Trend Analysis and Forecasting
• Conducted Over 150+ Training Sessions at Various Colleges and Corporate

Curriculum

BASIC EXCEL

  • Basic Excel
  • Functions – VLOOKUP, SUMIFS, COUNTIF, etc.
  • Excel file types
  • Excel Shortcuts

DEVELOPING MACROS

  • Macro Security Options
  • Create a Macro with the Macro Recorder
  • Edit a Macro
  • Debug a Macro
  • Different ways of running a Macro
  • Relative Reference, Absolute Reference
  • Quick Automation techniques

THE VISUAL BASIC ENVIRONMENT

  • Getting familiar with VBA environment
  • The Project Explorer
  • Properties
  • Immediate Window
  • Watch Window
  • Code window
  • Module
  • Predefined VBA Objects
  • Methods
  • Objects
  • Procedure

PERFORMING CALCULATIONS

  • Creating Subroutines
  • Create User-defined Functions
  • Calling Subroutines and Functions from other Subroutines

WORKING WITH OBJECT AND RANGE

  • Worksheet objects – Activesheet, other sheet, create sheet, delete sheet
  • Range Object
  • Copying, pasting range
  • Different Range commands
  • Reading and Writing Worksheet values

COMMUNICATING WITH THE USER

  • Simple Message Boxes
  • Message boxes that return values
  • Buttons and icons
  • Input Boxes Variables
  • Declaring Variables
  • Using Variables
  • Watching Variables

CONTROL & DECISION STRUCTURES

  • Conditional Statements > If-Then-Else, ElseIf
  • Decision Structures
  • Select Case Looping
  • For next loops
  • Do While
  • Do Until Error Trapping
  • Learning, and, Using Arrays
  • Decision Construct in VBA

ERROR HANDLING AND DEBUGGING

  • Determining breakpoints
  • How to step through code
  • Working with break mode during run mode
  • Identifying the value of expressions
  • Understanding error handling
  • Understanding VBA's error trapping options
  • Trapping errors with the on error statement

VBA FORM & TOOLBOX

  • Working with Forms
  • Working with Form Controls like Buttons, Combo, Text box
  • Creating UI form
  • Writing UI data to Worksheet

FINAL CASE STUDY & ASSESSMENT

  • Collection of data
  • Data Analysis and Methods
  • Quiz and Assessment