June 1998
Issues in Education:
Using VBA in a Management Science Course
By S. Christian Albright
The issue of emphasizing spreadsheet modeling in Management Science courses is, for many instructors, no longer much of an issue. Wayne Winston and I have strongly advocated this approach in our "Practical Management Science" textbook (Duxbury 1997), as have other recent competing textbooks, and the market appears to be responding very favorably. In this article, I take the advantages of the spreadsheet approach as a given and go one step further.
Specifically, I advocate allocating part of the course to spreadsheet modeling and part to developing applications for end-users by using Visual Basic for Applications (VBA), the built-in Microsoft Office programming language. I have been doing this at Indiana University for several years in a course for undergraduate Computer Information Systems (and related) majors, and it has received extremely high evaluations from the students.
Admittedly, this type of course is not for every audience. My students have had at least one programming course (either in C or Visual Basic), and learning more programming is arguably as valuable to them as learning how to develop linear programming or other MS models. Most of these students go on to consulting jobs, where they are often required to develop an application for a client who is not an expert in or even aware of MS models.
Therefore, my course trains them how to "do it all." Students learn how to develop an LP model in a spreadsheet and then use VBA to embed this in an application with a front end and a back end. The front end is the part the user sees first. It typically explains the capabilities of the application and allows the user to enter input parameters for the model. After the user completes this part, the back end then creates a user-friendly report that lists the outputs of the model, either in numerical or graphical form.
It then allows the user to start over with new inputs. In the midst of this, there is a "formulation" sheet where the model is developed possibly on the fly in response to the user's inputs. Although we obviously spend a lot of class time learning how to create this model this MS part of the course consumes at least half of the class periods this sheet will probably be hidden from the user. The user only sees the understandable part: the user-friendly report.
Some instructors might be apprehensive about introducing computer programming into an MS course that is already packed with topics. This is a legitimate concern, and I again admit that this course is not for every audience.
There is no doubt that when I introduced VBA, some of the usual MS topics had to go. In fact, the only MS topics I now include are LP (together with a touch of IP and NLP) and simulation. However, I'm willing to sacrifice a few traditional MS topics to make room for VBA. My reasons include the following:
(1) VBA looks at least as good on my students' resumes as MS.
(2) VBA greatly expands students' abilities to exploit (and understand) Excel's capabilities.
(3) VBA provides a great introduction to object-oriented programming a really hot topic in today's computing world.
(4) The learning curve for VBA is less steep than for other programming languages such as C. For example, students can use Excel's recorder to generate code (and then modify it as necessary). This not only gives them success early in the course, but it also enables them to learn the language quicker.
(5) Weaving back and forth between MS and VBA class periods decreases the possibility of boredom.
(6) The variety of applications one can develop are unlimited. For example, I recently learned how to use the DAO (data access object) class to bring data from an Access database into Excel, programmatically, and then use this data as input to an MS model. Students' eyes lit up when they learned about this possibility. The ability to write SQL queries in VBA in order to import external data for an LP model wow!
There is a downside to this approach: It takes me considerably more time to prepare for classes than when I taught just MS modeling. The sample VBA applications I write and exhibit in class must work correctly, be well-documented and be written in a clear, understandable manner so that students can learn from them and emulate them. In other words, they can't just be written for my own personal use. This takes time, and it certainly doesn't help when there are several not-completely-compatible versions of VBA floating around! However, it's all worth the work when students thank me for how much they learned and how valuable this material will be in their jobs.
This topic of VBA in MS courses was the subject of an education session at the recent Dallas INFORMS meeting. (Speakers besides myself included Rob Easley from Notre Dame, Cliff Ragsdale from Virginia Tech and Sam Savage from Stanford.)
Judging from the extremely large turnout, there appears to be a lot of interest in this approach. If you are interested, perhaps the best way to see some of the possibilities is to look at the Web site for my course: http://www.indiana.edu/~busk410. As with most Web sites, it's always changing and might not always be up-to-date. But because I believe this is an area where we ought to take advantage of what others such as myself have done, I invite you to be my guest!
S. Christian Albright is a professor of Operations and Decision Technologies at Indiana University in Bloomington, Ind.
|
|
OR/MS Today copyright © 1998 by the Institute for Operations Research and the Management Sciences. All rights reserved.
Lionheart Publishing, Inc.
506 Roswell Street, Suite 220, Marietta, GA 30060, USA
Phone: 770-431-0867 | Fax: 770-432-6969
E-mail: lpi@lionhrtpub.com
URL: http://www.lionhrtpub.com
Web Site © Copyright 1998 by Lionheart Publishing, Inc. All rights reserved.