MS EXCEL VBA

What is VBA or Macros?

What it can be used for

Activate the Developer Tab

Macro Security Settings

RECORDING A MACRO

Naming & Recording a simple Macro

Recording using Relative & Absolute References

Editing a Recorded Macro in the VBA Editor

Saving the File as Macro Enabled Workbook

RUNNING AND ASSIGNING A MACRO

Running a Macro using a Toolbar

Assigning a Macro to a Button or Drawn Object on a Sheet 

Assigning a Macro to a Quick Access Toolbar

Creating a Short-Cut Key to Run a Macro 

THE VBA EDITOR

Opening and Closing the VBA Editor

Using the Project Explorer

Inserting a Module

Working with the Properties and Code Windows

UNDERSTANDING VBA TERMINOLOGY

Module, Procedures, Functions

Object, Property, Methods

Application, Workbook, Worksheet, Range

PROCEDURES

Creating a Sub Routine

Understanding the code structure

Commenting Statements

Calling procedures

USER INTERACTION

Creating an Interactive Message Box

Creating a Custom Input Box

Advantage of Application.Inputbox

CONDITIONAL STRUCTURES

Simple IF (IF…THEN)

IF Else (IF…THEN…ELSE)

IF & Logical Operators (IF AND/OR…THEN)

Nested IF (IF…THEN…ELSEIF)

SELECT CASE

CASE ELSE

VARIABLES AND CONSTANT

Declaring a Variable’s Data Type

Working with Object Variables

Option Explicit

Constants

CONTROL STRUCTURES (LOOPS)

For Loop (FOR…NEXT)

For Each Loop (FOR EACH…NEXT)

Do Until Loop (DO UNTIL…LOOP)

Do While Loop (DO WHILE…LOOP)

WORKING WITH RANGE

Referring to a Range

Entering Values and Formulas

Formatting Cells and Values

With…Statement

Using CurrentRegion Property

Accessing a range using UsedRange Property

The End Property

Offset

Select Special Cells

Sort & Filter

Rows and Columns (Insert/Delete/Autofit/Hide)

WORKING WITH WORKSHEETS

Activate Worksheet

Add/Delete/Rename a Worksheet

Copy/Move a Worksheet

Working with Group Worksheets

Hide/ Protect Worksheets

WORKING WITH WORKBOOK

Open/Close Workbook

Save/Protect Workbook

This Workbook

WORKING WITH APPLICATION

Screen updating/ Display Alerts

Cut copy Mode/ Username/ Speech

USER DEFINE FUNCTIONS

Using Excel Worksheet Functions in VBA

VBA Function

Creating User-Define Functions

Using Range and declaring variables in User defined functions

CREATING USER FORMS

Creating a custom form

Adding text boxes to forms

Moving controls

Adding label controls to forms

Changing text box and label properties

Combo box, option, command

USING EXCEL EVENTS

Sequences of Events

Events Associated with Worksheets

Events Associated with a Workbook

ERROR HANDLING & DEBUGGING

Understanding different Error Types

Debugging: Stepping through Code and Breakpoint

On Error Goto Statement

Resume Next Syntax