THE SPECIALIST WITH A UNIVERSAL MINDANDREW VAZSONYI, Feature Editor, McLaren School of Business, University of San FranciscoTHE REBIRTH OF BASIC PROGRAMMINGby Andrew Vazsonyi University of San Francisco Before the advent of spreadsheets we implemented mathematical models by programming in BASIC. Spreadsheets phased out this approach and the word programming became a scary, dirty word in business. Limitations of spreadsheets are accepted as facts of life. All this can change now. ENTER VISUAL BASIC Designers of spreadsheets introduced macro languages to increase the power of spreadsheets. Mastering macros is difficult, and so macros are used primarily by experts to empower users to apply spreadsheets. Macros to enlarge the scope of applying mathematical models are difficult to find. Excel Version 5 introduced Visual Basic to write macros and this opens more and better ways to use spreadsheets for the decision sciences. The challenge is to combine the best of two worlds: Basic and spreadsheets. To give you an inkling of what can be done, I calculated an approximate value of <$E PI>, by using two million terms of the Gregory series, as shown by the Visual Basic macro in Exhibit 1. (This is a terrible way to calculate <$E PI>, and Gregory and David Volfovich Chudnovski used a different method to calculate the first 260,321,336 digits of <$E PI fwd 12 .>) It took 182 seconds, on my computer, to get the value 3.141593154. (The correct value to 9 significant digits is 3.141592654.)
Visual Basic Macro to calculate Pi
'Calculate Pi using the Gregory series:
Sub Pi()
T1 = Timer 'Start clock to measure time
' The four statements of the Do loop to get sum of terms
' Calculate Pi and time to run macro
' Output to spreadsheet
' Beep to warn user end of calculations
The above example aims to prove four points:
OPPORTUNITIES The new optimizers/solvers provide good capability to find optimal solutions. But scenario management, including sensitivity, What-IF analysis, can be tedious. Suppose you want to change in a linear programming problem two of the left-hand side coefficients, and want to build a 10 by 10 table for the results. You can write a Visual Basic macro to do the job. You invoke the macro, go and get a cup of coffee, return and inspect the table. You can go even further and create a function of two parameters returning the optimum value. Optimizing some nonlinear problems may be beyond the power of the optimizer/solver included in your spreadsheet package. Often the manager is interested only in certain discrete values of the unknowns. A brute force method that surveys all candidate solutions may be within the power of Visual Basic. You may start with a course lattice of a few million alternatives, and zoom in to a more accurate answer, again requiring a scan of a few million alternatives. You may use a multiple-do loop, or even a function approach. Spreadsheets are favored tools for Monte Carlo simulation. But there are many problems requiring samples of many thousandsþyou run out of cells on the spreadsheet, and the calculations would take too much time anyway. You can run queueing problems with hundreds of thousands of trials within minutes. You can do parametric Monte Carlo simulation to manage scenarios, to build data tables or functions. Visual Basic provides more flexibility in working with graphs. For example you can
COMMENT I will be glad to collaborate with anybody who wishes to develop teaching techniques for Visual BASIC.
Write, call, or fax to my home:
|