VBA Course

Introduction To VBA:

•  What Is VBA? Need and Application of VBA
• Introduction to Developer tab
•  Introduction to Macro Recordings: Using the Excel Macro Recorder, Macro Security, Recording Macro,
  Naming Macro, Executing Macro, Saving and Editing Macro.
•  Working In the Visual Basic Editor: Project Explorer, Properties window, Object Browser, Standard
  Module and Sheet Module.
• Debugging mode, Breakpoints, Bookmarks, Watch window, immediate window and Locals window, Inbuilt
  VBE Help feature
•  Introducing the Excel Object Model: Application, Workbooks, Worksheet Objects
•  Variable, Constant and Data types: Variable Naming rules, Declaring, Initializing Variables, Option
  Explicit, Object Variables
•  Simple Dialog Boxes: Message boxes and Input boxes

VBA Programming Concepts:

• Scope and lifetime of variables
• VBA Sub and Function Procedures
•  Using VBA and Worksheet Functions
•  Working with Range Objects: Properties and Methods of Ranges – Range referencing, selection, active
  cell, cells and offset properties, resize ranges, value, formula and text properties, clear, delete, copy,
  paste and format ranges, Filters, special cells method, union and intersect ranges, current region
  property, working with dynamic ranges, last cell, last row and last column
•  Workbooks and Worksheets: The Workbooks Collection, Getting a Filename from a Path, Files in the
  Same Directory, Overwriting an Existing Workbook, Saving Changes, The Sheets Collection, Worksheets,
  Copy and Move

Controlling Program Flow With Loops And Logical & Error Handling:

• Using Conditional constructs& Loops: If-Then-Else, Select-Case, And/or conditions, Using Looping
  constructs: For-Next, For-Each, Do-While, Do-Until Decision-making and Code Branching, Using Label
  Constructs
• Automatic Procedures and Events
•  Types of Errors: Design Errors, Compile Errors, Runtime Errors. Logical Errors.
•  How to Debug the Errors.
•  Discussion of Formula Writing Skills in VBA.
•  Difference between R1C1 and A1 style for writing formula in VBA.
•  Writing of Basic and Complex formulas in VBA.

Error Handling And Arrays:

•  Data Validation & Input restrictions Effective Coding
•  Testing and debugging your code
•  Error-Handling Techniques
•  Bug Extermination Techniques
•  Using On error Go to and On error Resume next
•  Introduction to arrays
•  Static Arrays, Dynamic Arrays
•  One dimensional, Two dimensional and multi dimensional arrays

Using Names, Sort And Filter, PivotTables And Pivot Charts With VBA Macros:

•  Named Ranges: Naming Ranges, Using the Name Property of the Range Object, Working with Named
  Ranges, Determining which Names Overlap a Range
•  Sorting: Structuring the Data, Sorting a Range, Sorting a Table,
•  Filter: AutoFilter Object, Filter Object, Date Custom Filter, Adding Combo Boxes, Copying the Visible
  Rows, Advanced Filter
•  PivotTables and Pivot charts: Creating a PivotTable Report, Pivot Caches, PivotTables Collection,
  Pivot Fields, Calculated Fields, Pivot Items, Grouping, Visible Property, Calculated Items, Updating Pivot
  Tables, Synchronizing multiple pivot tables, Pivot Charts, External Data Sources

Advanced User Forms And GUI :

•  User Form Basics
•  Using User Form Controls
•  User Form Techniques and Tricks
•  Form Control vs Active X control
•  Accessing Your Macros through the User Interface
•  User Defined Functions & Add ins

Charts And Dashboards With VBA Macros:

•  Charts: Chart Sheets-Adding a Chart Sheet Using VBA Code, Embedded Charts, Using the Macro
  Recorder, Adding an Embedded Chart Using VBA Code, Creating charts, Defining Chart Series with
  Arrays, Converting a Chart to Use Arrays, Changing chart type and series type, Adding secondary axis,
  Changing chart marker options, Determining the Ranges Used in a Chart, Creating Dynamic Charts,
  Chart Labels, Copying Charts as picture/chart/excel objects into other Microsoft application.
•  Creating Interactive Dashboards: Introduction to dashboard and interactive dashboards, Creating
  dashboard using Named ranges, Charts, Tables, Pivot Tables and user forms, Choosing the right chart –
  Bullet Chart, Thermo meter chart, Using Alerts in dashboards, Interactive Dashboard Examples