Spreadsheet interface

Jump to: navigation, search
This article is about planned development or proposed functionality. Comments welcome.

Proposal: create a spreadsheet-like GUI for ASCEND.

This is not the same as integrating ASCEND with existing Spreadsheets. The idea would be to allow models to be created on a free-form grid of some sort, similar to Excel, but really a whole new program using the existing ASCEND back-end to do the calculations.


People are adept at creating models using spreadsheets. It seems to be a natural way to program. I suspect the reasons are twofold:

  • People are spatially oriented. They remember where they put something much more easily than they remember what they called it.
  • With every new element in a spreadsheet, they see a new result and can quickly decide if it is correct. Programming is incremental.


  • Spreadsheets do not handle arrays well, especially when a model has an array whose size is dictated by a parameter passed from the "outer" model that has it as a part of it.
  • Spreadsheets lead to strange 'labels' like 'A5' or 'C24' to refer to cell names; using a language-based model definition, the user is deciding what these labels should be and can give them more memorable names.


  • Perhaps this type of GUI will be suitable only for the construction of one-off models, done quickly, as people often do when programming in Excel. I would like it to be useful for larger models, however, so I do not want it being useful only for small models to be the only outcome.
  • Can we imagine using Excel itself as a basis for such a GUI? Or do we have to write our own spreadsheet from scratch? (Twenty years ago there were spreadsheet packages available that one could adopt and modify. Do these still exist?)

A thought experiment

In ASCEND we create type definitions. Is this something that we can envision doing using a spreadsheet front end? Could we create an instance of a model that we could convert to a parameterized type definition, for example? In an attempt to recover thoughts that we had on this type of front end from over 20 years ago, I will play with creating a model using a spreadsheet like approach. Let's build a model constructed of parts whose type we previously define.

Example model building

Let’s create a model of a flash unit. I am used to the equations for such a model, it contains parts, and it uses arrays of arbitrary size in its type definitions. We will use the simpleflowsheet01.a4c code as a basis for this modeling.


We first must define a mixture. If we were in Excel, we could create an example mixture model by creating a column of y values, one for each component. We would then form their sum by adding up the column. Now,

  • can we convert that to a type definition?
  • How do we tell Excel that each of the cells corresponding to a y value is of type fraction? Further,
  • how do we tell Excel that we want one such cell for each member of a set we call “components?” And
  • how do we put the names into “components?”

Note that we would be unable to enforce that the sum of the mole fractions add up to one in Excel without using the “goal seeker” or the “solver” under the tools menu. This, of course, is not how we would envision ASCEND to work. It should be solver for the spreadsheet. We must encode the equations we want enforced in the model. We could do this by putting errors into cells and noting that we want them to be zero at a solution. Other cells could simply be there as intermediate computations. Having said this, how would ASCEND extract equations from a spreadsheet?

Thinking out loud here, let’s see what might be an approach. We should really be trying to discover the wealth of options, but we will start with trying to discover just one that could work.

How do we create an array in a model? One possibility would be to select a cell and declare its type to be an array. That would open a new sheet that will contain the array. We need three items (at least) for an array (let’s assume an array is one dimensional so a matrix will be an array of arrays). We need an index set over which the array exists, the base type for the array elements, and the array elements, one for each member of the index set. When the sheet for an array opens, we see these three parts. (Question: should it be a new sheet or should it open as a block inside the current model?) We select the base-type cell, and a list appears that gives us legal base-types. We select one, and its name fills into the base-type cell. Until we give values to the index set, we do not know how many array elements we need, so it appears these should be represented by an icon until we select the index set. We click on the index set and fill in some values - [1..10] or [‘a’,’b’,’c’], for example. As we create these values, ASCEND dynamically creates the cells for the array. We could also fill in the index set by searching for it elsewhere in the parent models containing this array. Or we could leave it unset for the moment – with the cell for the array in the parent model showing in red as it is not yet fully instanced.

We start at the lowest level and create an atom definition for “fraction.” We do this by opening a new sheet based on the template “atom.” The sheet opens with labeled cells in it for the various parts of an atom. We put values into the lower and upper bounds of 0.0 and 1.0 respectively. There are flags in this sheet (e.g., a fixed flag). We fill in the dimensions for this atom (where? how?).

We are not dealing yet with a lot of issues, but the idea that we could program in this style of interface should be apparent. And, it seems it is in the style of programming in a spreadsheet.

Okay, we have some rudimentary thoughts on the building blocks. We now create the mixture model. We open a new sheet and call it “mixture.” We pick a cell and declare it to be an index set. If we click on it, a template for a set opens which we fill in with element lists, should we want to do that now. We fill in ‘a’, ‘b’, ‘c’, etc.

We pick another cell and select it to be an array with the name ‘y.’ Opening the array sheet, we set the base type to be “fraction.” We select the index set, indicate this set is equal to another, and search through the parent model to find the index set. The next time we open this array within this mixture model, we will see three elements – one for each of the species ‘a’, ‘b’, and ‘c’.

We select a cell in the mixture model and construct a term that is the sum of the elements in the array y less unity. We flag this cell as an equation.

We need yet to think about how to set up methods. We leave that for the time being.


We next create the model “molar_stream.” New to this model is the FOR/CREATE loop. We also have an included part called a state that is a “mixture.” Some of the other bits are similar to elements in the mixture model.

We pick a cell and label it “state.” We click on it, select “IS_A” as the type cell, and a sheet opens. The sheet has a spot for us to select its type: mixture. When we do, ASCEND fills in what it knows about a mixture from our previous instance. We select “components” at the parent level, select “ARE_THE_SAME,” and search for and equate it to “components” inside the sheet we just called “state.”

How might we construct a FOR/WHILE loop? We want a one-to-one mapping with this statement. We select a cell and declare it to be a FOR/CREATE loop. At this level – i.e., inside this sheet rather than in a new sheet - ASCEND inserts a template of cells (with a box around them, perhaps) that corresponds to this loop. It has an index variable (we label it “i”) along with an index set from which it is to be drawn – i.e., “components.” We tie this index set to the set “components” we created earlier in this sheet. We pick another cell, indicate it is an equation (obviously it will be indexed), and construct the equation. How do we select f[i]? We need not only to pick f but also the general index calculation over which f is indexed, here simply “i”. But this index expression could be of the form 2*i-1. We pick f first. ASCEND sees it is an array of variables. It opens another cell associated with this pick of f into which we construct the index calculation. We select the minus operator, select Ftot (not indexed so no subindex calculation), and then y inside state (indexed and we equate this index with that for f or we construct the index expression over again) that ASCEND provides space for as we construct this equation.

The above FOR/WHILE loop construction makes it clear that programming is by location and not by name – as when we program a spreadsheet. Nice. It also makes it clear that we need to develop an approach for each type of statement we have in ASCEND.

It should be fun

Since we already know the statement types, this design activity should be fun and not too difficult. Programming the support interface will be the hard part?? At least it would be for me.

-- Art Westerberg

Another thought experiment

Here's another take on how this stuff could work. The key idea is that this special new spreadsheet would allow relations to be entered into cells, such as "C5 == B4". Then, cells like that would provide a visual clue about the convergence status of the relations they contain.

Cells with fixed values would have to be tagged in some way. Same as the FIX/FREE functionality we use at the moment.

Arrays would be handled as sequences of cells. FOR loops would be done using relative indexing, the same as for standard spreadsheets. Stuff like SWITCH statements could be handled using something similar to the current 'IF' function of spreadsheets. This might require some changes to the way that conditionals and handled in libascend.

Models could be declared by drawing a box around a number of cells and declaring a name for the component. Parameters would need to be defined by tagging the necessary cells. Then, this set of cells could be called in the same way as an equation, eg "mymodel(A4, B5)". When the submodel was entirely converged, it might show the cell in green. If not, the cell could be double-clicked and an instance of the originally declared model would open, showing all the internal variables.

In additional to all this, there would be a need to specify the REQUIREd outside sheets, for model reuse. This could be done using something similar to the Excel add-in loader interface; it would be a list of files loaded to support the current sheet.

Documentation comments could easily be added throughout the sheet, perhaps with a simple ' prefix in such cells.

Units of measurement could be handled as a suffix entered into cells. Cells could have their units of measurement changed, and values would be automatically converted. A (fixed) cell would simply remember the units entered by the user.

Minimum and maximum values for cells would be configurable by right-clicking. Variable types would not be present in this system, but dimensional checking would still be possible and could be helpful in alerting users to input errors in realtime.

With the above approach, it would be fairly simple to build up complex object-oriented conditional models, including model re-use, in a free-form way not currently possible in ASCEND. IT wouldn't be hard to set up the spreadsheet GUI to export the necessary variables and equations to the solver.

Dynamic modelling might even be possible in this scenario, although how dynamic modelling data could be represented to the user would be more difficult to work out.

I think that a METHODS system could be worked out, using a Javascript-style dynamic object model. A scripting layer, similar to Visual Basic in Excel, would be allowed to augment MODEL definitions with methods, and these methods would be able to access and set internal variables of the models. Then, these methods would be callable from other places in the sheet. A special method could be run whenever a model sheet was instantiated and/or a file loaded.

-- John Pye