Microsoft Excel 2010 VBA

Course code: MSEXC2010VBA
Duration: 2 days
Overview
This two day course leads you through Excel Visual Basic for Applications (VBA) from recording macros to advanced form design. As you progress you will learn how to write object-oriented programs, how to create program control structures and handle errors effectively.
Prerequisites
This course is suitable for advanced users of Microsoft Excel. They may be responsible for large amounts of data handling which needs automating or they may be part of a team and need to learn how to program features and functions to improve data access and usability.
A thorough knowledge of Excel is required. We recommend completion of Microsoft Office Excel 2010 Level 3 Advanced or equivalent knowledge.
Delegates will learn how to:
You will learn how to harness the power of the Visual Basic language to control and automate Excel worksheets and applications.
COURSE CONTENT
Getting started with Macros
Recording and Modifying Macros
Assigning Macros to Toolbar, Buttons, Shortcut key
Visual Basic for Applications Environment
Visual Basic Editor and its options
Component Management
Shortcuts
Introduction to Procedural Programming
Procedures: Functions and Sub-routines
Data Types
Excel Model Object
Application Object
Workbook Object and Workbooks Collection
Worksheet Object and Worksheets Collection
ThisWorkbook, ActiveWorkbook, ActiveCell, ....
Selection Object
Range Collection
Cells Collection
Using Status Bar
Screen Updating and Optimizing
Timer and Time Measuring
Events Handling in VBA
Control Flow
If ...Then....Elseif....Else....Endif
Case
Do...Loop , While
For... Next
Exit
VBA and Sheet Data Exchange (Cells, Range Methods)
Getting and Printing Data from Users (InputBox, MsgBox)
Variable Declaration
Scope and LifeTime of Variables and Objects
Operators and Their Priorities
Modules Options
Creating Worksheet Functions
Objects, Classes, Methods and Properties
Locking Project for Viewing
Procedures, parameters, variables
Passing parameters by reference and by value (ByRef or ByVal)
Optional and default parameters
Passing an array of arguments to a procedure
Debugging
Run Time, Design Time, and Break Mode
Breaks and Watches
Local Window
Immediate Window
Error Handling
Errors Types
On Error GoTo
On Error Resume Next
VBA Forms
Creating and Using Forms
Invoking Forms in VBA Code
Using VBA Forms in Worksheet
Arrays
Dynamic arrays
Arrays optimization
Multidimensional arrays
Arrays and objects
 
Creating and using collections
Adding and remove elements
Using index and key
Dealing with files
Reading text files
Using CSV files