Using COFE in Excel

Excel and automation

Using the COFE automation, one can set up his or her own way of operating COFE inside Microsoft Excel. This requires creating a COFE flowsheet object inside an .xls workbook, and some additional VBA (Microsoft Visual Basic for Applications) programming.

Most common tasks envisioned for running COFE inside Microsoft Excel is the use of thermodynamic functions, and the monitoring of properties of streams and unit operation at flowsheet solution. To facilitate these tasks, an Excel template is provided with COFE that will set up a worksheet for you that will allow you to include a new or existing COFE flowsheet document, and set you up to perform the most common tasks.

The following tasks can be performed with COFE in Excel:

• Create a new COFE-in-Excel document
• Entering functions
• Flowsheet operations

Excel versions

The approach outlined in this help file has been tested under Microsoft Excel 2000, 2003, 2007 and 2010.

Macro security

Since VBA macros are required to operate COFE inside Excel in a useful manner, these macros will need to be stored somewhere. Often, software venders place a locked-down Excel add-in with the required macros in a location on your computer that is considered safe. In such a case, Excel will can allow for automatically enabling the macros in such an add-in. Usually, the user is not allowed to change the add-in, and all programmed functionality remains as is when the application was distributed.

For COFE, a different approach was used. Each COFE document contains its own macros. The user can simply access these macros by starting the VBA editor inside Excel (Macros from the Tools menu, select Visual Basic Editor). Since CAPE-OPEN allows for a vast amount of different operations, it is considered important and useful for the end-user to be able to not only access the macros that operate on CAPE-OPEN objects, but also to modify them and to add to them to suit the user's specific needs.

This results in each COFE-in-Excel xls document being self-consistent and ready to ship. The down side is that changing the macros in a single document will not change the macros in all of your documents.

Another down side is macro security. For the macros to run properly in Excel 2003 and earlier, they will have to be enabled. Excel allows the following levels of macro security:

• Very high: macros are only allowed to run from trusted locations and trusted sources. This setting will make that the COFE macros will not run;
• High: macros are only allowed to run from trusted locations and trusted sources. Trusted sources are signed with a digital signature. COFE-in-Excel documents are not, and therefore this setting will make that the COFE macros will not run;
• Intermediate: the user can specify with opening a document whether the macros should be enabled or disabled for the selected document. The macros should only be enabled for documents that are from a trusted source;
• Low: all macros are automatically enabled. This setting is not recommended.

It is recommended to use the intermediate macro security level (select Options from the Tools menu in Excel, go to the Security tab, click the Macro Security button), and click enable on the macro security document ONLY when the document is from a location that you can trust.

For Excel 2007 and later, the Excel document should be stored in a trusted location. Open the Trust Center for more information (Excel 2007: click the Office button and click the Excel Options button; Excel 2010: select Options from the File ribbon).

COFE menu

A few functions in Excel files embedding a COFE document are available via the COFE menu; in Excel 2007 or later, this menu is available from the Add-in ribbon.