Pro Tip: Searchable Excel Stream Tables with VBA
Note: This article was originally published in AIChE's ChEnected blog.
The astute process engineer loves building stream tables in Excel. They find nothing so satisfying. Their enthusiasm is squandered, though, as they start using the data in further calculations. Hunting and pecking for the correct cell reference is slow, prone to error, and, in several other ways, uncivilized. It is for times like these that Microsoft gave us VBA.
Stream tables in Excel
One of the first steps in process and economic modeling in Excel is to build a process stream table based on user input. The stream table neatly aggregates material and energy balance data for use in further calculations, for example, in estimating process metrics, equipment sizing parameters, capital costs, or operating costs.
Formulas referencing this data are predisposed toward certain difficulties though. The data-dense stream-table format puts numerous values in adjacent cells, and the calculations using them can be spread across multiple sheets. These factors make formulas slow to write, interpret, and trace. They also invite bugs to creep in and remain undiscovered.
A better way
To reference a piece of data in a stream table, you need to specify the stream number and the property name. As an Excel function, it might look like this:
This user-defined function (UDF) is actually simple to implement. It requires just eleven lines of VBA code, three named ranges, and a little formatting discipline. In return, you get a fast and accurate method for referencing stream data. Furthermore, it is clear to any user what value the formula is referencing.
The spreadsheet setup
Within your stream table, you will need to create three named ranges as shown below: StreamNumbers, StreamProperties, and StreamData. Each property label in the named range ‘Properties’ should be fully descriptive of that property. For example, use “Mole flow H2”, instead of just “O2” or “O2flow”.
The VBA code
In the June 2017 issue, Chemical Engineering Progress published an excellent article by Professor David Clough on using VBA for process engineering applications. I will point you towards that, or any of the plentiful online resources, for VBA basics. In summary, open the VBA editor, create a new module, and insert the code below. Note that the green text denotes comments (explanatory text unnecessary for the functioning of the code).
Using the function
With the VBA code and named ranges in place, you should now be able to use the StreamTable() function in spreadsheet formulas. Note that text arguments need to be entered in quotes, like ‘Mole flow’ is in the formula:
= StreamTable(103, “Mole flow”)
Or, for more versatile spreadsheets, use cell references for your arguments:
A note on trade-offs
Excel wizards will pick up on a couple of caveats. First, is that the StreamTable() function can be replicated using a single native spreadsheet function, as shown below. This has the benefit of not requiring VBA, but the formula loses its clarity.
= INDEX(StreamData, MATCH("Pressure", StreamProperties, 0), MATCH(110, StreamNumbers, 0))
Second, calculation time for formulas using StreamTable() will be slower than for formulas using direct cell references. This should be insignificant under normal circumstances, but it will be noticeable if you are implementing the function hundreds of times, or if you are performing iterative sensitivity analyses like Monte Carlo.
Complex spreadsheet formulas may look impressive, but the skillful spreadsheet developer strives for clarity. Clear and simple formulas are faster to write, easier to understand, and less susceptible to errors. Simple UDFs, like StreamTable(), can go a long way toward this end.