- Email: [email protected]
Appendices
Appendices
APPENDIX A GUIDE TO VISUAL BASIC FOR APPLICATIONS (VBA) IN EXCEL Excel’s programming language is called Visual Basic for Applications (VBA) and VBA programming code is considered to be a Macro. As a programming language, it contains many features and control structures that are not immediately useful to a scientist or engineer. VBA is powerful convenient and so on for performing calculations and analyses within a spreadsheet and it can give results almost instantaneously via user-written functions or subroutines. VBA is an object-oriented programming language, which means that things such dialog boxes, menus, windows, spreadsheets, workbooks and so on are objects that can be changed or controlled by programming. In this text, the only objects considered are the Module that contains the VBA programming code and the Spreadsheet or Sheet that contains input data and output results. This guide will describe how to write scientific functions and subroutines that communicate between VBA and the spreadsheet.
A.1
Accessing the VBA Programming Environment
To begin, it is helpful if one accesses the VBA programming environment, which may not be obvious depending on the version of Excel. In older versions of Excel, VBA is accessed by first making sure that it is installed (Tools, Addins, Analysis Toolpak-VBA). Then, VBA is accessed through the Macro Sub-Menu. In newer versions of Excel, VBA is accessed through Developer Menu of the Excel spreadsheet. If the Developer Menu is not visible, then in Excel 2010, one has make sure that the Developer Menu item is checked under the Excel Options under the File Menu-Options-Customize Ribbon:
695
696
Appendices
Once that the box is checked and the OK button is clicked, the Developer Menu should appear on the Microsoft ribbon. In each version of Excel, the procedure for accessing the VBA programming environment has changed. However, it is useful to remember that the VBA programming environment is considered to be used for programming Macros so that enabling access to the Macro environment will probably also allow access to the VBA programming environment. After returning to the Worksheet or Spreadsheet, the Developer Menu should appear on the Excel Ribbon:
It is important to Click on Macro Security and Enable all macros. Further, it is important to save the spreadsheet as a Macro-Enabled worksheet to insure that the Worksheet that now has file type *.xlsm rather than *.xls.
Some of the problems associated with having programs run can be traced to these steps.
Appendices
A.2
697
Overview of the VBA Programming Environment
After clicking on Visual Basic on the Ribbon, the VBA programming environment appears:
The screen might appear differently from the above figure due to sizing of the windows, the number of opened Workbooks or installed add-ins. To open the Immediate window, select View then Immediate Window. In the present screen, the Project Window “Project—VBAProject” shows that Book 1 contains four Excel objects: three spreadsheet pages (Sheet 1, Sheet 2, Sheet 3) and ThisWorkbook. There are several add-ins that contain proprietary code. For the most part, the scientific user does not need to be concerned with any of these objects or details. However, there are no Modules in the VBAProject and at least one module is needed to contain user-functions and subroutines.
698
Appendices
A.3 Adding a Module to Contain User-Functions and Subroutines To add a Module, select Insert and then Module. All other selections are not needed by general scientific users (Procedure, UserForm, ClassModule, File). Then, the VBAProject should look like the following:
where it can be seen that a folder Modules now appears with Module1 now appears. Module1 can contain any number of functions and subroutines subject to the limitations of Excel and computer memory. More than one module can be added to organize the programs. For example, a single module could contain all of the constants, functions and subroutines needed for a given equation of state. Another module could contain the functions and subroutines for another equation of state. The modules can be named by clicking on Module1 in the Project and then on the Name field of the Properties—Module1 box. The modules can be imported or exported through the VBA File menu.
Appendices
A.4
699
Example VBA Functions and Subroutines in a Module
Some sample functions and subroutines will help to illustrate their uses and differences. Six different functions are shown below:
Once that these functions are typed into Module1, they are immediately available in the main spreadsheet. By typing the function names with parentheses and without any arguments, the results will be calculated and displayed as below:
700
Appendices
Results of the sample functions are shown to the right. Examining the typed function names, ¼Calc1(), calc2(), etc., it is clear that VBA does not distinguish between upper and lower case function names. Moreover, VBA “decides” which function names should be uppercase and which should be lowercase, seemingly at random, and corrects “¼Calc2 ()” to “¼calc2()” in the second function. The exact case of the function in the VBA programming environment can be specified if the variable or function is given in a declaration statement. However, the Excel spreadsheet tends to remember the function name according to the first change of the upper/lower case of the function name regardless of the declaration. By changing to the VBA programming environment, the Immediate window shows the results of printing intermediate results by the program statement: Debug.Print. It is clear that the calculation order is approximately from top to bottom, but Excel decides the precise order according to cell modification. For this case, the Calculate Full ( ) selection was used. Calculate Full is available by addition to the tool bar and it is recommended for making sure that Excel performs calculations when desired. Closing the Project Window is done by clicking on the X of the Project— VBAProject window. Opening the Project Window is by clicking on Project Explorer under the View menu or by clicking on the Project Explorer icon ( ) on the toolbar.
Appendices
701
The function names are accessed from the spreadsheet by typing “¼” in a cell. For the case of typing “¼C,” the following drop-down box appears with the list of function names:
The function can be rapidly input into the cell by using the up/down arrow and then hitting Enter or Return. This means that long function names are easily used. Long function names or function names with an underscore, “_,” are desirable because beginning with Excel 2010, the number of Spreadsheet cells was increased using up to three letters for columns. Function names such as “STP1” are no longer valid as they refer to a column name at location STP1 rather than a function name. It is preferable to write VBA function names as “S_TP1.”
Tips: Select range Replace ¼ with ¼ ALL to recalculate selected cell ranges only Print intermediate values in VBA in the Immediate Window of the VBA Editor Debug.Print “variable ¼ ”, variable
702
Appendices
APPENDIX B GUIDE TO IMPORTING DATA INTO 3D GRAPHER B.1 Installation of 3D Grapher (Windows Version Only Available) 1. Download from http://www.romanlab.com/3dg/. 2. Install. The program is freeware for 30 days. A university site license costs only about USD 100, but before asking your professor to buy one, download it and test it with the supplied 3D Grapher files of this text before asking a faculty member to buy it. Your university may have something that can plot and rotate data just as well. I like this one because its small and simple, but it does have lots of faults. Nevertheless, it is interesting so I hope that you will try it. If you don’t have a site license, skip to step 6. 3. Get Registry file from your university homepage if you have. 4. Put Registry file into the 3D Grapher folder. 5. Double click on it. Click OK to modify your registry. 6. Enjoy!
B.2 3D Grapher Files Supplied
Appendices
B.3
Steps for Making Your Own 3D Plots in 3D Grapher
Important Notes: 1. First make the calculations and then make columns of values in the proper order and with the desired units. 2. Follow the steps and make a simple table plot first that consists of N sets of 3 1 values. That is, a column x1-T-P values with N rows. 3. After making the columns of data, copy it and open 3D Grapher. Select Graphs, Add Table Graph. . . 4. Input a name for the Table Graph. 5. See the next page BEFORE pasting.
703
704
Appendices
Important Notes: 1. Put the number of ROWS into the Data sizes box. The program assumes that you have three values for each row. For 629 rows, the data size will be 629 1 2. For a surface (face) plot the data size is 629 2 but there must be twice as much data arranged as x1-T1-P1 A y1-T1-P1 B
Appendices
Important Notes: 1. It is important to specify the size of the vector before pasting. Otherwise, everything will be pasted into one cell and the results will be lost. 2. A surface plot (Face) uses a Data size of N 2. See the supplied 3D Grapher files for examples. 3. A single point can also be plotted. This is convenient for plotting a critical point of a pure substance or special points on the diagram such as a feed composition. 4. A pair of points can be used to plot a tie-line.
705
706
Appendices
Important Notes: 1. You may not see ANYTHING at first. This is because the SCALE of the plot must be set manually. Go to the Graphs ! Options. . . menu and change the scales of the x, y and z axis from their general values (10, þ10) to values for x-T-P, for example, (0,1),(0,400),(0,40) for x, C, MPa. 2. There is no axes labeling in the program. So keep things in order. Screens can be copied and pasted into PowerPoint for labeling. 3. The ORDER of the Table plots cannot be changed. They can be selected (checked) for plotting but they will remain in their order of construction.
707
Appendices
APPENDIX C
PARTIAL LIST OF EXCEL WORKSHEETS
Properties of CO2 CO2_v60.xlsm Properties of H2O H2O_v62.xlsm Thermophysical and pure substance critical point estimation: Joback_Method.xls Co-current and Counter-current heat exchanger: Heat_Exchanger_v20.xlsm Solid-vapor equilibria including co-solvents: SVE-PR_EoS_v13.xlsm Vapor-liquid equilibria at constant temperature: VLE-constant_T-PR_EoS.xlsm Solid-liquid-vapor equilibria: SLVE-PR_EoS-v14.xlsm Critical points of mixtures: CRITICAL_LOCI_v13.xlsm Chemical equilibria: AmmoniaSynthesisTadiabatic-v14.xlsm Chemical equilibria with critical point analysis: Hydroformylation-Critical-v11.xlsm
APPENDIX A GUIDE TO VISUAL BASIC FOR APPLICATIONS (VBA) IN EXCEL Excel’s programming language is called Visual Basic for Applications (VBA) and VBA programming code is considered to be a Macro. As a programming language, it contains many features and control structures that are not immediately useful to a scientist or engineer. VBA is powerful convenient and so on for performing calculations and analyses within a spreadsheet and it can give results almost instantaneously via user-written functions or subroutines. VBA is an object-oriented programming language, which means that things such dialog boxes, menus, windows, spreadsheets, workbooks and so on are objects that can be changed or controlled by programming. In this text, the only objects considered are the Module that contains the VBA programming code and the Spreadsheet or Sheet that contains input data and output results. This guide will describe how to write scientific functions and subroutines that communicate between VBA and the spreadsheet.
A.1
Accessing the VBA Programming Environment
To begin, it is helpful if one accesses the VBA programming environment, which may not be obvious depending on the version of Excel. In older versions of Excel, VBA is accessed by first making sure that it is installed (Tools, Addins, Analysis Toolpak-VBA). Then, VBA is accessed through the Macro Sub-Menu. In newer versions of Excel, VBA is accessed through Developer Menu of the Excel spreadsheet. If the Developer Menu is not visible, then in Excel 2010, one has make sure that the Developer Menu item is checked under the Excel Options under the File Menu-Options-Customize Ribbon:
695
696
Appendices
Once that the box is checked and the OK button is clicked, the Developer Menu should appear on the Microsoft ribbon. In each version of Excel, the procedure for accessing the VBA programming environment has changed. However, it is useful to remember that the VBA programming environment is considered to be used for programming Macros so that enabling access to the Macro environment will probably also allow access to the VBA programming environment. After returning to the Worksheet or Spreadsheet, the Developer Menu should appear on the Excel Ribbon:
It is important to Click on Macro Security and Enable all macros. Further, it is important to save the spreadsheet as a Macro-Enabled worksheet to insure that the Worksheet that now has file type *.xlsm rather than *.xls.
Some of the problems associated with having programs run can be traced to these steps.
Appendices
A.2
697
Overview of the VBA Programming Environment
After clicking on Visual Basic on the Ribbon, the VBA programming environment appears:
The screen might appear differently from the above figure due to sizing of the windows, the number of opened Workbooks or installed add-ins. To open the Immediate window, select View then Immediate Window. In the present screen, the Project Window “Project—VBAProject” shows that Book 1 contains four Excel objects: three spreadsheet pages (Sheet 1, Sheet 2, Sheet 3) and ThisWorkbook. There are several add-ins that contain proprietary code. For the most part, the scientific user does not need to be concerned with any of these objects or details. However, there are no Modules in the VBAProject and at least one module is needed to contain user-functions and subroutines.
698
Appendices
A.3 Adding a Module to Contain User-Functions and Subroutines To add a Module, select Insert and then Module. All other selections are not needed by general scientific users (Procedure, UserForm, ClassModule, File). Then, the VBAProject should look like the following:
where it can be seen that a folder Modules now appears with Module1 now appears. Module1 can contain any number of functions and subroutines subject to the limitations of Excel and computer memory. More than one module can be added to organize the programs. For example, a single module could contain all of the constants, functions and subroutines needed for a given equation of state. Another module could contain the functions and subroutines for another equation of state. The modules can be named by clicking on Module1 in the Project and then on the Name field of the Properties—Module1 box. The modules can be imported or exported through the VBA File menu.
Appendices
A.4
699
Example VBA Functions and Subroutines in a Module
Some sample functions and subroutines will help to illustrate their uses and differences. Six different functions are shown below:
Once that these functions are typed into Module1, they are immediately available in the main spreadsheet. By typing the function names with parentheses and without any arguments, the results will be calculated and displayed as below:
700
Appendices
Results of the sample functions are shown to the right. Examining the typed function names, ¼Calc1(), calc2(), etc., it is clear that VBA does not distinguish between upper and lower case function names. Moreover, VBA “decides” which function names should be uppercase and which should be lowercase, seemingly at random, and corrects “¼Calc2 ()” to “¼calc2()” in the second function. The exact case of the function in the VBA programming environment can be specified if the variable or function is given in a declaration statement. However, the Excel spreadsheet tends to remember the function name according to the first change of the upper/lower case of the function name regardless of the declaration. By changing to the VBA programming environment, the Immediate window shows the results of printing intermediate results by the program statement: Debug.Print. It is clear that the calculation order is approximately from top to bottom, but Excel decides the precise order according to cell modification. For this case, the Calculate Full ( ) selection was used. Calculate Full is available by addition to the tool bar and it is recommended for making sure that Excel performs calculations when desired. Closing the Project Window is done by clicking on the X of the Project— VBAProject window. Opening the Project Window is by clicking on Project Explorer under the View menu or by clicking on the Project Explorer icon ( ) on the toolbar.
Appendices
701
The function names are accessed from the spreadsheet by typing “¼” in a cell. For the case of typing “¼C,” the following drop-down box appears with the list of function names:
The function can be rapidly input into the cell by using the up/down arrow and then hitting Enter or Return. This means that long function names are easily used. Long function names or function names with an underscore, “_,” are desirable because beginning with Excel 2010, the number of Spreadsheet cells was increased using up to three letters for columns. Function names such as “STP1” are no longer valid as they refer to a column name at location STP1 rather than a function name. It is preferable to write VBA function names as “S_TP1.”
Tips: Select range Replace ¼ with ¼ ALL to recalculate selected cell ranges only Print intermediate values in VBA in the Immediate Window of the VBA Editor Debug.Print “variable ¼ ”, variable
702
Appendices
APPENDIX B GUIDE TO IMPORTING DATA INTO 3D GRAPHER B.1 Installation of 3D Grapher (Windows Version Only Available) 1. Download from http://www.romanlab.com/3dg/. 2. Install. The program is freeware for 30 days. A university site license costs only about USD 100, but before asking your professor to buy one, download it and test it with the supplied 3D Grapher files of this text before asking a faculty member to buy it. Your university may have something that can plot and rotate data just as well. I like this one because its small and simple, but it does have lots of faults. Nevertheless, it is interesting so I hope that you will try it. If you don’t have a site license, skip to step 6. 3. Get Registry file from your university homepage if you have. 4. Put Registry file into the 3D Grapher folder. 5. Double click on it. Click OK to modify your registry. 6. Enjoy!
B.2 3D Grapher Files Supplied
Appendices
B.3
Steps for Making Your Own 3D Plots in 3D Grapher
Important Notes: 1. First make the calculations and then make columns of values in the proper order and with the desired units. 2. Follow the steps and make a simple table plot first that consists of N sets of 3 1 values. That is, a column x1-T-P values with N rows. 3. After making the columns of data, copy it and open 3D Grapher. Select Graphs, Add Table Graph. . . 4. Input a name for the Table Graph. 5. See the next page BEFORE pasting.
703
704
Appendices
Important Notes: 1. Put the number of ROWS into the Data sizes box. The program assumes that you have three values for each row. For 629 rows, the data size will be 629 1 2. For a surface (face) plot the data size is 629 2 but there must be twice as much data arranged as x1-T1-P1 A y1-T1-P1 B
Appendices
Important Notes: 1. It is important to specify the size of the vector before pasting. Otherwise, everything will be pasted into one cell and the results will be lost. 2. A surface plot (Face) uses a Data size of N 2. See the supplied 3D Grapher files for examples. 3. A single point can also be plotted. This is convenient for plotting a critical point of a pure substance or special points on the diagram such as a feed composition. 4. A pair of points can be used to plot a tie-line.
705
706
Appendices
Important Notes: 1. You may not see ANYTHING at first. This is because the SCALE of the plot must be set manually. Go to the Graphs ! Options. . . menu and change the scales of the x, y and z axis from their general values (10, þ10) to values for x-T-P, for example, (0,1),(0,400),(0,40) for x, C, MPa. 2. There is no axes labeling in the program. So keep things in order. Screens can be copied and pasted into PowerPoint for labeling. 3. The ORDER of the Table plots cannot be changed. They can be selected (checked) for plotting but they will remain in their order of construction.
707
Appendices
APPENDIX C
PARTIAL LIST OF EXCEL WORKSHEETS
Properties of CO2 CO2_v60.xlsm Properties of H2O H2O_v62.xlsm Thermophysical and pure substance critical point estimation: Joback_Method.xls Co-current and Counter-current heat exchanger: Heat_Exchanger_v20.xlsm Solid-vapor equilibria including co-solvents: SVE-PR_EoS_v13.xlsm Vapor-liquid equilibria at constant temperature: VLE-constant_T-PR_EoS.xlsm Solid-liquid-vapor equilibria: SLVE-PR_EoS-v14.xlsm Critical points of mixtures: CRITICAL_LOCI_v13.xlsm Chemical equilibria: AmmoniaSynthesisTadiabatic-v14.xlsm Chemical equilibria with critical point analysis: Hydroformylation-Critical-v11.xlsm