Excel Macro Training

Customized In-Hose Training program for Corporate, B-Schools, Colleges across India & International

DURATION: 20 Hours (3 Full Day)

Course Content

OVERVIEW

• 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