Excel Macros

Introduction of VBA

  • What is VBA
  • Advantage of VBA
  • First Excel Macro
  • Recording and executing macros
  • Using the VBA Editor

Variables and Data Types in VBA

  • Declaring Variables
  • Variable Names
  • Scope of Variables
  • Putting Values in Variables
  • Creating Constants
  • Data Types
  • Storage and Data Sizes

VBA Operators and Expressions

  • Simple Arithmetic Operators
  • Relational or Comparison Operators
  • Concatenate Operators
  • Logical Operators
  • Order of Operations
  • Mathematical Expressions

Conditional branching

  • If…Then…Else
  • Nested If
  • Select…Case

Looping

  • While….wend
  • For…Next
  • Do…While
  • Do… Until
  • For Each…Next
  • EXIT Statement

Arrays

  • What an Array Is
  • Creating Arrays
  • Resizing Arrays
  • Two-Dimensional Arrays

Functions and Procedures

  • Creating a Subroutine
  • Understanding Parameters
  • Parameter Passing
  • Function vs Procedure
  • Return Statement
  • Arrays as Parameters
  • Using Functions in Formulas
  • Mathematical Functions
  • Conversion Functions
  • String Functions
  • Working with Dates

Event-driven programming in VBA

  • The role of events in Windows
  • Responding to events through event procedures

Excel Object Model

  • Properties
  • Methods
  • Events
  • Collections
  • With…End With
  • Worksheets
  • Workbooks
  • Ranges (Cells)
  • Charts

Creating User Form

  • Command buttons
  • List and Combo boxes
  • Labels
  • Option buttons
  • Check boxes
  • Text boxes
  • Frames