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
Outline:
-
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
Duration:
2 days (14 hours)
Target Audience:
This course is aimed at experienced Excel users who wish to automate repetitive tasks or simplify more complex tasks using Excel macros and Visual Basic for Applications (VBA) programming language
Methodology:
Demonstration, practical learning, case discussion and exercises
Prerequisite(s) or equivalent knowledge:
Participants must have good Microsoft Excel skills and understand key concepts of spreadsheets. It is not necessary to have prior programming knowledge.
Methodology:
Demonstration, practical learning, case discussion and exercises
Prerequisite(s) or equivalent knowledge:
Participants must have good Microsoft Excel skills and understand key concepts of spreadsheets. It is not necessary to have prior programming knowledge.
US 15579 
GB 13368 
CA 10818 
AU 8818 
IE 6277 
BR 2899 
NZ 1891 
CN 1347 
