An Excel add-in with User-Defined-Functions (VBA) to calculate the friction pressure loss (head loss) in circular pipes with full flow water. The pressure loss calculator function has the selective options for the friction loss formulations, can be selected as either "Hazen-Williams" or as "Darcy-Weisbach". For the latter, another selective options are possible for the equation calculating of the Darcy-Weisbach friction factor (details given in Features). Besides, another functions are given to convert between the roughness factors/coefficients, made use of in the Darcy-Weisbach and the Hazen-Williams formulations. The Matlab calculator tools of the same Excel functions can be found in pressure_loss_calculator-Matlab @GitHub.
- The VBA user-defined functions are available as packed in the Excel add-in ExcelAdd-In_PressureLoss.xlam but also available on an individual basis for each function in the GitHub folder Modules-UDFs), the details for the functions described below:
- The function for the calculation of the pressure loss has the options in selecting the solver type through the equations either by 'Darcy-Weisbach' or by 'Hazen-Williams'.
- Besides, another feature in the tool options allows users to select through various algorithms to calculate the Darcy-Weisbach friction coefficient f, limited to algorithms by 'Moody', 'Colebrook-White', 'Clamond', 'Swamee-Jain', 'Zigrang-Sylvester', and 'Haaland'.
- Aside from the pressure loss calculation function, two other converter tools are also given to obtain the Hazen-Williams roughness coefficient C as a non-steady variable by a function of (i) the absolute roughness of the pipe (also known as ε - eps) and (ii) the Darcy-Weisbach friction factor f.
- The limitations for use of equations and algorithms are given in the code (e.g. the operational limitations in using Hazen-Williams).
A stand-alone example Excel file is given in examplePressureLoss&RoughnessConverters.xlsm to illustrate the use of the user-defined-functions in question. WARNING: please do not load the developed Excel add-in ExcelAdd-In_PressureLoss.xlam in this stand-alone example Excel file because it already involves of the functions packed in this Excel add-in. Otherwise you will have two of the same functions and modules in your VBA library, one from the example Excel file and the other from the Excel add-in!!!
There are four different worksheets in this example Excel file examplePressureLoss&RoughnessConverters.xlsm, each hosts to illustration of usage for different functions, details given below. It should be noted that each Excel WorkSheet has the layout of INPUTS on the left-hand side and the USAGE EXAMPLE on the right-hand side, latter involves of the calculator functions in use (Please check the ScreenShot). Another note is that this Excel file follows a consistent formatting by use of cell styles (e.g. calculation results in calculation style, the results in output style etc.).
-
WorkSheet "PressureLossCalculator": This first Excel WorkSheet PressureLossCalculator shows the usage of the pressure loss calculator function PressureLossCalculator.bas as PressureLoss(L, D, aRou, mFlow, T, P, Solver, Algorithm, fTol, MaxIter), respectively, the input arguments being the length of the pipe segment, the water mass flow rate, the water temperature, the hydrostatical water pressure, the solver as optional (the default is as "Darcy-Weisbach"), the algorithm as optional (the default is as "Clamond"), and iteration inputs as optional (valid only for the algorithm "Colebrook-White") fTol as the iteration tolerance and MaxIter as the maximum amount of steps for the iteration. For example, the cell "G5" host the pressure loss calculation as based on the "Darcy-Weisbach" solver with "Clamond" algorithm and the cell "G14" as based on the "Hazen-Williams" solver (no friction factor algorithm this time - please trace the precendents from the INPUTS section).
-
WorkSheet "DarcyFrictionAlgorithms" : This Excel WorkSheet illustrates the usage of the Darcy-Weisbach friction factor algorithms. For example, the cell "F5" has the return from the f_ColebrookWhite function (without iteration input required in do-while loop), the cell "F6" has the return from the f_Moody function etc.
-
WorkSheet "Converter_f2C" : This Excel WorkSheet illustrates the usage of the converter function tConverterDW2HW that is developed to convert the Darcy-Weisbach friction factor f to Hazen-Williams roughness coefficient C and the vice versa conversion from C to f. The two examples are that i) "H3" cell is the return as f as converted from the C given in "F3" and ii) "H6" cell is the return as C as converted from the f given in "F6"
-
WorkSheet "Converter_Roughness" : This Excel WorkSheet illustrates the usage of the converter function tConverterRoughness that is developed to convert the relative roughness (absolute pipe roughness / pipe diameter - eps/D or rRou) to Hazen-Williams roughness coefficient C and vice versa. The two examples are that i) "H3" cell is the return as C as converted from the rRou given in "F3" and ii) "H7" cell is the return as rRou as converted from the C given in "F7"
-
Other WorkSheets : The other Excel WorkSheets "zPipeCatalogue" and "zDiskinData" are not an illustration of usage but a required data for this example Excel file examplePressureLoss&RoughnessConverters.xlsm. The zDiskinData is necessary for the user-defined-functions developed (given in this repository), i.e. especially at functions tConverterRoughness and tReynoldsLimits.
The Excel add-in ExcelAdd-In_PressureLoss.xlam allows using of the developed user-defined-functions in any Excel file that your calculations take part. How to install an Excel add-in is well described in the tutorial Acompara J - How to Install an Excel Add-In - Guide @ExcelCampus.com.
All of the modules developed within this repository are given in the folder Modules-UDFs. Generally, each of these modules hosts to a unique Excel functions developed, as shown in the table at section List of Functions. Here the idea is to present an overview of the Excel functions. Besides, one can use some of the Excel functions developed partially if other functions are not required by simply importing the bas file/s or copy&pasting them on the Visiual Basic Editor.
Please note that, after copy&paste, you have to delete the first codeline in the .bas file. For example, if you need only of the converter function, after copy&paste, you have to delete the line Attribute VB_Name = "Converter_f2C" in the Converter_f2C.bas or (another example) if you need only of the Clamond algorithm you have the delete the Attribute VB_Name = "DWf_Clamond" from the code lines of dwf_Clamond.bas. Please check the Screenshot of How2Do.
Here is the list of functions developed and in use (latter original works by other Developers):
| Function | Description | Module |
|---|---|---|
| PressureLossCalculator (L, D, aRou, mFlow, T, P, Solver, Algorithm, fTol, MaxIter) | The main function calculating the pressure loss | PressureLossCalculator.bas |
| tConverterDW2HW (f_or_C, D, Re, T, P) | The converter tool among the Darcy friction factor f and the Hazen-Williams roughness coefficient C | Converter_f2C.bas |
| tConverterRoughness (rRou_or_C, ConverDir) | The converter tool among the relative roughness rRou and the Hazen-Williams roughness coefficient C | Converter_rRou2C.bas |
| tReynoldsLimits (rRou_or_C, InputType) | Returns the limitations for the Reynolds range applicable for a given relative roughness or C value | tHWLimitsReynolds.bas |
| f_ColebrookWhite (D, Re, rRou, fTol, MaxIter) | returns the Darcy-Weisbach friction factor by solving iteratively the Colebrook-White equation | dwf_ColebrookWhite.bas |
| f_Clamond (Re, rRou) | Function returning the Darcy-Weisbach friction factor by use of the Clamond algorithm | dwf_Clamond.bas |
| f_Moody (D, Re, aRou) | Function returning the Darcy-Weisbach friction factor by use of the Moody algorithm | dwf_Moody.bas |
| f_Haaland (D, Re, aRou) | Function returning the Darcy-Weisbach friction factor by use of the Haaland algorithm | dwf_Haaland.bas |
| f_SwameeJain (D, Re, aRou) | Function returning the Darcy-Weisbach friction factor by use of the Swamee & Jain algorithm | dwf_SwameeJain.bas |
| f_ZigrangSylvester (D, Re, aRou) | Function returning the Darcy-Weisbach friction factor by use of the Zigrang & Sylvester algorithm | dwf_ZigrangSylvester.bas |
| Reynolds (mFlow, D, T) | Function returning the Reynolds number as a function of the water mass flow rate | tReynolds.bas |
| LogBase (x, base) | User-Defined VBA function returning the logarithm of a given number x at a given base of base | zOtherTools.bas |
| PiNumber () | User-Defined VBA function returning the Pi number at the highest precision | zOtherTools.bas |
| Linterp (KnownYs, KnownXs, NewX) | Linear interpolation function - developed by Wells, Ryan | zInterp_Wells.bas |
| XSteam Module | A collection of functions returning the water properties at a given operational condition (e.g. rhoL_T(T) returns the water density as a function of temperature T) - developed by Holmgren, Magnus - Please note that for ease of use while calling the XSteam functions in pressure loss calculator functions developed, the XSteam module is integrated to our modules in the VBA library instead of usage of XSteam.xla add-in. | zXSteam.bas |
You are free to use, modify and distribute the code as long as authorship is properly acknowledged. The same applies for the original works 'XSteam' by Holmgren M. and 'colebrook.vb' by Clamond D, this repository functions make use of.
We would like to acknowledge all of the open-source minds in general for their willing of share (as apps or comments/answers in forums), which has encouraged our department to publish our user-defined-functions here in GitHub.
This repository pressure_loss_calculator-Excel makes use of other original open-source projects:
- XSteam by Holmgren M. | Author Description: XSteam provides accurate steam and water properties from 0 - 1000 bar and from 0 - 2000 deg C according to the standard IAPWS IF-97. For accuracy of the functions in different regions see IF-97 (www.iapws.org).
- colebrook.m by Clamond D. - re-arranged as to the VBA programming language | Author Description: fast, accurate and robust computation of the Darcy-Weisbach friction factor f according to the Colebrook equation.
- Linterp.vb by Wells R. | Author Description: A simple and powerful Excel linear interpolation function.
The Excel Add-In (the user-defined VBA functions developed) as well as the Matlab tool of the same (released in pressure_loss_calculator-Matlab.git) are by-products from the PhD study about the 4th generation (4G) low-temperature district heating systems in supply to low-energy houses, carried out by Hakan İbrahim Tol, PhD under the supervision of Prof. Dr. Svend Svendsen and Ass. Prof. Susanne Balslev Nielsen at the Technical University of Denmark (DTU). The PhD topic: "District heating in areas with low energy houses - Detailed analysis of district heating systems based on low temperature operation and use of renewable energy" - free download by DTU (link) or by ResearchGate (link).
- Tol, Hİ. pressure_loss_calculator-Excel. DOI: 10.5281/zenodo.1215618. GitHub Repository 2018; https://github.com/DrTol/pressure_loss_calculator-Excel/
- Tol, Hİ. District heating in areas with low energy houses - Detailed analysis of district heating systems based on low temperature operation and use of renewable energy. PhD Supervisors: Svendsen S. & Nielsen SB. Technical University of Denmark 2015; 204 p. ISBN: 9788778773685.
- Sanks RL. Flow in conduits. In: Sanks RL, Tchobanoglous G, Bosserman BE, Jones GM, editors. Pumping station design. Boston, USA: Butterworth-Heinemann 1998: p. 33-39.
- Clamond D. Efficient resolution of the colebrook equation. Industrial & Engineering Chemistry Research 2009; 48: p. 3665-3671.
- Clamond D. colebrook.m - Efficient resolution of the Colebrook-White equation (v1.0). MathWorks File Exchange: https://nl.mathworks.com/matlabcentral/fileexchange/21990-colebrook-m?focused=5105324&tab=function
- Diskin MH. The limits of applicability of the Hazen-Williams formula. La Houille Blanche 1960; 6: p. 720-726.
- Liou CP. Limitations and proper use of the Hazen-Williams equation. Journal of Hydraulic Engineering 1998; 124(9): p. 951-954.
- Colebrook CF & White CM. Experiments with fluid friction in roughened pipes. Proceedings of the Royal Society A - Mathematical, Physical & Engineering Sciences 1937: p. 367-381.
- Niazkar M, Talebbeydokhti N & Afzali SH. Relationship between Hazen-William coefficient and Colebrook-White friction factor: Application in water network analysis. European Water 2017; 58: p. 513-520.
- Asker M, Turgut OE & Coban MT. A review of non iterative friction factor correlations for the calculation of pressure drop in pipes. Bitlis Eren Univ J Sci & Technol 2014; 4(1): 8 p.
- Genić S, Arandjelović I, Kolendić P, Jarić M, Budimir N & Genić M. A Review of explicit approximations of Colebrook’s equation. FME Transactions 2011; 39: p. 67-71.
- Holmgren M. X Steam, Thermodynamic properties of water and steam (v1.0). MathWorks File Exchange: https://nl.mathworks.com/matlabcentral/fileexchange/9817-x-steam--thermodynamic-properties-of-water-and-steam