Visual Basic for Applications (VBA) - Excel

Visual Basic for Applications (VBA) - Excel

Audience

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

Prerequisites

Good working knowledge of Excel.

No previous programming experience is required.

Duration

3 days. Hands on.

Course Objectives

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.

Course Content

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.

Virtual Courses

ALL of our courses can be delivered virtually. And our Bath public schedule of courses are now available as live virtual sessions, using the popular Zoom Virtual Classroom and remote labs. Delegates can test their access at: www.zoom.us/test

Public Courses

On-Site Courses

Can't attend one of our public classes? Booking for multiple people?

All our courses are available on your site! Delivered for your staff, at your premises.

Contact us to find out more...