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

VBA for Excel 2010

Visual Basic Applications for Excel 2010

[Duration:  15 hours]
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

View Course Dates