Microsoft Excel 2003 Programming using VBA

	This 2 day Excel VBA programming course introduces advanced Excel users to the process of writing and editing Macros in Excel 2003. No prior programming experience is required.
	Duration: 2.0 day(s) Price: £416.00 + VAT	Download Course Content   Download Course Files

Locations

Infero Ltd is based in the Lace Market in Nottingham. Training courses take place at the following locations including Nottingham, Derby, Loughborough, Mansfield, Chesterfield, Burton-on-Trent, Lincoln, Leicester, Sheffield, Stoke-on-Trent, Tamworth, Wolverhampton, Walsall, Birmingham, Solihul, Coventry, Nuneaton, Peterborough, Grantham.

Target Students

This course will appeal to students looking to gain the skills necessary to apply VBA to develop macros, format worksheets, create user-interactive macros, work with multiple worksheets, and perform calculations. It may also appeal to students who already have knowledge of the basics of Excel, including how to create, edit, format, and print worksheets that include charts and sorted and filtered data

Prerequisites

Proficient knowledge of Microsoft Excel including knowledge of:

• Recording macros
• Viewing a macro in the Editor window
• Applying conditional formatting
• Linking multiple workbooks and worksheets
• Creating charts
• Sorting and filtering data
• Formatting worksheet and workbooks
• Creating advanced formulas, e.g. IF and VLookup

Knowledge of PivotTables is helpful but not required.

Delivery Method

Instructor led, group-paced, classroom-delivery learning model with structured hands-on activities.

Certification

This course is one of a series of courses that addresses Microsoft Certified Application Specialist (Microsoft Business Certification) skill sets. The Microsoft Certified Application Specialist program is for individuals who use Microsoft's business desktop software and who seek recognition for their expertise with specific Microsoft products. Certification candidates must pass one or more proficiency exams in order to earn Microsoft Certified Application Specialist certification.

Course Objectives

Upon completion of the course, you will have gained the skills necessary to apply VBA to develop macros, format worksheets, create user-interactive macros, work with multiple worksheets, and perform calculations.

Course Content

Lesson 1: Getting Started

Introducing Visual Basic for Applications.
Recording a Macro.
Running a Macro.
Editing a Macro in the Visual Basic Editor.
Understanding the Development Environment.
Using Visual Basic Help.
Closing the Visual Basic Editor.

Lesson 2: Working with Procedures and Functions

Understanding Modules.
Creating a Standard Module.
Understanding Procedures.
Creating a Sub Procedure.
Calling Procedures.
Using the Immediate Window to Call Procedures.
Creating a Function Procedure.
Naming Procedures.
Working with the Code Editor.

Lesson 3: Understanding Objects

Understanding Objects.
Navigating the Excel Object Hierarchy.
Understanding Collections.
Using the Object Browser.
Working with Properties.
Using the With Statement.
Working with Methods.
Creating an Event Procedure.

Lesson 4: Using Expressions, Variables, and Intrinsic Functions

Understanding Expressions and Statements.
Declaring Variables.
Understanding Data Types.
Working with Variable Scope.
Using Intrinsic Functions.
Understanding Constants.
Using Intrinsic Constants.
Using Message Boxes.
Using Input Boxes.
Declaring and Using Object Variables.

Lesson 5: Controlling Program Execution

Understanding Control-of-Flow Structures.
Working with Boolean Expressions.
Using the If...End If Decision Structures.
Using the Select Case...End Select Structure.
Using the Do...Loop Structure.
Using the For...To...Next Structure.
Using the For Each...Next Structure.
Guidelines for use of Control-of-Flow Structures.

Lesson 6: Working with Forms and Controls

Understanding UserForms.
Using the Toolbox.
Working with UserForm Properties, Events, and Methods.
Understanding Controls.
Setting Control Properties in the Properties Window.
Working with the Label Control.
Working with the Text Box Control.
Working with the Command Button Control.
Working with the Combo Box Control.
Working with the Frame Control.
Working with Option Button Controls.
Working with Control Appearance.
Setting the Tab Order.
Populating a Control.
Adding Code to Controls.
Launching a Form in Code.

Lesson 7: Working with the PivotTable Object

Understanding PivotTables.
Creating a PivotTable.
Working with the PivotTableWizard Method.
Working with PivotFields.
Assigning a Procedure to a Custom Toolbar.

Lesson 8: Debugging Code

Understanding Errors.
Using Debugging Tools.
Setting Breakpoints.
Stepping through Code.
Using Break Mode during Run mode.
Determining the Value of Expressions.

Lesson 9: Handling Errors

Understanding Error Handling.
Understanding VBA's Error Trapping Options.
Trapping Errors with the On Error Statement.
Understanding the Err Object.
Writing an Error-Handling Routine.
Working with Inline Error Handling.