Please enable JavaScript
VBA for Excel 2016 – Eagle Infotech Consultants
Eagle Infotech Consultants
150 Orchard Road #06-14
Orchard Plaza Singapore 238841

VBA for Excel 2016

Visual Basic Application for Excel 2016
[Duration of Course] – 16 hrs

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