1612
Ind. Eng. Chem. Res. 2000, 39, 1612-1613
A Perspective: The Use of the Spreadsheet for Chemical Engineering Computations E. M. Rosen* EMR Technology Group, 826 Brae Court, Chesterfield, Missouri 63017
L. R. Partin† L. R. Partin Enterprises, 101 Partridge Place, Kingsport, Tennessee 37663
The modern spreadsheet program as exemplified by Microsoft’s Excel (version 5 and higher)1 has developed into a suitable platform for carrying out a large variety of chemical engineering computations. The availability of its macro language, Visual Basic for Applications (VBA), played a key role in this capability. VBA provides a programming environment for developing new software applications. It also allows Excel to link with programs written in other languages such as FORTRAN. Therefore, the wealth of existing FORTRAN programs and numerical methods can be converted for application in Excel. General Background Since the introduction of VisiCalc in 1979,2 the spreadsheet has grown in both capability and popularity. Today, numerous books,3-5 articles, seminars, and Internet web sites abound giving training, advice, and descriptions on the use of spreadsheets. The impact on chemical engineering computations in particular has been no less dramatic. Rosen and Adams6 gave a review of some of the early applications of the spreadsheet in chemical engineering. For example, spreadsheets were quickly applied for process mass balances.7-9 Cutlip10 has shown that the spreadsheet is often competitive with a number of other mathematical software packages.11 Visual Basic for Applications The introduction of Visual Basic for Applications (VBA) as the macro language for Microsoft Excel Version 5.0 in 19932 greatly enhanced the capability of the spreadsheet to carry out comprehensive chemical engineering computations. The language provides a way of invoking a large number of built-in procedures as well as writing user add-in procedures.12,13 Use of the spreadsheet to integrate ordinary differential equations and partial differential equations was discussed by Rosen.14,15 Excel adds important features to these capabilities through its Goal Seek command for algebraic equation root finding and its Solver procedure for nonlinear equation set solution and optimization. Comprehensive examples of the use of VBA with Excel have been developed by Patrick16 and Partin.17,18 Giving New Life to FORTRAN With the shift in academia to the teaching of Visual Basic and the retirement of many FORTRAN practitioners, the interest in maintaining much of the chemical engineering technology developed in FORTRAN appears * To whom correspondence should be addressed. E-mail:
[email protected]. Fax: 530-324-7567. † E-mail:
[email protected].
Figure 1. Legacy FORTRAN Subroutine EigD.
to be slowing. The ease by which FORTRAN subroutines can be called from VBA provides a possible way of maintaining much of this legacy code.19 Executable FORTRAN programs can be integrated into the spreadsheet using the VBA shell function.20 Consider that it is desirable to include legacy FORTRAN subroutine EIGD into an Excel spreadsheet (Figure 1). The routine calls subroutine EVCSF (IMSL FORTRAN 90 MP Library Version 3.0) to find the eigenvalues and eigenvectors of a real symmetric matrix. The IMSL library is available as part of the Digital Visual FORTRAN Professional (version 5.0 and above) (DVF) compiler. Only two new lines of code need be added to the legacy code. The two ATTRIBUTES statements (!DEC$...) are placed at the very beginning of the subroutine. (Note: The USE NUMERICAL_LIBRARIES statement is required by the compiler to access the IMSL numerical method routines). The next step is to create a dynamic link library. This is done in the interactive Developer Studio environment or as the following DOS command:
DF /DLL:EIGD EIGD.FOR where EIGD is the name of the generated dynamic link library and EIGD.FOR is the name of the FORTRAN source code. As a result of the command, the following three files are created: EIGD.DLL, EIGD.EXP, and EIGD.LIB. The third step is to write an array function (Eigenv) in VBA as shown in Figure 2. Note that a DECLARE
10.1021/ie990640e CCC: $19.00 © 2000 American Chemical Society Published on Web 03/21/2000
Ind. Eng. Chem. Res., Vol. 39, No. 6, 2000 1613
Figure 3. Spreadsheet utilizing array function Eigenv.
scientific community. It provides a wealth of computational and reporting capabilities that are used routinely. With the advent of Visual Basic for Applications, legacy FORTRAN programs of the past (either in subroutine or executable form) can be utilized in the spreadsheet environment. The example spreadsheet may be downloaded from http://ourworld.cs.com/edwardmemrose/. Literature Cited
Figure 2. VBA array function Eigenv.
statement must be used to describe how arguments are passed to the EIGD entry point subroutine of EIGD.DLL. The output array EV is set equal to the name of the array function procedure Eigenv. The path given in quotes is the path to the location of the dynamic link library. Note that array EV is defined as a variant type to allow the storage of different data types. To utilize the array function on the spreadsheet (Figure 3), an area on the spreadsheet of equal size to the EV output array (B13 to D17) must be highlighted and the command )Eigenv(B6:D8) must be entered followed by pressing Control + Shift + Enter. B6 is the start of the input matrix and D8 is the end location. The eigenvector columns are placed one row below its corresponding eigenvalue in the EV array (see the VBA coding of Figure 2). Conclusions The Microsoft Excel spreadsheet program (version 5.0 and higher) is widely accepted by the engineering and
(1) Walkenbach, J. MicroSoft Excel 2000 Power Programming With VBA; IDG Books Worldwide: Foster City, 1999. (2) Power, D. J. A Brief History of Spreadsheets, http:// dssresources.com/history/sshistory.html. (3) Billo, E. J. Excel for Chemists; Wiley-VCH: New York, 1997. (4) Bloch, S. C. Spreadsheet Analysis for Engineers and Scientists; Wiley: New York, 1995. (5) Diamond, D.; Hanratty, V. C. A. Spreadsheet Applications in Chemistry Using MicroSoft Excel; Wiley: New York, 1997. (6) Rosen, E. M.; Adams, R. N. A Review of Spreadsheet Usage in Chemical Engineering Calculations. Comput. Chem. Eng. 1987, 6, 723. (7) Schmidt, W. P.; Upadhye, R. S. Material Balances on a Spreadsheet. Chem. Eng. 1984, Dec 24, 67. (8) Goldfarb, S. M. Spreadsheets for Chemical Engineers. Chem. Eng. 1985, April 15, 91. (9) Julian, F. M. Flowsheets and Spreadsheets. Chem. Eng. Prog. 1985, Sept, 35. (10) Cutlip, M. B. Chemical Engineering Problems With Solutions. CACHE Corporation, Aug 1997; August; http://www.cache.org/. (11) Philips, J.; DeCicco, J. D. Choose the Right Mathematical Software. Chem. Eng. Prog. 1999, July, 69. (12) Rosen, E. M. Visual Basic for Applications, Add-Ins and Excel 7.0. CACHE News 1997, 45 (Fall). (13) Rosen, E. M. The Case for Excel and Visual Basic for Applications. CACHE News 1998, 46 (Spring). (14) Rosen, E. M. Integrating Differential Equations Using Excel 7.0. CACHE News 1998, 47 (Fall). (15) Rosen, E. M. Excel 7.0: Partial Differential Equations. CACHE News 1999, 48 (Spring). (16) Patrick, R. K. Use Spreadsheets to Make Safety Fault Trees Easy to Use. Chem. Eng. Prog. 1999, April, 69-72. (17) Partin, L. R. Application of PC-Based Software in Process Engineering. Presented at Chemputers Conference, Feb 15, 1995. (18) Partin, L. R. http://users.intermediatn.net/lpartin. (19) Rosen, E. M. Calling FORTRAN Subroutines from Excel 7.0. CACHE News 1999, 48 (Spring). (20) Rosen, E. M. Executing FORTRAN Programs from Excel: Use of the Shell Function. CACHE News 1998, 47 (Fall).
Received for review August 26, 1999 Revised manuscript received January 14, 2000 Accepted January 24, 2000 IE990640E
