Stephen Hall continues his series on how to use Excel for project engineering. Download the interactive workbook to experiment with as the series develops
Most engineering calculations require material properties such as density and viscosity. The commercial flowsheet simulation packages are underpinned by extensive physical property libraries, vapour-liquid equilibrium data, and property prediction engines. Reliable computations, especially those that involve phase equilibria, are critically dependent on accurate properties and the selection of the best Equation of State. But the everyday problems that I encounter do not need this level of sophistication. I am very happy with using simple correlations that map density, viscosity, thermal conductivity, specific heat, and vapour pressure against temperature. Those properties are enough to solve heat and material balances and to perform hydraulic analyses.
When I create a model that solves problems for any user-defined fluid, I attach a worksheet that contains the coefficient values for physical property equations for a set of fluids. This month I am describing that worksheet and showing how to use it. As a bonus, I am including a set of VBA Functions that give thermodynamic properties for water and steam according to IAPWS-97 (The International Association for the Properties of Water and Steam).
For most models the user will enter the name of the fluid, its temperature, and, for gases, its pressure. Other models determine the temperature and pressure, in which case the user will only need to enter the fluid name. Unless you incorporate a databank with the fluid properties, the user will need to enter the properties as well, but with this month’s worksheet that will not be necessary. Download the companion workbook from https://bit.ly/ModellingWithExcel and follow along with the narrative in this article.
The worksheet includes a table with fluids and their properties. You can ensure that the fluid name that the user enters is contained in the databank by giving the column of fluids a name such as “lFluids”. As discussed last month, I use the first letter of named ranges to indicate what type of range it is: “l” for list, “h” for column header, “t” for table, “inp” for user input. Armed with the named list of fluids, use Data Validation (List; =lFluids) on the input cell(s) to present a dropdown list of fluids for the user to pick from.
In its simplest form, the fluids with their static properties and correlation parameters are listed in a table, named “tFluids” for this example. If the table dimensions are fixed such that the parameters will not move (to a different column in the worksheet) then you can access the parameters and compute the temperature-dependent property using the VLOOKUP function. For a property that is correlated to a linear equation of form y=mx+b, the formula would look something like: “=VLOOKUP(inpFluid,tFluids,2,FALSE)*inpTemperature+VLOOKUP(inpFluid,tFluids,3,FALSE)”, where the correlation parameters “m” and “b” are in columns 2 and 3 of the data table, respectively. Note the fourth argument, “FALSE”, which returns data only when inpFluid exactly matches an entry in the data table. If you omit that argument, the VLOOKUP will return a value for the first entry that is alphabetically adjacent to inpFluid if that fluid is missing from the table and therefore will give an incorrect answer.
VBA user-defined functions (UDFs) provide a much better user experience than the VLOOKUP method. The worksheet entry becomes: “=dens(inpFluid,inpTemperature)” when using the UDF in Listing 1. The data are still in the tFluids table, but with named column headings it no longer matters if the columns are rearranged or if new columns are inserted in the table. Line 10 in the listing finds the row in the table for inpFluid. Lines 20 and 30 retrieve the m and b parameters from the table, and Line 40 computes the result.
The companion workbook contains UDFs for the properties listed in Table 1. The worksheet already includes correlated properties for more than 100 substances. It also includes features for managing the content of the data table. Due to space limitations the VBA code is not included here; you will need to open VBA and examine the code in the workbook to discover all of the details for the features that are described.
The UDFs offer many benefits, including the following:
You need to be logged in to read the rest of this articleLogin Create an account
Catch up on the latest news, views and jobs from The Chemical Engineer. Below are the four latest issues. View a wider selection of the archive from within the Magazine section of this site.