As an alternative to the FORTRAN calculations, use the Excel procedure to perform calculations to determine the results of different input conditions.
In Excel procedure, the specified parameter, constant, stream data, and component properties are transferred to a Microsoft Excel spreadsheet, which invokes a user-defined macro to perform the calculations and sends the results back to PRO/II in R[] array. Other unit operations and streams can refer to the results using the Define system.
An Excel spreadsheet template (ExcelCalculatorTemplate.xlsm) is provided along with the PRO/II application. It is available in the User folder. It has two worksheets, Parameter worksheet and Component worksheet, which are used to transfer data from PRO/II to Excel, and vice versa. Also, all the values are saved according to the UOM set customization in the spreadsheet.
Note: You can customize the spreadsheet to include new macro and new formulas.
To use the Excel procedure
In PRO/II, double-click the calculator unit operation module.
Select the Use Excel Procedure check box.
Select the required Thermodynamic System from the Thermodynamic System drop-down list box.
In Excel Procedure section, enter the details pertaining to the following fields.
Spreadsheet Name: Browse and select the spreadsheet required for performing the calculations.
Worksheet Name: Enter the name of the worksheet that contains fields for calculating and displaying parameters. If the worksheet name is not entered, P2Parameter is used by default.
Component Sheet: Enter the name of the worksheet that contains fields for calculating and displaying components. If the Component sheet name is not entered, P2Component is used by default.
Macro Name: Enter the name of the macro defined in the spreadsheet for performing the calculations. If the macro name is not entered, Macro 1 is used by default.
Note: If the information entered in any of the above fields is incorrect, an error message is displayed.
Select any of the following check boxes, as necessary.
Display Excel during calculations: Select this check box to display the Excel spreadsheet while calculations are performed.
Save Excel after calculations: Select this check box to save the Excel spreadsheet after the calculations are performed.
Keep Excel open after calculations: Select this check box to keep the Excel spreadsheet open after the calculations are performed.
“…” button will allow the user to select the file. This button will point to TEMP folder if there are any excel templates present in the TEMP folder. If no excel files are present in TEMP folder then it will point to user folder where the default template is present.
If the user selects an excel file from non-TEMP location then it will be copied to the TEMP location immediately. If the file with similar name is already present in the TEMP folder then it will pop-up the following window to select any one option.
First option will overwrite the existing file in the TEMP directory.
Second option will copy the file to TEMP directory with the given name.
Third option will create the copy of the P2Parameter and P2Component worksheets to the same excel file present in the TEMP folder and update the worksheet names in the main window. The copied worksheet names will be appended with “_n” where n is the index increment starting with one.
Edit button will open the template from the TEMP folder allowing modifying.
Rename button will allow the user to rename the excel file in the TEMP folder.
Click OK.
Run the simulation.
Rules for Transferring Data from Excel Spreadsheet to PRO/II, and Vice Versa.
In the Excel spreadsheet, if formulae are available for Molar rate (total or component), Mass rate, Volume rate, Temperature, or Pressure in the respective cells, their values are not transferred from PRO/II to Excel.
If the result of formula or macro for Molar rate (total or component), Mass rate, Volume rate, Temperature, or Pressure is different from the values available in PRO/II, Special Stream functions, such as SRXSTR are automatically used by PRO/II to re-flash the changed values in the respective streams.
In the Excel Spreadsheet, if there is a change in any or all of the flow rates (Component, Total Molar, Total Mass, and Total Standard liquid rate) of a stream, the data is transferred from Excel spreadsheet to PRO/II in the following order:
Individual component flow rates are updated and normalized.
Total molar flow rates are updated. Individual component flow rates are changed proportionately.
Total Mass flow rates are updated. Individual component flow rates are changed proportionately.
Total Volumetric flow rates are updated. Individual component flow rates are changed proportionately.
Note: In the Excel spreadsheet, modification of individual component flow rates for Assay and Spiral streams is not recommended.