Automating Task With Microsoft Excel Vba | IT Training & Certification | Info Trek
Respect Your Dreams
Follow through on your goals with courses

Automating Task With Microsoft Excel Vba


Format What’s this?
Starting From
RM 1800.00
  1. 3 Days
  1. HRDF SBL Claimable
  2. Lunch & refreshment provided
  3. Certificate of Attendance available
Starting From
RM 1800.00
  1. 3 Day with 08 hours per day
  1. Mon 15 Jun 09:00 - Wed 17 Jun 17:00
  1. HRDF SBL Claimable
  2. Certificate of Attendance available
  1. 3 Days
  1. All of our private classes are customized to your organization's needs.
  2. Click on the button below to send us your details and you will be contacted shortly.

Automating Task With Microsoft Excel Vba


This course is about using Microsoft Excel as a development tool to design an information system – how to access it, analyze it and give it meaning. The ability to rapidly convert raw data to useful information leads to better decision making, which ultimately leads to better strategy in performing critical tasks to compete, survive and increase market share.


This course is designed for Clerks, Officers, Executives and Managers of all level and personnel who want to learn more in-depth knowledge and practical uses of Microsoft Excel VBA.


Basic knowledge of Microsoft Excel (Foundation & Intermediate) is essential with the follow pre-requisites:

  • Able to switch between task applications
  • Able to minimize, maximize and close Excel application
  • Able to identify where is the Status Bar and Formula Bar
  • Able to create a spreadsheet with simple formatting (Font, Font Size, Font Color, Fill Cell With Color, Border)
  • Insert, Delete and Rename a worksheet
  • Able to perform SUM, AVERAGE, MAX, MIN, IF Functions (Basic Functions)
  • Able To define And use Range Names
  • Able to highlight cells
  • Understand what is dialog box


This program will be conducted with interactive lectures, PowerPoint presentation, discussions and practical exercise


Upon completion of this program, participants should be able to :

  • Learn how to begin building custom applications with four commonly used Excel object : Application, Workbook, Worksheet, Range
  • Used to design custom interfaces in Excel

Expand All


Introduction To Microsoft Excel As A Development Tool

Overview of the Excel Objective and how to use them to design applications.


  • Introduction to Excel Objects

Know what is Excel Objects (Application, Workbook, Worksheet, Range)

  • Getting and Setting Properties

Get and Set Excel Object properties

  • Calling Methods

Use Excel Object Method

  • Using Application Object

Know Excel = Application in VBA

  • Working with Application Properties

Get and Set Application Properties (Caption, Path, Window State, DisplayStatusBar, Display Formula Bar)

  • Working with Application Methods

Use Application Methods (Close, Calculate)

  • Using Workbook Object

Know Workbook in VBA

  • Working With Workbook Properties

Get and Set Workbook Properties (Name, Path, Saved)

  • Working with Workbook Methods

Use Workbook Methods (Activate, Close, Protect, Save, Save Copy As)

  • Using Worksheet Object

Know Worksheet Object in VBA

  • Working with Worksheet Properties

Get and Set Worksheet Properties (Index, Name, Used Range, Visible)

  • Working with Worksheet Methods

Use Worksheet Methods (Activate, Calcilate, Delete)

  • Using Range Object

Know Range in VBA

  • Working with Range Properties

Get and Set Range Properties ( Count, Dependents, Name, Value, Formula, Text)

  • Working with Range Methods

Use Range Methods ( Calculate, ClearContents, Copy)

Language Used In Manipulating Excel Objects

Focuses on the Language use to Manipulate Excel Objects, i.e. Microsoft Visual Basic for Applications (VBA). You will be given an Insight into the most important concepts of VBA and will be introduced to its Basic Components.


  • Introduction to Visual Basic for Application (VBA)

Work Around in Visual Basic Editor

  • Quick Look at VBA

Know How to Write Simple VBA Code

  • VBA Subroutine

Write Subroutine and Function

  • Inserting a VBA Module

Insert a Module into Excel Project Explorer as Container of Subroutines and Functions

  • Entering and Executing VBA Routine

Create and Run Subroutine that Created

  • Understanding with Variables

Know what is Variable

  • Understanding with Data Types

Know Available DATA Type in VBA

  • Dimensioning a Variable

Write Code to Declare Variable

  • Using Variable

Use Variable to Capture data

  • Advantages of Variable

Know the Advantages of Declaring Variable During Coding Time

  • Working with Object Variable

Declare Variable of Excel Objects

  • Using Optional Variable and Variants

Declare Variable of Variant Type

  • Forcing Variable Declaration

Set Option Explicit to Force must Declare Variable

  • Calling Routines

Call Other Subroutines form a Subroutine

  • Passing Data

Pass Data to Other Subroutines for Manipulation

  • Displaying Message Using MsgBox

Show Message Box on Screen During Run time to Display Information

  • Entering Data Using InputBox

Gather User Input During Run Time

  • Understanding the Scope of Variable, Procedure, Module and Project

Knowing Variable, Procedure, Module and Project

  • Branching and Looping

Write the VBA Syntax to Control the flow of Execution of Subroutine

If – Then – Else – Elself

For – Next

Do – Loop

Select Case

For – Each – Next


Designing And Creating Worksheet Based Forms

The User Interface is one of the most Important Aspects of a Custom Application; it Governs how the user Interact with the Application. This Module Cover Numerous Objects that can be used to Design Custom Interfaces in Excel.


Introduction to Control and Forms

  • Know Controls and Forms in VBA

Placing Controls on a Sheet

  • Able to Create user form and place controls on it

Setting Control Properties

  • Set Control Properties

Writing Event Handling Code

  • Write Code to Handle what to do when an event has happened to the control on the user form

Running Event

  • Able to Write Code to Execute Event that Happened

Understanding the Common Properties and event of Controls

  • Know Common Properties and Event of Controls

Using Checkbox Control

  • Able to Create Checkbox to Get User Response

Using Option Button Control

  • Able to Create Option Button to get User Response

Using Toggle Button Control

  • Able to Create Toggle Button to Run Event

Using Listbox Control

  • Able to Create listBox to Provide Options to User

Using ComboBox Control

  • Able to Create ComboBox to Provide Options to User

Using ScrollBar and SpinButton Controls

  • Able to Create ScrollBar and SpinButton on Userform

Using TextBox Control

  • Able to Create TextBox to get user input and to set Value od Textbox

Using Label, Image and Frame Controls

  • Able to use Label, Image and Frame Controls

Using Userform

  • Write code to use designed userform

Debug Techniques

Debugging is the most Important and Probably the Least Understood Aspect of Programming. No one Writes Perfect Code on the first Try. Being able to efficiently locate and correct the mistakes you’ve made is a significant part of what Separates a great programmer from a skilled amateur.


Identifying Code Errors and Program Bugs

  • Able to Identify and Correct Program Bugs

Basic Debugging Techniques

  • Able to use Message Box to Help to Check Code Outcome
  • Able to use Immediate Window
  • Able to use Locals Window
  • Able to use Watch Window
  • Able to use debug.Print Command

Establishing Breakpoints and Stepping Through Code

  • Able to use Breakpoints to Run the Code Line by Line to Locate Code Errors

Case Study

This Module integrates all the relevant knowledge and builds some spreadsheets with VBA as well as customizes to manipulate data retrieved from client’s SRT system.


Project :

  • Calculate Statistic
  • Event Programming
  • Basic User Form
  • Modify Current Code to cater new scenarios

Pauline Leong Pei Loo

Pauline Leong Pei Loo

Being in the information technology background for so many years, she is an expert on Microsoft Applications, Publisher (Version 2000, XP, 2003 & 2007, VBA, Introduction To Internet, Multimedia Fundamentals, IT Skills For Non-PC Users, Basic IT skills and related programs. In her previous company, she was responsible for developing training courseware and customizing training curriculum.Pauline is a reliable person, helpful, independent and friendly person with a pleasant personality. Her classes are lively as she enjoys interacting with people and delivering her knowledge to individuals. Read More

Izham Fariz Ahmad Jinan

Izham Fariz Ahmad Jinan

Izham Fariz experience in IT includes creating systems using Visual Basic and Power Builder as front-end and MS-SQL 2000 and MS Access as database. Other programming languages are C++ and Cobol. He is also exposed to Rational Rose and Microsoft Transaction Server. Read More

Course Reviews

No Remarks


0 Ratings