[NEW 3-Day] Excel VBA Training
Course Information
  • 27-Sep-2021
  • Online
  • Izham Fariz
  • English
  • RM 1980 RM 1580
    Until 31 Aug 2021
  • 9:00am - 5:00pm

[01] INTRODUCTION

Why learn Excel VBA? VBA is excellent for automating actions – saving time by performing programmed actions on repeat or routine basis. With excellent VBA skills, one becomes more productive in work performance while reducing mistakes.  Participants will be exposed to the overall concept and applications of VBA in excel throughout the entire course. The course is 20% lecture and 80% hands-on, which would provide ample opportunity for the participants to immediately apply what they have learned.  In this 3-Day training, participants learn Visual Basic for Applications language as well as the Excel classes and objects needed to create VBA scripts to automate Excel. Participants create procedures and functions using the VBA language and Excel objects.

 

[02] COURSE OBJECTIVES

  • Create recorded macros in Excel
  • Understand the Excel object model and VBA concepts
  • Create command procedures
  • Create and work with UDFs (user-defined functions)
  • Use a range of common programming techniques
  • Code to drive a user form
  • Write a variety of error handling routines
  • Use the macro recorder to create a variety of macros
  • Work with the three main components of the VBA Editor window
  • Create and use variables
  • Write code to manipulate Excel objects
  • Create a custom form complete with controls and event procedures
  • Create procedures that start automatically

 

[03] WHO SHOULD ATTEND

• For Managers across all business units, and Regular Excel users who want to automate their tasks and with minimal VBA programming

• Accounts Supervisor/ Assistant/ Clerk
• Office Administrators/ Admin Assistant
• Operation Manager/ Project Manager/ Line-of-Business Manager

• PA/ Secretaries

 

[04] METHODOLOGY

• Interactive lecture and discussion.

• Hands-on exercises

 

[05] PREREQUISITE

Participants must have prior knowledge of Microsoft Excel

 

[06] DATE, TIME & FEE

Date: 27 - 29 September 2021 (Mon - Wed)

Time: 9.00am - 5.00pm

Mode: Online/ Interactive

[HRDF Claimable - SBL KHAS]

 

Fee:

- Early Bird Offer (before 31 Aug 2021) - RM 1,580

- Special Offer (before 14 Sep 2021) - RM 1,780

- Normal Fee - RM 1,980

 

>> Fee includes Course Notes, Certificate of Completion and 6% Service Tax.

** OFFER ** 

- 10% Cashback for SST2u Learner Card+ (SLC+) members. T&Cs applied.

 

[07] COURSE OUTLINE

 

DAY 1

 

1. Overview

 

2. Recorded Macros

• Understanding Excel Macros
• Setting Macro Security
• Saving a Document as Macro Enabled
• Recording a Simple Macro
• Running a Recorded Macro
• Relative Cell References
• Running Macro with Relative References
• Viewing a Macro
• Editing a Macro
• Assigning a Macro to the Toolbar
• Running a Macro from the Toolbar
• Assigning a Keyboard Shortcut to a Macro

• Deleting a Macro
• Copying a Macro

 

3. Recorder Workshop

• Preparing Data for an Application

• Recording a Summation Macro
• Recording Consolidations
• Recording Divisional Macros

• Testing Macros
• Creating Objects to Run Macros

• Assigning a Macro to an Object

 

4. Understanding VBA

• Programming in Microsoft Excel
• VBA Terminology
• Understanding Objects
• Viewing the Excel 2010 Object Model

• Using the Immediate Window

• Working with Object Collections

• Setting Property Values
• Working with Worksheets
• Using the Object Browser

• Programming with the Object Browser

• The Best VBA Help Available

 

5. The VBA Editor

• The VBA Editor Screen
• Opening and Closing the Editor
• Using the Project Explorer
• Working with the Properties Window

• Using the Work Area
• Viewing Other Panes
• Working with Toolbars
• Working with a Code Module
• Running code from the Editor
• Setting Breakpoints in the Editor
• Stepping Through Code

 

DAY 2

 

6. Procedures

• Understanding Procedures
• Where Procedures Live
• Creating a New Sub Routine

• Making Sense of IntelliSense

• Using the Edit Toolbar

• Commenting statements
• Indenting Code
• Bookmarking in Procedures

 

7. Using Variables

• Understanding Variables
• Creating and Using Variables
• Implicit and Explicit Declarations

• The Scope of Variables
• Procedure Level Scoping
• Module Level Scoping
• Passing Variables by Reference
• Passing Variables by Value
• Data Types
• Declaring Data types
• Using Arrays

 

8. Functions In VBA

• Understanding Functions
• Creating VBA Functions
• Using a VBA Function in a Worksheet

• Setting Function Data Types
• Using Multiple Arguments
• Modifying a VBA Function
• Creating a Function Library
• Referencing a Function Library
• Importing a VBA Module
• Using a Function In VBA Code

 

9. Using Excel Objects

• The Application Object
• The Workbook Objects
• Program Testing with The Editor

• Using Workbook Objects
• The Worksheets Object
• Using the Worksheets Object
• The Range Object
• Using Range Objects
• Using Objects in a Procedure

 

10. Programming Techniques

• The MsgBox Function
• Using MsgBox
• InputBox Techniques
• Using the InputBox Function

• Using the InputBox Method

• The IF Statement

• Using IF for Single Conditions
• Using IF for multiple Conditions
• The Select Case Statement
• Using The Select Case Statement

• For Loops
• Looping With specified Iterations

• The Do...Loop Statement
• Looping With Unknown Iterations

 

DAY 3

 

11. Creating Custom Forms

• About Custom Forms
• Creating a Custom Form
• Adding Text Boxes to a Form
• Changing Text Box Control Properties

• Adding Label Controls to a Form
• Adding a Combo Box Control
• Adding Option Buttons
• Adding Command Buttons
• Running a Custom Form

 

12. Programming UserForms

• Handling Form Events
• Initializing a Form
• Closing a Form
• Transferring Data From a Form

• Running Form Procedures

• Creating Error Checking Procedures

• Running a Form From a Procedure
• Running a Form from the Toolbar

 

13. Automatic Startup

• Programming Automatic Procedures

• Running Automatic Procedures
• Automatically Starting a Workbook

 

14. Error Handling

• Error types
• The On Error statement
• Creating a Simple Error Handler
• Using the Resume Statement
• Using Decision Structures in Error Handlers

• Using the Err object
• Error Handling in Event Procedures
• Defining Custom Errors

 

15. Conclusion

 

[08] TRAINER PROFILE

IZHAM FARIZ

MCT, Microsoft Office Specialist Expert - 2020, HRDF Certified Trainer

 

Izham has closed to 20 years in conducting trainings both in public university and the private sector. He has helped thousands of office workers all over cope with data and document volumes in digital form, so they can become more efficient and come out being smart office workers.

Izham has a Bachelor Degree in Science (IT) and a series of Microsoft Office professional qualifications. He specialized in conducting training programs with practical applications,. including database projects, automation of Human Resource, Operation and Finance functions, using automation and programmed solutions in Excel. They include running simulations, creating data extraction tools as well as dashboards to simplify business operations.

 

[08] ENQUIRY & REGISTRATION

Enquiry: SSTcourse@gmail.com

Contact: - General (03 - 5888 7357)

- Teo (011 - 3178 9203)

- Logesh (012 - 503 0346)

- Siti (012 - 383 8603)

- Han (019 - 323 0507)