UDF returning #value

[Guest]

Howdy;

I have the following UDF that keeps returning #value in the worksheet. When
I debug, it appears to bring in the right values, and does the calcs, at
least through the nSecondsPerDay.

Help or guidance would be apprecicated.

Regards
Tim
-----

Function MixVelocity2007(OilRate_mbd As Double, WaterRate_mbd As Double, _
GasRate_mscfd As Double, Pressure_psi As Double, Temperature_F As Double, _
PipeID_in As Double) As Double

Dim nLiquidRate As Double
Dim nGasRate As Double
Dim nTempConversion As Double
Dim nPipeIDArea As Double
Dim nSecondsPerDay As Double

nLiquidRate = (OilRate_mbd + WaterRate_mbd) * 5.6146
nGasRate = GasRate_mscfd * 1000 * (14.7 / Pressure_psi)
nTempConversion = ((Temperature_F - 32) * (5 / 9) + 273.15) / 288.15
nPipeIDArea = (PipeID_in / 12) ^ 2 * 3.1415 / 4
nSecondsPerDay = 60 * 60 * 24

MixVelocity2007 = (nLiquidRate + (nGasRate * nTempConversion)) / _
(nPipeIDArea * nSecondsPerDay)

End Function

 

[Dave Peterson]

Try changing this one line:
nSecondsPerDay = 60 * 60 * 24
to
nSecondsPerDay = 60# * 60 * 24
or
nSecondsPerDay = 86400
or
nSecondsPerDay = 86400#

60 * 60 * 24 are all integers. Since they're all integers, excel's VBA wants to
use an integer to hold that product (60*60*24). But 86400 is too large to be
held in an integer--and so you get an overflow error.

By using 60#, I've made that 60 a double. So excel's VBA will use a double to
hold that product and everything will (er, should) work ok.

 

[Guest]

Dave;

Thanks for the tip, works like a charm. I thought the DIM statement for the
variable would have handled it...

Regards,
Tim

 

[Dave Peterson]

But it wasn't the assignment to the left hand side, it was that intermediate
result on the right hand side.

Kind of weird, huh?

 

[Guest]

Put all of the constants such as 60 and 24 into proper declared constants -

dim dblMinutesinHour as double = 60

and so on.

This will make the code more readable.

Also, there is no error handler on your UDF - you should add one to ensure
that an error in this function does not cause other problems.