Microsoft Excel Level 4 - Macros & VBA (All versions)

Microsoft Excel Level 4 - Macros & VBA (All versions)

Please remember a customised outline can also be put together of subjects you wish to cover. Our trainers will always quantify what delegates wish to learn on each course as well and try to include this in the content.

Completion of this course will map delegates towards the Microsoft Excel MOS Exam - Variation between different versions, e.g. 2003 and 2007 will be covered within the relevant course when delivered.

Overview

Delegates learn how to record and develop automated procedures in Excel using the Visual basic for Applications language. It emphasises hands-on experience through use of sample course files and, if appropriate, delegates' own worksheets.

Recording Macros:

• Naming the procedure
• Allocating keyboard shortcuts
• Where to store the recorded code
• Relative vs. absolute recording
• Stopping recording.

Running your Procedure:

• Keyboard shortcut
• From the menu
• Button on worksheet
• Toolbar Buttons
• Customising the menu.

The Visual Basic Editor:

• Identifying key screen components
• Finding your procedure
• Describing your procedure.

Objects, Properties and Methods:

• Introducing Objects, Properties and Methods
• Using the Object Browser
• Editing your VBA code.

Writing Procedures:

• Inserting a module
• Writing your code
• Looping
• Techniques to speed up performance
• Keeping the user informed of progress
• Using the VBA Help facility.

Using Variables:

• Creating Variables
• Types of Variable
• Declaring Variables
• Scope of Variable
• Forcing Declaration of Variables.

Creating an Interface:

• Using Message Boxes, Input Boxes and User Forms
• Linking Controls to and From Worksheets
• On Action Procedures.

Controlling the Worksheet:

• Placing your data correctly
• Using Named ranges
• Adjusting Column widths
• Protecting the Worksheet
• Protecting your procedure.

Handling Errors:

• Syntax Errors
• Run Time Errors
• Logical Errors
• Stepping through your procedure
• Using Break points
• The Immediate and Watch windows
• Using OnError.

Creating Function Procedures:

• Describing a Function procedure
• Writing the Function
• Calling the function
• Saving the Function as an Add-In.

Handling Arrays:

• Describing and Declaring Arrays
• Populating your array with data
• Extracting data from the Array to the worksheet.

Consolidation Exercises

There will usually be time at the end of the course for delegates to consolidate their new skills by creating procedures from scratch. It is strongly recommended that delegates use this time to develop procedures that will automate tasks in their own files. If this is not convenient, further exercises will be set by the trainer.