UDF issue... AGAIN!

[Jameo]

Hi guys,

Can anyone see any possible issues with the following UDF?

Public Function Vel(Pressure As Double, Flow As Double, Diameter As Double) As Double
Dim z As Double
Dim Area As Double

Area = (Pi * (Diameter / 2) ^ 2) / 1000
z = 1 - 0.00248 * (Pressure + 1.01325)
Vel = Flow * 1000000 / 86400 / Area * (1.01325 / (Pressure + 1.01325)) * z / 0.99978
End Function

It should be used to calculate the flow rate of gas in a pipeline. However, it is just returning #VALUE!

Any ideas?

Thanks

 

[MrKowz]

Pi is not a pre-defined variable; it is a worksheet function. Try:

Public Function Vel(Pressure As Double, Flow As Double, Diameter As Double) As Double
Dim z As Double
Dim Area As Double
 
Area = (WorksheetFunction.Pi * (Diameter / 2) ^ 2) / 1000
z = 1 - 0.00248 * (Pressure + 1.01325)
Vel = Flow * 1000000 / 86400 / Area * (1.01325 / (Pressure + 1.01325)) * z / 0.99978
End Function

 

[Joe4]

Don't you have to declare/set "Pi"?
I tested it out in VBA, and Excel doesn't seem to identify the variable "Pi" with the actual value of "Pi".

 

[Jameo]

Where would I be without you MrKowz? Obviously you are right... again

Thanks very much mate

 

[MrKowz]

Where would I be without you MrKowz? Obviously you are right... again

Thanks very much mate

Happy to be of (continued) assistance! Thank you for the feedback.

 

[MrKowz]

Don't you have to declare/set "Pi"?
I tested it out in VBA, and Excel doesn't seem to identify the variable "Pi" with the actual value of "Pi".

If you wanted to use the variable Pi in multiple areas, and you don't want to call it with WorksheetFunction.Pi each time, you can declare it as a Public Const:

Option Explicit
Public Const Pi As Double = 3.14159265358979
 
Public Function Vel(Pressure As Double, Flow As Double, Diameter As Double) As Double
Dim z As Double
Dim Area As Double
Area = (Pi * (Diameter / 2) ^ 2) / 1000
z = 1 - 0.00248 * (Pressure + 1.01325)
Vel = Flow * 1000000 / 86400 / Area * (1.01325 / (Pressure + 1.01325)) * z / 0.99978
End Function

Note, even though you are defining Pi to a fixed number of decimal places, Excel's Floating Point Decimal approximation restricts you to that limited number of decimal places anyway.

 

[Jameo]

Where would I be without you MrKowz? Obviously you are right... again

Thanks very much mate

Edit:

Thanks for your input as well Joe, didn't realise you had replied until now

 

[Joe4]

Actually, I didn't add much that MrKowz didn't already say, I am just a much slower typist, so I didn't see his reply!

 

[pgc01]

If you wanted to use the variable Pi in multiple areas, and you don't want to call it with WorksheetFunction.Pi each time, you can declare it as a Public Const:

... or not public, inside the function

Option Explicit
 
Public Function Vel(Pressure As Double, Flow As Double, Diameter As Double) As Double
Const Pi As Double = 3.14159265358979
...

 

[MrKowz]

... or not public, inside the function

Option Explicit
 
Public Function Vel(Pressure As Double, Flow As Double, Diameter As Double) As Double
Const Pi As Double = 3.14159265358979
...

If there is only one function that uses Pi, this would work nicely. However, if there are multiple UDFs that need "Pi" in the workbook, declaring a single public constant would likely be ideal.