Microsoft Excel VBA Macro Programming

Objective:

At the end of the training, participants are able to: 

• Record and run macros easily
• Write VBA code manually using Excel
• Perform Loops, Controls and Error Handling in Excel Macro
• Create and apply VBA functions in Excel
• Use Events to trigger specific VBA code
• Write useful VBA application that automate routine tasks

• Introduction to VBA
  • Recognizing the power of Excel VBA Macro
  • Identify workbooks that support macros
  • Showing the Developer Tab
  • Understanding Macro Security
  • Recording and running a macro

• Understanding the Basics
  • Exploring the VBA Editor
  • Examining Recorded Macro codes Introducing the Selection Object
  • Understand Properties, Methods and Parameters
  • Using the Line Continuation Character
  • Using the With…End With statement
  • Writing Comments

• Working with Variables and Constants
  • Understanding the Scope of Variables
  • Identifying the different Data Types
  • Working with Variables
  • Exploring Object Variables
  • Declaring and using Constants

• User Interaction techniques
  • Using VBA InputBox function
  • Using Excel inputBox method
  • Using VBA MsgBox function
  • Using UserForm

• Overview of Objects
  • Introducing the Application Object
  • Exploring Workbooks and Workbook Object
  • Exploring Worksheets and Worksheet Object

• Referring to Ranges
  • Introducing the Range Object
  • Referencing a range using the UsedRange Property
  • Accessing a range using the UsedRange Property
  • Finding the First Cell with data
  • Referencing the Last Row and Column in the data range
  • Using the Cells Property
  • Utilizing the Offset Property while working with Range

• Loop Structures
  • Using For Each..Next Loops 
  • Using For…Next Loops
  • Using Do…While Loops
  • Using Do..Until Loops
  • Exiting a Loop Early
  • Nesting Loops

• Selection Structures
  • Using simple if statement
  • Using If..Then..Else statement
  • Using multiple If..Then..Else statement
  • Using Select Case statement
  • Using nested if statement

• User-defined Functions
  • Exploring commonly used VBA Functions
  • Create user-defined functions
  • Executing user-defined functions

• Event Programming
  • Understand Event Parameters
  • Working with Worksheet Events
  • Working with Workbook Events

• Error Handling
  • Identifying the different types of errors
  • Debugging tools: Stepping through Code
  • Debugging tools: Breakpoint
  • Debugging tools: Immediate Window
  • Debugging tools: Watch Window
  • Handling Errors by choosing to ignore them
  • Employing basic error handling with the On Error GoTo Syntax

• Hands-on VBA Macro Project
  • Build Database from Report
  • Define the Monthly Project

• Useful VBA Macros
  • Combining Workbooks
  • Separating data into different worksheets
  • Separating data into different workbook