VBA Excel 2013/2016


Duration : 3 Days (24hrs) / 9.00am-6.00pm

Course Summary

This 3-day course guides participants in using some of Excel’s commonly used formulas. Participant also learns how to simplify your work in the Excel environment by automating many of the repetitive tasks that are part of database development. In this course, participant will apply the Visual Basic for Applications (VBA) programming language to simplify many of the tasks in Excel 2010/2013/2016 environment.

What you will learn

Participants looking to gain the skills necessary to apply VBA to develop macros, format worksheets, create user-interactive macros, work with multiple worksheets and perform calculations.

Participants will be proficient in using VBA to create macros for automating repetitive tasks in Excel 2010/2013/2016.

Course Information

Course Objective

At course completion, the participant will be able to:

  • Record and edit macros
  • Use the basic visual editor
  • Create sub and function procedures
  • Understand objects, properties, methods and events
  • Explore the Excel object hierarchy and use the Object Browseer
  • Work with variables and understand data types
  • Use intrinsic functions
  • Work with control-of-flow structures
  • Design UserForms and work with controls
  • Use debugging tools
  • Add error handling to code


  • Download Course Brochure

    Course Fees & SkillsFuture Funding


    Government Funding
    Course is eligble for SDF Training Grant & Absentee Payroll from SSG. 
    Self-Sponsored trainee may offset course fee payable using their SkillsFuture Credit Claim or/& PSEA (subject to available balance in trainee's account). 
    Terms and conditions apply. 

    SDF Course Title : VBA Excel (SF)
    Course Ref No. : CRS-Q-0032182-ICT

    WSQ Funding Course Fees
    WSQ Funding
    Singapore Citizens & Singapore PRs
    Total Fee
    Incl Regn Fee
    Less
    SDF Training
    Grant
    NETT
    Fee
    Excl GST 7%
    NETT
    Fee
    Incl GST 7%
    SME Company S$680.00S$585.00S$95.00S$142.60
    Non-SME CompanyS$680.00S$325.00S$355.00S$402.60
    Self-SponsoredS$680.00S$325.00S$355.00S$402.60*
     
    Mid-Career Enhanced Scheme (MCES)
    - Singaporean, 40 yrs & above
    S$680.00S$585.00S$95.00S$142.60*
     
    Note: * - SkillsFuture Credit Claims or/and PSEA may be used to offset the course fees.
    Course Fees (Applicable to Non-SDF/Foreigner)
    Course Title Normal Course Fees Promotional Course
    Fees valid till
    31-Jul-2021
    Promo Course Fees w GST
    NICF - VBA ExcelS$799.00S$650.00S$695.50
    Registration FeeS$30.00S$30.00S$32.10
    Total FeeS$829.00S$680.00S$727.60

    (Contact Customer Service @ 6333 4843)

    Course Prerequisite

    Prior knowledge in Excel 2003/2007/2010/2013 and especially suitable for participants who have attended Excel 2003/2007/2010/2013 Intermediate and Advance course


    Course Outline

    1.0 Getting Started with VBA Excel

  • Introducing Visual Basic for Applications
  • Displaying the Developer Tab on the Ribbon
  • Recording a Macro
  • Saving a Macro Enabled Workbook
  • Running a Macro
  • Editing a Macro in the Visual Basic Editor
  • Understanding the Development Environment
  • Using Visual Basic Help
  • Closing Visual Basic Editor
  • Understanding Macro Security

  • Download Course Brochure

    2.0 Working with Procedures and Functions

  • Understanding Modules
  • Creating a Standard Module
  • Understanding Procedures
  • Creating a Sub Procedure
  • Calling Procedures
  • Using the immediate Window to call Procedures
  • Creating a Function Procedures
  • Naming Procedures
  • Working with the Code Editor
  • 3.0 Understanding Objects

  • Understanding Objects
  • Navigating the Excel Object Hierarchy
  • Understanding Collections
  • Using the Object Browser
  • Working with Properties
  • Using the With Statement
  • Working with Methods
  • Creating an Event Procedure
  • 4.0 Using Expressions, Variables and Intrinsic functions

  • Understanding Expressions and statements
  • Declaring Variables
  • Understanding Data types
  • Working with Variable Scope
  • Using Intrinsic Functions
  • Understanding Constants
  • Using intrinsic Constants
  • Using Message Boxes
  • Using Input Boxes
  • Declaring and Using Object Variables
  • 5.0 Controlling Program Execution

  • Understanding Control-of-Flow Structures
  • Working with Boolean Expressions
  • Using the If..End If Decision Structures
  • Using the Select Case ..End Select Structure
  • Using the Do..Loop Structure
  • Using the For..To..Next Structure
  • Using the For Each ..Next Structure
  • Guidelines for use of Control-of-Flow Structures
  • 6.0 Working with Forms and Controls

  • Understanding UserForms
  • Using the Toolbox
  • Working with the UserForm Properties, Events, and Methods
  • Understanding Controls
  • Setting Control Properties in the Properties Window
  • Working with the Label Control
  • Working with the TextBox Control
  • Working with the Command Button Control
  • Working with the Combo Box Control
  • Working with the Frame Control
  • Working with Option Button Controls
  • Working with Control Appearance
  • Setting the Tab Order
  • Populating a Control
  • Adding Code to Controls
  • Launching a Form in Code
  • 7.0 Debugging Code

  • Understanding Errors
  • Using Debugging Tools
  • Setting Breakpoints
  • Stepping through Code
  • Using Break Mode during Run mode
  • Determining the value of Expressions
  • 8.0 Handling Errors

  • Understanding Error Handling
  • Understanding VBA’s Error Trapping Options
  • Trapping Errors with the On Error statements
  • Understanding the Err Object
  • Writing an Error-Handling Routine
  • Working with Inline Error Handling
  • 9.0 VBA Macros Security and Protection in Excel

  • Password Protecting the code
  • Hiding and very hiding Worksheets
  • Protecting the Sheet
  • Protecting the Workbook
  • Password Protecting a single VBA Procedure

  • Course Schedule

    Course Title   Venue Register Online
    Course Mode
    Start Date
    End Date
    Time
    NICF - VBA ExcelSingaporeRegisterFULL-TIME17-Nov-202119-Nov-20219:00-6:00PM 
    NICF - VBA ExcelSingaporeRegisterFULL-TIME8-Dec-202110-Dec-20219:00-6:00PM 
    NICF - VBA ExcelSingaporeRegisterFULL-TIME19-Jan-202221-Jan-20229:00-6:00PM 
    NICF - VBA ExcelSingaporeRegisterFULL-TIME21-Feb-202223-Feb-20229:00-6:00PM 
    NICF - VBA ExcelSingaporeRegisterFULL-TIME16-Mar-202218-Mar-20229:00-6:00PM 
    NICF - VBA ExcelSingaporeRegisterFULL-TIME6-Apr-20228-Apr-20229:00-6:00PM 
    NICF - VBA ExcelSingaporeRegisterFULL-TIME9-May-202211-May-20229:00-6:00PM 
    NICF - VBA ExcelSingaporeRegisterFULL-TIME22-Jun-202224-Jun-20229:00-6:00PM 
    NICF - VBA ExcelSingaporeRegisterFULL-TIME4-Jul-20226-Jul-20229:00-6:00PM 
    NICF - VBA ExcelSingaporeRegisterFULL-TIME3-Aug-20225-Aug-20229:00-6:00PM 
    NICF - VBA ExcelSingaporeRegisterFULL-TIME21-Sep-202223-Sep-20229:00-6:00PM 
    NICF - VBA ExcelSingaporeRegisterFULL-TIME3-Oct-20225-Oct-20229:00-6:00PM 
    NICF - VBA ExcelSingaporeRegisterFULL-TIME2-Nov-20224-Nov-20229:00-6:00PM 
    NICF - VBA ExcelSingaporeRegisterFULL-TIME12-Dec-202214-Dec-20229:00-6:00PM 
    NICF - VBA ExcelSingaporeRegisterSAT12-Feb-202226-Feb-20229:00-6:00PM 
    NICF - VBA ExcelSingaporeRegisterSAT7-May-202221-May-20229:00-6:00PM 
    NICF - VBA ExcelSingaporeRegisterSAT6-Aug-202220-Aug-20229:00-6:00PM 
    NICF - VBA ExcelSingaporeRegisterSAT5-Nov-202219-Nov-20229:00-6:00PM