Microsoft Excel Level 4

Microsoft Excel Level 4 (VBA) – Course Outline

​

About This Course

This course is designed for delegates who want to automate common worksheet tasks, convert worksheets into forms and understand Microsoft Excel VBA code.

 

Target Audience

Thorough knowledge of Excel is required, gained either by previous experience or by attendance on a Microsoft Excel Level 3 course.

 

At Course Completion

After completing this course, students will be able to:

 

• Understand what a macro does

• Assign macros to buttons and sheets

• Edit a macro

• Work with the range object

• Work with workbooks and sheets

• Understand Objects, Properties and Methods

• Use control structures

• Customise the toolbar and display

• Understand debugging and error trapping

• Work with worksheet and workbook events

• Work with procedures

• Understand macro naming conventions

 

Course Content

 

Unit 1 - Introduction

• What is a macro?

• Recording a macro

• Running a macro

• Relative references

 

Unit 2 - Assigning Macros

• To a button on a sheet

• To a drawn object on a sheet

• To a button on a toolbar

 

Unit 3 - Editing Macros

• The VB editor and the project explorer

• The code window

• Procedures

• Inserting comments

• Printing a VB module

 

Unit 4 - The Range Object

• The cells property and range property

• Flexible ranges

• Range contents

 

Unit 5 - Workbooks and Sheets

• Activating a workbook and worksheet

• This workbook

 

Unit 6 - Objects, Properties and Methods

• Controlling objects with properties

• Performing actions with methods

• Performing multiple actions on an object

 

Unit 7 - Variables and Constants

• Variables and data types

• Constants

 

Unit 8 - Control Structures

• Conditional control structures

• Looping control structures

• Non-structured statements

• Sub procedures

 

Unit 9 - Controls, Dialogue Boxes and Forms

• Built-in dialogue boxes

• Predefined dialogue boxes

• Custom forms and worksheet forms

• User-defined forms

• Adding form controls

• Form control properties

• Control and dialogue box events

• Displaying and dismissing a user form

• Handling form controls

 

Unit 10 - Toolbars and Display

• About toolbars and menus

• Customised toolbars

• Display

 

Unit 11 - Debugging and Error Trapping

• Stepping

• Error handling

 

Unit 12 - Worksheet and Workbook Events

• Worksheet object events

• Workbook object events

 

Unit 13 - Function Procedures

• User-defined functions

 

Unit 14 - Appendix – Naming Conventions

• User form controls

 

 

Course duration: one day