In Chapter 8 we covered VBA functions. Sometimes it is easier to put a formula in the Worksheet, rather than write a formula in a program. Below is a list of built-in Math Functions for the worksheet. For complete examples and descriptions go to http://www.techonthenet.com/excel/formulas/index_ws.php
Type in the following labels as seen on the next page: deltaP=, friction=, length=, diameter=, density=, velocity=, deltaP=, deltaP=, deltaP=
Type the following pressure drop formula into B10.
=1.294E-3 * .021 * 4000/2 * 62.4 * 2.98^2
Press Enter and the result is deltaP = 30.116236.
This is the equivalent of using a calculator to get the result.
We will use the same formula, but input the values from cells in the worksheet. We will input values for friction, length, diameter, density, and velocity in cells B12:B16 where:
B12 = .021
B13 = 4000
B14 = 2
B15 = 62.4
B16 = 2.98
Type the following formula in B17 and press Enter.
= 1.294E-3 * B12 * B13 / B14 *B15 * B16^2
DeltaP = 30.11624. The same result. The advantages of the spreadsheet calculation is you can re-enter values quickly to get the result. You also are saving your calculation and result to the spreadsheet. The disadvantage of this method is that using cells as references, it is difficult to keep track of what values are in the formula. Named cells fix that problem.
Naming cells makes your formulas easy to read. We will use the same formula again, but with named cells. Use the names below.
friction
length (ft)
diameter (inches)
density (lbs/cuft)
velocity (ft/s)
We will now name the cells B12:B16. Place your cursor in cell B12 to make it the active cell. Click in the Name Box to highlight the text B12, and type in the word friction in the Name Box. You must then press enter for the name to become active. Cell B12 is now named friction. Repeat the naming for B13 as length, B14 as diameter, B15 as density, and B16 as velocity. Type the following formula into cell B18.
= 1.294E-3 * friction * length / diameter * density * velocity^2
In cell B18 deltaP = 30.11624. The naming system is very quick, and allows you to see the variables in your formulas as words. This is a big advantage with complex formulas. Names can be deleted by choosing Formulas->Name Manager->Name->Delete.
Note: If you can’t find the Name Box, it is labeled in the screenshot in section 1.1 of this tutorial.
Named cells do not work in coding subroutines. Instead, you must define each variable as belonging to the value of a cell (for example friction = Range(“B12”). The named cells don’t help us but, it is still good practice to use descriptive variable names. We will now use the same formula in a subroutine program and output the deltaP value to cell B19. Run the following program.
Sub subdeltaP()
Dim deltaP As Double, friction As Double, length As Double
Dim diameter As Double, density As Double, Velocity As Double
friction = Range("B12")
length = Range("B13")
diameter = Range("B14")
density = Range("B15")
velocity = Range("B16")
deltaP = 0.001294 * friction * length / diameter * density * velocity ^ 2
Range("B19") = deltaP
MsgBox “deltaP = “ & deltaP
End Sub
Named cells do work in functions. We can create the following custom VBA function.
Function funcdeltaP(friction As Double, length As Double, diameter As Double, _
density As Double, velocity As Double) As Double
funcdeltaP = 1.294E-3 * friction * length / diameter * density * velocity^2
End Function
We can then call the function in the worksheet as seen in the screenshot below.
=funcdeltaP(B12,B13,B14,B15,B16)
Table of Contents
Ch1-Introduction
Ch2-Loops
Ch3-If Statements
Ch4-Functions
Ch5-Subs & Functions
Ch6-Read & Write
Ch7-Operators
Ch8-Built-in Functions
Ch9-Built-in Examples
Ch10-Debugging
Ch11-Running Subs
Ch12-Sample Programs
Ch13-WS Formulas
Ch14-Questions
