Engineers can make better use of the results of process simulations by automatically exporting data into heat and material balance tables in Microsoft Excel

Chemical 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, m³/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, 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 copy-and-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 be sources of data-transfer errors. VBA programs that automate data transfer eliminate that human error.

One of the greatest benefits of HMB extraction by VBA is that the VBA communication avoids the potential for human errors in data transfer that are common in copy-and-paste techniques. This article presents code examples and basic programming techniques to aid chemical engineers who are familiar with the basics of Hysys or other process simulators.

 

Getting started with Excel VBA

VBA is based on object-oriented programming techniques, a hierarchism of objects, properties and methods. An object is an item of the simulation, such as a Material Stream or Heat Exchanger Operation. Each object has its own properties, such as Temperature for Material Streams and Duty for Heat Exchanger Operations. Each object and its properties also have their own methods, such as GetValue, which returns the value of temperature or heat duty in the user’s specified units.

Let us say the simulation has a material stream (an object) named “100”, which has several properties, such as pressure, mass flowrate, component mass fractions and so on. The engineer may want to extract these properties and populate them in the spreadsheet. Example code utilizing the GetValue command is shown in the box below.

 

---

 

Code example for GetValue  command

The following code presents a simple example of the GetValue command for the given spreadsheet data:
Set HMB_stream = ProjectSimulation.Flowsheet.MaterialStreams.Item(“100”)
Range(“B2”).Value = HMB_stream.Pressure.GetValue(“kPa”)
Range(“B3”).Value = HMB_stream.MassFlow.GetValue(“kg/h”)
Range(“B5”).Value = HMB_stream.ComponentMassFraction(0)
Range(“B6”).Value = HMB_stream.ComponentMassFraction(1)

---

 

 

Tips and tricks

The transfer of stream data from process simulators to Excel via VBA is fast and convenient. However, engineering review of the material balance results is required to catch simulation errors. The engineer should consider that a process simulator like Hysys is not able to calculate stream properties for all conditions. For example, properties that may not be calculated are viscosity in two-phase streams or a property outside of the fluid package’s temperature limits. That stream property would read as “ <empty>” and VBA would not extract the data correctly.

Mistakes in programming VBA code are also common. After all, we are only human. An error as simple as entering the stream temperature values in the spreadsheet row that is set up for pressure values is an easy mistake to make. The process engineer should always review the HMB for these types of errors.

VBA will extract all properties and conditions by the variable unit exactly as defined by the process simulator. For example, the Hysys unit for pounds per hour is “lb/hr,” not “lbs/h” and kilograms per hour is “kg/h,” not “kg/hr”. These must be consistent in order for the code to function correctly.

Not only can VBA extract data from the simulation, but it can also write data to the simulation. This becomes useful if the engineer needs to modify the simulation with data in the spreadsheet and then extract data that results from the user upload. For example, an engineer may need to extract HMB data for a process with different material feed conditions, such as temperature, flow or pressure. The engineer would use the SetValuemethod to change the stream properties. Then the code would extract the simulation data that result from the updated feed properties.

The Developer tab in Excel has a function called “Record Macro.” This is a very useful function that automatically generates code for the user. For example, if a user clicks “Record Macro” and then changes the color of any cell, VBA will automatically generate the necessary code to change the color of a cell. To record a macro, first, set up Excel to show the Developer tab. The user should press the Office button, click on Excel options and then check, “Show Developer tab in the Ribbon” from the Popular page.

The Object Browser (Figure 2) from the VBA Editor View menu is helpful to find the available properties and methods of each object.

Many properties have their own properties and methods. These are easily found by placing a period after the first property, and a drop-down list will appear (Figure 3).

It is important to remember to add references to your Excel file. From the VBA editor, click the Tools menu, click References, and then enable the Hysys Type Library (Figure 4) check box (or whichever library is appropriate for your process simulator of choice).

Another critical step to get started is variable declaration. Declaring the VBA variables for the Hysys HMB file and assigning these variables to the simulation forms the “skeleton” for the code that will be written. Declaring variables and applying these variables are shown in the three code examples below, for the HMB and flowsheet excerpts given in Figures 4a, 4b and 4c.

Variable declaration example — ONLINE EXCLUSIVE

The first step is to declare the VBA variables for the Hysys HMB file using the Dim command, and assign these variables to the simulation.

Dim ProjectSimFile As HYSYS.Application

Dim ProjectSimulation As SimulationCase

Dim HMB_stream As ProcessStream

Set ProjectSimFile = CreateObject(“HYSYS.Application”)

ProjectSimFile.Visible = True

Set ProjectSimulation = ProjectSimFile.ActiveDocument

Set HMB_stream = ProjectSimulation.Flowsheet.MaterialStreams.Item(“100”)

 

GetValue Example — ONLINE EXCLUSIVE

stream_num = Cells(8, 2).Value

set HMB_STREAM = ProjectSimulation.Flowsheet.MaterialStreams.Item(stream_num)

Cells(10, 2).Value = HMB_STREAM.MassFlow.GetValue(“lb/hr”)

Cells(11, 2).Value = HMB_STREAM.Temperature.GetValue(“F”)

Cells(12, 2).Value = HMB_STREAM.Pressure.GetValue(“psia”)

Cells(13, 2).Value = HMB_STREAM.VapourFraction

 

SetValue Example — ONLINE EXCLUSIVE

In this example we consider a process feed with different conditions, temperature, pressure, composition and so on, but with the same process operating parameters. In this scenario, the SetValue function would be used to export data from the HMB to the simulation.

‘ SET FLASH VESSEL FEED CONDITIONS

stream_num = Cells(1, 2).Value

Set HMB_stream = ProjectSimulation.Flowsheet.MaterialStreams.Item(stream_num)

HMB_stream.MassFlow.SetValue Val:=Cells(3, 2).Value, UNIT:=”lb/hr”

HMB_stream.Temperature.SetValue Val:=Cells(4, 2).Value, UNIT:=”F”

HMB_stream.Pressure.SetValue Val:=Cells(5, 2).Value, UNIT:=”psia”

Cells(6, 2).Value = HMB_stream.VapourFraction

‘ IMPORT FLASH VESSEL PRODUCT STREAMS

For i = 3 To 4

stream_num = Cells(1, i).Value

Set HMB_stream = ProjectSimulation.Flowsheet.MaterialStreams.Item(stream_num)

Cells(3, i).Value = HMB_stream.MassFlow.GetValue(“lb/hr”)

Cells(4, i).Value = HMB_stream.Temperature.GetValue(“F”)

Cells(5, i).Value = HMB_stream.Pressure.GetValue(“psia”)

Cells(6, i).Value = HMB_stream.VapourFraction

Next i

 

Crude slate example

In many cases, the owner would like to analyze different operating cases for the same (or similar) process configurations. These cases could differ with regard to feed composition, operating temperature and pressure, flowrate or product quality. Instead of creating different simulation files, VBA could be used with different parameters to set the values within the simulation. Then, once converged, the spreadsheet could get the values from the simulation to populate the HMB.

Consider the example of a petroleum refinery processing different crude slates. Each crude slate assay would have various components, composition and true boiling-point (TBP) data. This could consist of over 100 components once the crude assay is input into Hysys. A simple method would be to use VBA to assign an assay composition to the feed and allow the simulation to converge for its unique material balance. Consider this simple example of three different crude slates, each with its own process requirement of temperature and pressure (Figures 5 and 6).

The user would write the VBA code to select the assay and assign the composition to the unit feedstream. The code would then assign the feed flowrate, temperature and pressure, as well as the temperature and pressure downstream of the heat exchanger. An example of this code is given below.

Crude slate sample code — ONLINE EXCLUSIVE

‘ SET UNIT FEED CONDITIONS

‘ Select Crude Slate

   slate = Range(“C5”).Value

   Set Crude_Oil = ProjectSimulation.Flowsheet.MaterialStreams.Item(slate)

  

‘ Set Unit Feed Stream Composition to Crude Slate

   stream_num = Range(“C7”).Value

   Set HMB_stream = ProjectSimulation.Flowsheet.MaterialStreams.Item(stream_num)

   HMB_stream.ComponentMolarFraction.SetValues _

       Crude_Oil.ComponentMolarFraction.GetValues(“”)

      

‘ Set Unit Feed’s Flow, Temp and Press

   HMB_stream.StdLiqVolFlow.SetValue Val:= Range(“C6″).Value, UNIT:=”barrel/day”

   HMB_stream.Temperature.SetValue Val:=Range(“C10″).Value, UNIT:=”F”

   HMB_stream.Pressure.SetValue Val:=Range(“C11″).Value, UNIT:=”psig”

  

‘ Set Temp and Pressure Downstream of HX

   stream_num = Range(“D8”).Value

   Set HMB_stream = ProjectSimulation.Flowsheet.MaterialStreams.Item(stream_num)

   HMB_stream.Temperature.SetValue Val:=Range(“D10″).Value, UNIT:=”F”

   HMB_stream.Pressure.SetValue Val:=Range(“D11″).Value, UNIT:=”psig”

A real-world, practical example of an application of VBA automation to a process with several different operating configurations is demonstrated by a request from one of the author’s recent clients, a petroleum refinery. The client wanted to analyze eight different cases and determine which configuration would provide the greatest profit versus performance, as well as verify the process against different feedstocks. The engineering team reviewed two different feedstocks, and four different operating conditions for each. That is, several different operating points (pressure at the distillation column, temperature at the outlet of different heat exchangers and column pumparound rates) were adjusted via VBA in a method similar to the examples in this article.

One could imagine the difficulty of developing eight different simulation files or manually adjusting each file for each HMB case. Errors would likely occur and all of the effort to develop the analysis would be wrong. Not only that, this would take much longer to input the data than by automation.

Of course, eight different simulation cases is an extreme example. However, owners many times analyze 2–3 different cases, because, in reality, variables such as feedstocks, temperatures and pressures change. In these real-world scenarios, one can easily see the benefit of using VBA automation with Hysys and other process-simulation software products. ■

Edited by Mary Page Bailey

 

Editor’s note: Portions of this article were not included in the print version; they are denoted “ONLINE EXCLUSIVE”

 

References

1. Aspelund, A., others, An Optimization-Simulation Model for a Simple LNG Process, Computers & Chemical Engineering, 34.10, pp. 1,606–1,617, 2010.

2. Aspen Hysys Customization Guide, March 2011.

3. Bhutani, N., Tarafder, A., Ray, A.K., and Rangaiah, G.P., Multi-Objective Optimization of Industrial Styrene Production Using a Process Simulator and a Genetic Algorithm, AIChE 2004 Annual Meeting, Austin, Tex., Nov. 7–12.

4. Helder Manuel Silva Fevereiro, Universidad do Porto, Departamento de Engenharia Química, Estudos de Simulação Sobre a Reformulação das Unidades 3000 e 1200 da Refinaria do Porto da Petrogal.

5. Microsoft Office Support, www.support.office.com/en-us/article/show-the-developer-tab.

6. Rosen, Edward M., On the Choice of VBA, CACHE News, 56, 2003.

7. Sharma, S., and Rangaiah, G. P., “Chemical Process Retrofitting and Revamping: Techniques and Applications,” pp. 97–127, Jan. 2016.

8. Shende, S., Consider Automation to Check Shell-and-Tube Heat Exchanger Design, Hydrocarbon Process., March 2014.

 

Author

Babak Firoozi is a process engineer working in the Energy and Chemicals division of Fluor Corp. (3 Polaris Way, Aliso Viejo, CA, 92698; Email: [email protected]). Firoozi’s process engineering experience is in the oil-and-gas industry, primarily focusing on process simulation, downstream refining and landfill-gas processes. He earned his B.S.Ch.E. from the University of Baja California, Mexico, and he earned his M.S.Ch.E. from California State University. He also is a registered Professional Engineer in the state of California.