Example of Using the Excel Unit Op

Example of Using the Excel Unit Op

CHEMCAD allows use of a custom unit operation model specified in Microsoft Excel. Some customization of CHEMCAD files is required. The unit operation model must be written in Visual Basic for Applications (VBA). This document is a tutorial that guides the advanced user through the process of customizing CHEMCAD files and inserting VBA code in the framework provided by Chemstations. The user is responsible for developing their own VBA model of a unit operation.

The Excel Unit Op will be utilized to perform the task of a mixer in this example.  First a new job is created in CHEMCAD. Name the job Excel.  Two feed streams and one outlet stream are connected to an  Excel Unit Op.  No other unit ops are needed. Connect the flowsheet as shown, then double click the the Excel Unit Op to open its dialog box.

Upon opening the dialog box, the File Paths screen will appear. 

The first field displays the file name of the custom dialog box used by this unit.  For the first part of this example, this will be ignored.  Custom dialog boxes will be addressed in the second part of the example.  This field can either be deleted or left with the default value.  If it is left, an example dialog box will show up upon closing this dialog box.  The example dialog is not functional and can be ignored. 

The second field displays the file name of the Excel workbook used by the Excel UnitOp. This is the file that contains the Visual Basic Applications code. CHEMCAD automatically creates this file (as well as the user added dialog box files)  the first time you open the Excel  Unit Op dialog box.  Both files are created in the job directory.  To use a file in a different location, the full path must be specified in this dialog box.

The second page of the dialog box lists the macros that are pre-defined and built into the Excel Unit Op.  Any user added macros that are not listed must be added to this screen, once they are written.  To disable a macro, check the diable box on the right.

The OnEntry macro performs the function of bringing in all data of the streams from CHEMCAD to Excel and the macros.  The ExcelUop macro has skeleton programming code that will fit the need of most users.   The OnExit macro puts all the new or changed data back into CHEMCAD.  The user can customize any of these macros, but will normally customize only ExcelUop. The user can create additional macros.  If additional macros are created, they must be listed in this dialog box in order to be run.  The order in which the macro is listed in this dialog box is the order in which the macro will be executed. 

Click  “OK” to close the Excel Unit Op dialog.

Now define some components for the Excel job.  From the Thermophysical menu select Component list to edit the job’s components. For this job, pentane, heptane and hexane are added to the component list.   Once a component list is defined, give values of components, temperature, and pressure for the two feed streams.  Feed stream one will be at 60 °F and 15 psia.  Feed stream two will be at 90 °F and 10 psia.  The amounts of each component can be seen in the picture below.  All values were chosen arbitrarily for the sake of the example. 

Open Excel and then open the file created by CHEMCAD (here, Excel1.xls).  This file is found in the job folder of the current work directory (default: c:\cc5data). 

Now open the Visual Basic Editor box.  This can be done either by the Tools menu > Macros > Visual Basic Editor on the tool bar, or by pressing [Alt]+[F11].  The code for each of the built in macros can be seen in the Procedures box on the right. Jump to the ExcelUop sub by selecting it with the combo box in the upper right corner of the project view window, as shown. 

Now you are in the correct subroutine to add user code for a unit operation model.   Scroll through the code until you get to the line that states

' [your code goes here, get what you need through the above object handles].

The lines preceding this one are comments added for the user’s benefit which describe available variables.  The lines are listed below for explanation purposes.  These variables were declared and defined in the OnEntry macro.

    ‘   nComp - number of components

    '   nInlets - number of inlets

    '   nOutlets - number of outlets

    '

    '   inletTempR() - temperature of inlets (base 1)

    '   inletPresPsia() - pressure of inlets (base 1)

    '   inletEnthBtu_Hr() - enthalpy of inlets (bass 1)

    '   inletMoleVapFrac() - vapor fraction of inlets (base 1)

    '   inletCompRatesLbmol_Hr(,) - component mole rates of inlets(stream index, base 1)

    '

    '   outletTempR() - temperature of outlets (base 1)

    '   outletPresPsia() - pressure of outlets (base 1)

    '   outletEnthBtu_Hr() - enthalpy of outlets (bass 1)

    '   outletMoleVapFrac() - vapor fraction of inlets (base 1)

    '   outletCompRatesLbmol_Hr(,) - component mole rates of outles(stream index, base 1)