Briefly

The credibility of any model depends upon its ability to produce valid and flexible outcomes within a controlled and easy-to-use environment. Macros help to not only provide this environment but also solve problems which normal Excel functions cannot. While the workshop begins with macro development, I have tried to avoid getting bogged down in too much VBA language. All professionals using Excel extensively will gain from attendance at this workshop which includes several useful routines for use in any type of modelling.

Detailed Outline - A full one day workshop                                      

For Whom:
Participants who need better functionality and control over their business applications, but are not interested in becoming VBA programmers. This is for those who want to use elementary macros - both procedural and function types to solve specific problems, and use the automatic macro recorder wherever practicable.

Assumed expertise:
Applicants should be competent in Excel, with a good understanding of functions and arguments, and with sufficient Excel tools at his/her disposal to develop fairly complex spreadsheet solutions. No specific expertise in Macros is assumed since this is an introductory course to automatic and manual macro procedures and functions.

Contents:
Both Control and Function macros are covered in the workshop together with Forms features of Excel 2007 into which macros can be linked. The emphasis will be on procedural control macros, rather than developing VBA programming expertise. However some Function macros will be developed and common routines built as illustrations.

A simple but constructive examination of Excel’s automatic macro recorder and short-cut key initialisation functions are considered with several examples of its use. Specific activity includes:

- The primary macro types - Control and Function procedures.  

- The structure of subroutines and procedures - their location in Modules and simple analysis.

- Creating, storing and using control procedures with Excel’s macro wizard.

- The importance of arguments and dimensions - private and public macros.

- Running a macro - from menu, short key-stroke, and other sub-routines.

- Function macros - some common VBA functions and commands for function Macro development.

- Inputs to macros through direct cell values and message/input boxes.

- Using macros to calculate and enter solutions into spreadsheet cells.

- Using UserForms to control model use  - linking UserForms to macros.  

- Developing user selection variations in UserForms - buttons, slides and lists.

- Common macros used in financial applications are illustrated.