Excel VBA

Course Length: Two or Three Days
Fees: Training at Client Offices: One delegate: £495 per-day + VAT
Additional delegates attending: £100 each per-day + VAT

Manuals available @ £35 each (optional)

Two-Day Course

This is a hands-on course, intended for advanced users of Excel, to enable them to automate complex or repetitive spreadsheet procedures. It will provide an in-depth introduction to the Visual Basic programming language (Applications Edition)

This course is designed for Excel developers how have already attended an advanced Excel course or who have attained similar knowledge.

As a result of this course, delegates should expect to be able to:

	Automate complex or repetitive procedures by recording macros
	Read, edit and debug Visual Basic code
	Understand Objects/ Collections, Properties and Methods
	Create user-interactive macros
	Use and understand VBA Control-Structures
	Build new worksheet functions
	Create custom Dialog Boxes/ Forms

Main Topics Covered

	Recorded Macros: avoiding the common problems, understanding the limitations.
	How to run a Macro: customising the toolbars, assigning shortcut keys.
	Finding and reading the code: modules, comments, keywords, and statements.
	Understanding the Object model: the rules of grammar and syntax in an object oriented programming language, how to apply these, the Object-Browser.
	Interacting with the user: Input Boxes and Message Boxes.
	Looping: the For-Next loop, the Do-Loop, nested loops.
	Variables: using and declaring variables to store data.
	Making decisions; the If statement, the Block-If statement, Else-If, Select-Case.
	Custom Functions: creating and using new functions on worksheets and in VBA code.
	Debugging: how to respond to an error, the debug window, stepping through your Macro, the Watch window, the Immediate Pane.
	Dialog Boxes/Forms: designing and building your own, adding controls, showing, controlling and interacting with your user built dialog boxes.
	Event Driven Procedures: triggering macros automatically with workbook or form events.
	Course Review (Time permitting): Question/answer session

Three-Day Course

This is a hands-on course, intended for advanced users of excel, to enable them to automate complex or repetitive spreadsheet procedures. It will provide an in-depth introduction to the Visual Basic programming language (Applications Edition)

This course is designed for Excel developers how have already attended an advanced Excel course or who have attained similar knowledge.

As a result of this course, delegates should expect to be able to:

	Automate complex or repetitive procedures by recording macros
	Read, edit and debug Visual Basic code
	Understand Objects/Collections, Properties and Methods
	Create user-interactive macros
	Build new worksheet functions
	Create custom Dialog Boxes/Forms

Main Topics Covered

	Recorded Macros: avoiding the common problems, understanding the limitations.
	How to run a Macro: customising the toolbars, assigning shortcut keys.
	Finding and reading the code: modules, comments, keywords, and statements.
	Understanding the Object model: the rules of grammar and syntax in an object oriented programming language, how to apply these, the Object-Browser.
	Interacting with the user: Input Boxes and Message Boxes.
	Looping: the For-Next loop, the Do-Loop, the For-Each-Loop, nested loops.
	Variables: using and declaring, types and levels of variable.
	Calling Functions/procedures: setting up references to modules in other books, using Functions within Macros.
	Making decisions; the If statement, the Block-If statement, Else-If, Select-Case.
	Custom Functions: creating and using new functions on worksheets and in VBA code.
	Debugging: how to respond to an error, the debug window, stepping through your Macro, the Watch window, the Immediate Pane.
	Dialog Boxes/Forms: designing and building your own, adding controls, showing, controlling and interacting with your user built dialog boxes.
	Event Driven Procedures: triggering macros automatically with workbook or form events.
	Course Review (Time permitting): Question/answer session

Introduction to Visual Basic Programming

	What is Visual Basic?
	Terminology
	Steps to create an application
	Modular environment

Building an application

	Setting properties of objects
	Forms
	Introduction to controls
	Object-based, event-driven programming

Coding

	The Code Editor
	Using the Object Browser to view properties and methods of objects
	Statements and functions
	Conditional statements
	Looping statements
	Native code

Debugging

Overview

Forms

	Showing and hiding forms
	Order of events
	Multiple forms
	Start-up and end of applications

Variables

	Data types
	Scope and lifetime of variables
	Constants
	Arrays and user-defined types

Procedures

	Introduction to procedures
	Arguments and parameters
	Named arguments and optional arguments

Controls

	Control formatting
	Standard controls
	Custom controls

Menus

	Creating and coding menus
	Custom pop-up menus

Error handling

An introduction to error handlers

Wizards

	The Package and Deployment Wizard
	Application Wizard

The next step

	Data access using the Data Form Wizard and the data control
	MDI forms
	Classes
	An introduction to ActiveX controls within Visual Basic

If you have other questions, please contact us at enquiry@it-uk.com or complete our response form