Course Outline
2-Day Excel training course designed for experienced users
This 2-day course will teach you how to create visual basic macros, understand the basics of object orientated programming and manipulate code to suit your needs.
Target Audience
This course is valuable for experienced Excel users who had to manipulate large data sets or manage regular reports from inconsistent data
Objectives
This course is about harnessing the power of Microsoft Excel Visual Basic to learn the programming language behind the scenes. You will gain the knowledge and hands-on skills required to exploit Excel's macros, and build your own custom code modules to manipulate and manage your data.
Pre-Requisites
A good understanding of Excel functions and formulas
Topics Covered
Visual Basic and Macros
- Recording a macro
- Simple formatting; Cells, Columns
- The VB editor, the Property and Project window
- View and understand code structure
- Concepts of object oriented programming
- Object properties methods and actions
The VBA World
- Modules tab
- Subs and Functions
- Option Explicit and other Tools / Options
- Colour coded editor
- Split window
- F2, dropdown declarations, Ctrl+F
- Object Browser
- Shift+F2 (Ctrl+Shift+F2)
Subs vs Functions
- Syntax; Sub, Public and Private
- Procedure Names
- Auto Syntax Check
Running Functions
- Debug Window calling a function
- Query’s Conditions
- Form Event
Forms
- Creating a form, using Form Objects
- Naming Conventions
Programming VBA
- Comments
- Line continuation character
- Variables; search VBA Help Index for Long and go to Data Type Summary
- Option Explicit
- Variables and Variants
- Other data types
- Variable Scope
Branching and Looping
- Indenting
- If Then Else
- For Next loops
- Do Loops
- Select Case
Debugging
- Immediate Window Ctrl G
- Breakpoints F9
- Step Into F8
- Mouse over variable
Error Handling
- None
- On Error Goto 0
- On Error Resume Next
- On Error Goto xxxx
- Resume or Resume Next
- Error Handlers and Objects
