Entering functions in Excel

VBA functions

The functions that are implemented in VBA to access COFE data from within Excel worksheets are a good starting point, but do not represent the full extent of what can be done. While the functions will provide a good starting point for the beginning Excel user without VBA knowledge, the experienced user is encouraged to modify and add to the functions implemented in VBA.

All COFE related functions in VBA start with COFE_. Most of the functions can be seen in action from the example pages that are available when starting a new document.

Available functions

Entering functions and arguments

To facilitate entering COFE functions in Excel, start with typing - at the desired location - an equal sign followed by the function name, followed by an opening brace. For example: =COFE_ObtainProperty(Then, use the Excel formula wizard to help you along; hit the formula wizard on the left of the formula bar:

The formula wizard will show each of the function arguments and allow you to enter data for it:

If you do not know the function name, you can click on the formula wizard when the formula bar is empty. Select User defined for the function category. You can then pick the COFE_ function from the list.

Entering array formulas

Many of the functions return values that are arrays. If you enter such a function in a single cell, you will only see the first element of the resulting array. Instead, you will need to enter an array formula in Excel. To do this, follow the following steps:

Select the cells in which you want the answer to appear (a range of multiple cells),
Enter the formula, and its arguments, as outlined above,
Press Ctrl+Shift+Enter when you are done entering the formula. The Ctrl key ensures in Excel that the function is entered for the entire selection, as an array formula.

Most functions return arrays that are formatted as columns. Some return row functions. This is listed with each function. You can use the Excel function Transpose to convert row arrays to column arrays and vice versa.

If the range you have selected is shorter than the array that is returned from the function, only part of the array results will appear. If the range is longer than the returned array, Excel by default repeats the elements that appear at the start of the array. To circumvent this behaviour, the COFE_ functions that return an array are generally fit with an additional argument growArray, which, if specified, will make that the returned array is at least the specified amount of items long and the unused elements will be set to an empty string. This is useful the function results depend on the flowsheet configuration. For example, if you use the =COFE_GetCompoundNames() function, the resulting array has the size of the amount of compounds in the document. If you set up a spread sheet to work with different thermo models, the amount of compounds may vary. You can assign the result of that function to an array that is large enough to hold all desired cases (for example, 100 elements). To prevent the first elements from repeating over and over, you can specify a value of 100 for the growArray argument, e.g. =COFE_GetCompoundNames(,100).