Chemical Engineering October 2016 - 75

Engineering Practice
Using Excel VBA for Process-Simulator
Data Extraction
Engineers can make better use of the results of process simulations by automatically
exporting data into heat and material balance tables in Microsoft Excel
Aspen Technology
Babak Firoozi
Fluor Corp.
C
hemical engineering projects
require process
simulation to validate the
chemical
reactions
and
mechanical operations of the process
(Figure 1). Each project then
requires a heat and material balance
(HMB) report based on the
simulation results as a basic step of
the process engineering. The HMB
should contain thermodynamic and
other physical property data, such
as chemical composition, specific
heat, density, viscosity and so on.
Additionally, some HMB documents
may contain the heat duty required
by the process. These data, along
with the material flow, become the
basis for equipment sizing, line sizing,
utility demands, chemical and
catalyst provisions, as well as other
requirements to complete the process
design of the project.
Each project requires its own
HMB format based on the feedstock,
product, fluid phase or (perhaps
most importantly) client preference.
Some HMB formats prefer to
separate the fluid's vapor and liquid
flows and properties. Some projects
require British units (such as
lb/h, psig, gal/min, and so on), while
others prefer SI units (such as kg/h,
barg, m3/h and so on). Commercial
process simulators do not directly
output the data in a custom format,
making it potentially very labor intensive
to obtain a properly formatted
HMB. In fact, the data reports
from simulators may sometimes be
difficult to analyze because of the
excessive information provided by
the simulation.
This necessitates some extra effort
from engineers, but with the
guidelines presented in this article,
66
FIGURE 1. Process simulation software helps capture a full picture of a plant's performance and can enable
engineers to access large amounts of design data for projects
formatting HMB data can become
much less painful. The examples in
this article relate directly to Hysys, a
frequently used process simulator,
but similar programming principles
could be applied to other simulation
software products. Many commercially
available process simulators
communicate with the Visual
Basic for Applications (VBA) portion
of the spreadsheet program Microsoft
Excel. A VBA macro (a series of
procedures " recorded " for automatic
execution) can be programmed to
extract HMB data from the different
streams in a simulation and then
report that HMB data in an Excel
spreadsheet that is customized for
the project format.
Spreadsheets are used for project
HMBs because of the flexibility of
presentation. One method of populating
a spreadsheet-based HMB
requires a copy-and-paste operation
from the simulation to the HMB file.
This could create problems if material
streams, or rows or columns,
are added or deleted in the HMB.
A stream from the HMB page could
point to the incorrect stream on the
pasted page after an insertion or
deletion, which could create errors
in the equipment sizing, potentially
delay the project and ultimately affect
the project cost estimate. In
the worst-case scenario, if the error
is not discovered, the actual equipment
could be sized incorrectly and
the process would not work.
A better option is to write code
using VBA in Microsoft Excel to extract
the data from the simulation
and then populate the spreadsheet.
Microsoft Excel's VBA programming
module can be used to automate
many processes, such as
the creation of a table, which otherwise
might require several copyand-paste
operations. Actions that
could be automated are typically the
source of human error. Accidental
copying in the wrong cell, highlighting
the wrong cells and deleting or
inserting rows and columns all can
CHEMICAL ENGINEERING WWW.CHEMENGONLINE.COM
OCTOBER 2016

http://WWW.CHEMENGONLINE.COM

Chemical Engineering October 2016

Table of Contents for the Digital Edition of Chemical Engineering October 2016