Course: Microsoft Excel VBA (COMP1238) (Year 3 Students)

This course consists of four two-hour lab sessions in which students will learn how to record and edit macros in Microsoft Excel, and the essentials of writing VBA (Visual Basic for Applications) code to automate their Excel projects. Attention will be given to techniques that will help enhance financial models built in Excel and comprises an important part of the Designing Financial Applications course.

1: Lab Session - Using Command Buttons, Recording and Editing Excel Macros

This session introduces the concept of adding automation to a workbook by creating and programming Command Buttons. It also demonstrates the value of recording macros in Microsoft Excel as a valuable tool in learning to program with VBA. Students will learn to use the Macro recorder, how to edit and enhance macros and where best to save them.

• Handout: Microsoft Excel VBA - Command Buttons (fsExcelVBAUG_1.pdf)
  Instructions for creating and programming a Command Button on an Excel worksheet.
• Handout: Microsoft Excel 2007 – Macros and VBA (fsExcel2007VBAIntro_UG.pdf)
  Handout: Recording Macros in Excel (fsRecordingExcelMacros.pdf)
  Two versions of the same document, one for Excel 2007 with its new user interface and enhanced macro security measures, the other for Excel 2003 and earlier versions. Most of the information is the same but there are some important differences, explained in the 2007 document.
• Sample File: FillEmptyCells.zip
  An Excel workbook containing some data suitable for use with the macro recording exercise in the above handout.
• Handbook: Recording Excel Macros
  This handbook 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.
• Sample File: RecordingExcelMacros.zip
  This sample file contains examples of the code used in the handbook Recording Excel Macros.
• Link: Watch Video Demonstrations
  This link takes you to a page where you can watch a selection of annotated video demonstrations of some of the exercises from the handbook.

2: Lab Session - Building UserForms

This session focuses on building VBA UserForms with particular emphasis on enhancing interactivity in your financial models.

• Handout: UserForm Code Reference Basic Coding (fsUserFormCodeRefBasic.pdf)
  A handy reference of the important code statements that you need to power your VBA UserForms.
• Handout: Build a UserForm for Excel (fsUserForms.pdf)
  This document outlines the techniques used to build a UserForm and includes a step-by-step exercise building and coding a simple UserForm example.
• Sample File: fsUserForms_xls.zip
  A completed working example of the UserForm described in the exercise above.

[Updated: 11/01/2010]