Pre-requisites
This course is aimed at delegates who wish to
learn how to automate tasks in Microsoft Excel. Delegates should have a
good working knowledge of Excel, by either attending the Intermediate course,
or from personal experience.
Objective
To provide delegates with the confidence, knowledge and
practical experience to be able to automate tasks in Excel for rapid numerical
analysis & presentation of information. Delegates will learn how by using
macros, they can create flexible solutions that can be used by colleagues with
limited knowledge of Excel.
Overview
Delegates learn how to record and develop automated
procedures in Excel using the Visual basic for Applications language. It emphasizes
hands-on experience through use of sample course files and, if appropriate,
delegates' own worksheets.
Core
Topics
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
Ribbon Buttons
Customising the Quick Access Toolbar
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 UserForms
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.