Function not running correctly (keep getting #VALUE error)

**Delgrath** · 08-24-2010, 03:07 AM

I am writing a very simple function to calculate pressure drop along a rough pipe, the code is below


Function pdrop(ByVal rho As Double, Q As Double, D As Double, mu As Double, TubeL As Integer)

Dim Velocity As Double
Dim Re As Double


rho = Pipecalc.Range("E49").Value
D = Pipecalc.Range("J45").Value
mu = Pipecalc.Range("E48").Value
Q = Pipecalc.Range("E47").Value
TubeL = Pipecalc.Range("E45").Value

Velocity = 4# * Q / (WorksheetFunction.Pi * D ^ 2)


Re = rho * Velocity * D / mu


If Re < 2300 Then
    fricfac = 64 / Re
    Else
    fricfac = 0.3164 / Re ^ 0.25
    End If

pdrop = (Velocity ^ 2 * rho * fricfac * TubeL) / 2# * D

End Function

From my understand of the way I've written the function, I should be able to execute it by simply writing =pdrop() into any cell in my worksheet.. this is not the case however. If I do this I simply get a #VALUE error, and I know the input data I'm using works because I've tested it independently elsewhere. Any ideas as to what could be the problem?

NB: This is the first function I've had to write so if I've done anything stupid don't hesistate to let me know as I need to pick VBA up quite quickly.

**sweep** · 08-24-2010, 03:14 AM

Probably better written like so,

Function pdrop(ByVal rho As Double, Q As Double, D As Double, mu As Double, TubeL As Integer)

Dim Velocity As Double
Dim Re As Double


Velocity = 4# * Q / (WorksheetFunction.Pi * D ^ 2)


Re = rho * Velocity * D / mu


If Re < 2300 Then
    fricfac = 64 / Re
    Else
    fricfac = 0.3164 / Re ^ 0.25
    End If

pdrop = (Velocity ^ 2 * rho * fricfac * TubeL) / 2# * D

End Function

and used like this:

=pdrop(E49,J45,E47,E48,E45)

**Delgrath** · 08-24-2010, 05:03 AM

Thank you sweep that worked perfectly. With regards to assigning cell values to the variables what was the problem? Was it because I listed the variables in the function both as inputs AND as separate variables within the code? (I take it this was the wrong thing to do).

Thanks again.

**sweep** · 08-24-2010, 05:49 AM

The error was being raised because there were insufficient arguments in the function call, in much the same way as if you entered =SUM() into a cell. You could (although it's foolish to do so) enter something along the lines of =pdrop(1,2,3,4,5) into a cell, which will pass the correct number of arguments to the function code, which will immediately overwrite those values with the contents of the cells (specified in your original code).

To use specific cells as the arguments in your function, your opening line should have been

Function pdrop()

followed by dimensioning of variables, and assigning values.

For my money, if you're writing a function, you're almost certainly at some time going to want to change the cells that contain its arguments, so I would stick with my interpretation of your code.

I hope that's clear - let me know if not.