Advanced Excel with Visual Basic




Dale R. "Zai" Fox, Ph.D.



Office Hours: 2:00 - 3:30 PM  Tuesday and Thursday
5:30 - 6:25 PM Tuesday 

other hours by appointment





Walkenbach, Microsoft Excel 2000 Power Programming with VBA, IDG Books Worldwide.

Additional References: Mayes & Shank, Financial Analysis with Microsoft Excel, The Dryden Press.

Beninga, Financial Modeling, The MIT Press.


I will approach this class by focusing on aspects of VisualBasic that relate to spreadsheet  program development.   This will include studying both macros and procedures among many other things.  I will use the text as a supplement for teaching programming constructs such as arrays, conditional branching and looping among many other things.

The course will emphasize use of what are likely to be considered financial or accounting models to motivate the development.


- 52% of your grade will be based on two tests. The tests will be a mixture of multiple choice and short answer with some program debugging.

- 48% of your grade will be based on four homework assignments. All of the problems need be turned in individually.  On the other hand, you are expected to work together to develop your solutions.   Each homework assignment will be graded on a 100 point scale. They will be due at the beginning of the class period on the day they are due. If you turn a homework assignment in late within a day of the due date you will lose 5 points. If you turn it in late within 3 days of the due date you will lose 10 points. Anything turned in later than 3 days but still within a week will result in an automatic loss of 15 points. Finally, anything more than a week late will result in an automatic loss of 25 points.

You will want to e-mail your solutions to me by the date and time they are due. Any written discussion should be placed in a Word file and attached/inserted. You will also attach/insert things such as forms and projects to your e-mail.


Weekly Topic Coverage

Week 1

Getting acquainted
Some Excel Background While Looking at Loan Payments
Some More Issues Relating to Mortgages
Computing PMI Rates Using the If( ) Function

Week 2

Cell and Range References and Names
Some Principles of Spreadsheet/Workbook Development
The Visual Basic Integrated Development Environment
Macros and Recording Macros
Homework 1

Week 3

Developing Income Statements Using a Macro
Assessing Long Term Stock Risk

Measuring Portfolio Risk

Week 4

Developing User Defined Functions for Assessing Risk and Return
Some General Issues Concerning Functions
Computing PMI Using a User Defined Function

Week 5

Some Background on UserForms, Controls and Events
Input Validation for Functions
Developing a Form for Determining Information About Annuities
Homework 2

Week 6

A Bit About Combo Boxes
Spending a Week to Polish Up Earlier Lessons and Review 

Week 7

Return Tests

Week 8

Creating a UserForm that Writes Data to a Spreadsheet
Improving the UserForm's Input Validation and Access to Past Data

Week 9

Creating a PivotTable
Using External Databases in PivotTables
Homework 3

Week 10

Some Background on Forecasting
Exponential Smoothing
Measuring Prediction Validity for Exponential Smoothing

Week 11

Moving Average Forecasting
Measuring Prediction Validity for Moving Averages
Homework 4

Week 12

Creating and Customizing Toolbars
Creating and Customizing Menus

Week 13

Using Add-ins
Creating Add-Ins

Week 14



Optional Test


Return to Dale R. "Zy" Fox's Courses Page

Return to Dale R. "Zy" Fox's Home Page