VBATrainingContents

You will learn it all

Get Started

  • What is computer programming?
  • What is VBA?
  • How VBA Works?
  • What are objects, How they works?

Macros

  • What are macros?
  • How to Records Macro?
  • How to clean Macro?
  • Why do we need VB scripting?

Program Structure

  • Variables, Data Types
  • Scope – Public, Dim
  • Sub Procedure, Function Procedure
  • Event Procedures

VBA Components

  • VBProject
  • Workbook
  • Sheets
  • Userfrom
  • Class Module

Input and output in VBA

  • InputBox, Msgbox
  • Range, Userform, Text File

Data Processing

  • Loops
  • If Statement
  • Select Case
  • String Manipulation
  • Numeric Calculation
  • VBA array, Dictionary

Excel Object Model – How to refer

  • Workbook, Worksheets, and Range in VBA
  • Understanding Active Model

Workbook operations

  • Open a new file
  • Open a existing file
  • Save a file
  • Close a file

Worksheet Operations

  • Adding a worksheet
  • Removing a worksheet
  • Renaming a worksheet
  • Copying and Moving a worksheet

Range Operation

  • Name Range
  • Current Region, UsedRange
  • Last row and Last column
  • Offset, Resize, Intersect, Union

Excel Operations – VBA

  • How to sort data?
  • How to Filter Data ( Autofilter, Advance Filter)
  • How to remove duplicates
  • How to group and ungroup rows and columns
  • How to do subtotal

Automate charts and Graphs

  • Insert Chartsheet and ChartObject
  • Add Data into chart
  • Modify series
  • Modify series points
  • Create dynamically updating charts.
  • How to create pivot table?
  • How to add fields and pivot table?
  • how to filter fields in pivot table?

Working with VBA userforms

  • Create simple data entry form
  • Create dynamic data entry form
  • Listbox, checkbox, Combobox
  • Option buttons, Groups

File/Folder Operation

  • How to get path
  • how to validate a path.
  • Read, Write Text file
  • Delete files and folders
  • Create Folder
  • Copy and Move file and Folders
  • Working with FSO ( FileSystemObject)

VBA Array

  • Option Base
  • Static and Dynamic Array
  • Assing Range to Array
  • Assign Array to Range
  • Redim, Redim Preserve
  • Split, Join, Transpose
  • Filtering Array, Extracting Array.

Connecting Excel with Other Application

  • Understand object Binding
  • Late and Early binding
  • Connect Excel with access
  • Connect Excel with Word
  • Connection Excel with Powerpoint
  • Connect Excel with internet explorer

Projects

Extensive use of VBA Dictionary and Array.