2 days – Visual Basic Application for Excel 2016

COURSE synopsis

Course Details

Course Duration

15 HRS

Course Reference Number

TGS-2018500205

Funding Validity Period

02 May 2020 To
31 Dec 2024

Mode of Training

Classroom

Successful learners of Visual Basic Application (VBA) for Excel 2016 Course are able to use Visual Basic functions like Macros to convert tedious manual tasks into one click of a button to increase spreadsheet productivity, and to use Userforms for user interactive interfaces.

Target Audience

Those in Finance, Sales department and all who need to automate task to increase productivity.

Pre-Requisite

You need to have a good knowledge of Excel. No previous programming experience required.

Course Outline

Part 1: Macros For Spreadsheet Automation
Understanding Macros
  • Uses of macro
  • Planning the recording and recording a macro Relative and absolute recording options
  • Various methods of executing a macro
  • Storing a macro
Modifying Existing Macros
  • Examining the codes in a macro
  • Adding comments
  • Cleaning up the macro codes
  • Testing modified macros
Part 2: VBA Basics
Visual Basic Editor Environment
  • The VBE Window
  • Using the Project Explorer, Code & Immediate Window
  • Creating a new module
  • Entering VBA codes
  • Customizing VBE environment
Learning About Objects & Property
  • Understanding the Excel Object Model
  • Using the Object Browser
  • Importing and export objects
  • Objects referral
  • Using the Application, Workbooks, Worksheets & Window objects
  • Using the Range, Cells, Offset properties
  • Selecting cells, rows and columns
  • Using the Value and Formula properties
  • Properties for applying cell formatting
Part 3: VBA Programming Novice
Variables, Data Type & Constants
  • Commenting for programming
  • Declaring and Scoping variables
  • Defining data type
  • Working with Date & String type
  • Assignment expressions
  • Using logical operators
  • Arrays
Procedures, Functions And Constructs
  • Creating Procedures and Scoping a procedure
  • Various methods of executing a procedure
  • Passing arguments to procedures
  • Using built-in, MsgBox InputBox functions
  • Constructs for manipulating objects
  • With … End,  For Each … Next
  • Constructs for controlling program execution
  • Understanding decision making in VBA
  • GoTo,  If … Then,  Select Case,  For … Next loops,  Do … While loops,  Do … Until loops
Part 4: VBA Programming Senior
Error Handling
  • Error handling techniques
  • Using Break Mode, Setting Breakpoints and Trapping Error
Using Custom Functions
  • Creating custom functions for use in worksheet and procedures
  • Passing arguments to procedures
  • Adding custom functions to Paste Function dialog box
Part 5: VBA Programming Graduate
Introducing UserForms
  • Incorporating UserForm
  • Deploying and setting UserForm controls
  • Display and close UserForm using VBA
  • Deploying UserForm events
  • Applications of UserForm
Excel Events
  • Understanding event sequences
  • Using event handlers for workbook, worksheet and charts
Course Outline

Download the PDF below for a summary of the course outline.

COURSE FEES


Company Sponsored

Full Fee GST Broad-based Funding
($2 per training hour)
Nett Fee Payable (incl. GST)
$680 $61.20 $30 $711.20

Self-Sponsored

Full Fee GST Nett Fee Payable (incl. GST)
$680 $61.20 $741.20

REGISTER TODAY

Looking at in-house training or have an enquiry? Contact us!

Related Courses

IT

8 HRS

Non-Certifiable

IT

24 HRS

WSQ

IT

24 HRS

WSQ

GET IN TOUCH WITH US

Have an enquiry? Feel free to connect via any of the channels below.

enquiry form

Leave a message

WhatSAPP

Chat on WhatsApp at
+65 8776 7397

Training hotline

Give us a call at
+65 6737 5761

SHARE THIS COURSE