University of Greenwich Students' Reference

This page contains information and links to sample files for students who have attended my courses. If you have any questions regarding the material here or the course work please email me at martin@fontstuff.com (please remember to mention that you are a course student).

Note that all sample files are supplied in ZIP format for security. Course handouts are in Adobe PDF format which can be read on screen or printed.

Course: Introduction to Access Databases

1: Database Structure

This session explores concept of using databases to store, retrieve and manipulate data and compares the relative advantages and disadvantages of Access and Excel for data handling. It examines the structure of an Access database and begins the process of planning and designing a new database as well as importing and exporting data.

2: Tables

This session introduces the idea of relational databases, looks at how to organise related data in tables, why is often necessary to do so, and how to define the relationships between them.

3: Queries #1

This session deals with basic query techniques and explores the expressions used to filter data. It also shows how you can add power and flexibility to a simple query with the use of parameters.

4: Queries #2

This session deals with the more advanced aspects of queries, illustrating how you can quickly summarise data using the Totals tool and how to build powerful Crosstab Queries. Additional topics include the collection of Action Queries which are used to modify, move and delete data. The zip file contains an Access database with tables of sample data suitable for demonstrating these techniques.

5: Fixing Data

In this session you practice some useful techniques for "normalising" data by fixing bad data and converting data into a more logical format suitable for a relational database. The sample file is an Excel workbook containing a typical example of data which needs to be manipulated before it can be analysed meaningfully in a database.

6: Forms and Reports #1

Forms are used to create a friendly user interface for the database as well as being an important tool for entering, editing and viewing data. Reports based on tables or queries provide a highly customizable way of presenting data for viewing and printing. The design techniques for forms and reports are very similar. This session and the next will concentrate on building a user interface and creating a variety of reports. The zip file contains a part-built sample database containing data similar to that used in the previous session. The data has been normalised in the database. The source Excel file is also included.

Course: Introduction to Excel VBA

You will receive numerous handouts during the course, copies of which are available for download here together with additional useful items. If you are considering buying a reference book on Excel VBA I strongly recommend Excel 2003 Power Programming by John Walkenbach ISBN: 0-7645-4072-6 (follow the link for more details and to read my review).

1: Recording Excel Macros

The handout Recording Macros in Excel is a brief summary of the topics covered in the first lab session and includes an exercise to help you practice the topics learned. The handbook Recording Excel Macros is a printable version of my 52 page ebook (normally sold for $25) which you can either read on screen or print out. It contains a great deal of useful information on recording and editing Excel macros and includes several step-by-step exercises. The sample file contains examples of the code used in the handbook. The link Watch Video Demonstrations takes you to a page where you can watch a selection of annotated video demonstrations of some of the exercises from the handbook.

2: Adding Power to Your Recorded Macros

This part of the course deals with modifying recorded macros to make them more flexible and powerful. It introduces some important techniques that are used widely in VBA programming. The handouts Writing Conditional Code, Introducing VBA Message Boxes and Using Loops to Repeat Code illustrate, with additional study material, the topics covered in the lab session. Two of the handouts are accompanied by a sample Excel workbook containing working examples of the macros described in the handout.

3: Building a User Interface: UserForms and Worksheet Controls

This session shows you different ways to enhance your financial models by adding ways to interact with the "user", the person using the spreadsheet that you have created. You learn how to add and program Worksheet Controls (command buttons, combo boxes, spinner controls etc. on the worksheet itself) and to create professional looking dialog boxes in VBA with UserForms. The handout Build a UserForm for Excel includes an complete project for building a working UserForm that writes data into sequential rows on a worksheet. The accompanying sample file is a finished working example of the project.

4: Summary - Putting It All Together

This session revises the tools and techniques learnt in the previous sessions and looks at some practical examples of using VBA programming to enhance financial models. The link below contains two files, each containing a (very!) simple financial model. One file (WorkbookAutomationExample_Completed.xls) has numerous worksheet controls and a VBA UserForm already included. The other (WorkbookAutomationExample_Empty.xls) is the same file but without any of the tools added, so that you can practice creating them yourself.

 
Hit Counter