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

  • Public Class Icon
    Public Class
    • HRDF SBL Claimable
    • Lunch & refreshment provided
    • Certificate of Attendance available
    Starting From
    RM 1800.00
    3 Days
  • Private Class Icon
    Private Class
    • All of our private classes are customized to your organization's needs.

      Click on the button below to send us your details and you will be contacted shortly.
    3 Days

Course Details

Expand All

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

Modules

Expand All

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

Lesson

  • 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)


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.

Lesson

  • 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

With


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.

Lesson

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


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.

Lesson

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


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.

Lesson

Project :

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


Trainers

See All
Reset

Reviews

4.2
based on 120 ratings reviews