fbpx 2 days - Visual Basic Application for Excel 2016 | Eagle Infotech Consultants Skip to main content

2 days - Visual Basic Application for Excel 2016

Course Duration
15
Course Reference Number
CRS-N-0042855
Funding Validity Period
02 May 2019 To 30 Apr 2020
Mode of Training
Classroom

To make use of Visual Basic functions to convert tedious manual tasks into one click of a button, and to use Userforms for user interactive interfaces.

Who Should Attend

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
Full Fee GST Nett Fee after Funding (Incl. GST)
SME/MCES Non-SME/Normal WTS
$680
$47.60
$727.60
-
-

MCES: Singaporean age 40 and above

Normal: Singaporean/PR age 21 and above

WTS: Singaporean age 35 and above and earning $2,000 or below per month

 

SME: Singapore registered companies with

(i) at least 30% local sharing AND

(ii) group annual sales less than or equal to $100 million OR group employment size less than or equal to 200 people