Microsoft Excel: Visual Basic for Applications (Excel)

Course Overview

This course is designed for those who have knowledge of the workings of Excel and who wish to extend its abilities to automate frequently used tasks.

CourseMonster tracks Microsoft training and can notify you when the next Microsoft Excel course is available in your area. Contact us for more details.

Related Courses

• Microsoft Excel: Introduction for Beginners (1 day)
• Microsoft Excel: Data Analysis Decision Making/Solve Business Problems (1 day)
• Microsoft Excel: Visual Basic For Applications (VBA) Programming (2 day)
• Microsoft Excel: Formulae & Functions (1 day)
• Microsoft Excel: Data Analysis Decision Making/Solve Business Problems (2 day)
• Microsoft Excel: Visual Basic for Applications (VBA) Programming (4 day)
• Microsoft Excel: Charts, Pivot Tables & Macros (1 day)
• Microsoft Excel: Financial Modelling (2 day)
• Microsoft Excel: Business Modelling for Telecoms (2 day)
• Microsoft Office: Word, Excel & PowerPoint: Introduction for Beginners (1 day)

Skills Gained

On completion of the course, delegates will understand:

• How to create and edit code procedures and Functions.
• How to use Events.
• How to provide and control user access.
• How to create and protect applications.
• How to use Excel objects to achieve results.
• How to create and use Custom Forms and dialogue boxes.
• How to access and manipulate external data.

Prerequisites

Good working knowledge of Excel. No previous programming experience is required.

Course Outline

Visual Basic for Applications

• Introduction
• Definitions
• Objectives

Programming in Excel

• Procedures
• Relative and Absolute References
• Availability of Procedures
• Personal Macro Workbook

Editing Recorded Procedures

• Viewing Code
• The Visual Basic Editor
• Reading Code
• Using Visual Basic Procedures
• Interactive Procedures
• Control Structures
• Using Help
• Customising Visual Basic

User Defined Functions

• Creating and using User Defined Functions

Visual Basic Procedures

• Using Procedures Together
• Calling Procedures
• Optional Arguments
• Passing Arguments
• Project Organisation
• Protecting Procedures
• Protecting Modules

Visual Basic Objects

• Using Objects
• Referencing and Using Properties
• Common Properties
• Using Methods
• Collections
• Object Browser

Visual Basic Code

• Declaring Variables and Arguments
• Data Types
• Function Data Types
• Assigning Objects to Variables
• Scope and Life of Variables
• Arrays

Control Structures

• Conditional Structures
• Repetition
• Nesting structures

Testing and Debugging

• Debugging Tools
• Calls Stack
• Watch Expressions
• Immediate Pane

Error Handling

• Error Handler Design
• Error-Handling Hierarchy
• Testing Error Handling

Object Models

• Object Model Overview
• Using Objects to Automate Tasks

Application Objects

• Properties
• Methods
• Events

Data Access Objects

• DAO Objects
• Accessing ODBC Data
• DAO with ODBCDirect
• Using ODBCDirect

ActiveX Controls and Dialogue Boxes

• Custom Dialogue Boxes
• Controls

Optimising for Size and Speed

• Optimisation Strategies

User Access

• Menus
• Toolbars
• Command Buttons

Configuration Settings

• Writing to the Registry
• Reading from the Registry