what's wrong with custom function?

  • Thread starter Thread starter Juggernnath
  • Start date Start date
J

Juggernnath

Function RsGlaso(gama, API, T, Pb)
' Application.Volatile False

Dim Rs As Double, KorekcioniPb As Double
'gama = gas gravity - input testData=0.732
'API = ro [API] - input testData=38
'T - [F] - input testData=180
'Pb - [psi] - input testData=3811

KorekcioniPb = 10 ^ (2.8869 - (14.1811 - 3.3093 * Log(Pb)) ^ 0.5)
'Rs = gama * (((API ^ 0.989) / (T ^ 0.172)) * KorekcioniPb) ^ 1.2255
Rs = gama * (((API ^ 0.989) / (T ^ 0.172)) * KorekcioniPb) ^ 1.2255
RsGlaso = 5
End Function


It works if I type formulas into spreadsheet, but if I defined it as custom
function it returns #VALUE
 
It appears to me that you will get a value error when taking the square root
of a negative number.
KorekcioniPb = 10 ^ (2.8869 - (14.1811 - 3.3093 * Log(Pb)) ^ 0.5)
Click to expand...

Perhaps re-written as:
KorekcioniPb =
10^((28869 - 100*Sqr(141811 - 33093*Log(Pb))) / 10000)

I think you should get a Value error when Pb is at your test value of 3811
because this causes a negative square root.
Looks to me that Pb should be in the range of 0 - 72.619...

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


Juggernnath said:
Function RsGlaso(gama, API, T, Pb)
' Application.Volatile False

Dim Rs As Double, KorekcioniPb As Double
'gama = gas gravity - input testData=0.732
'API = ro [API] - input testData=38
'T - [F] - input testData=180
'Pb - [psi] - input testData=3811

KorekcioniPb = 10 ^ (2.8869 - (14.1811 - 3.3093 * Log(Pb)) ^ 0.5)
'Rs = gama * (((API ^ 0.989) / (T ^ 0.172)) * KorekcioniPb) ^ 1.2255
Rs = gama * (((API ^ 0.989) / (T ^ 0.172)) * KorekcioniPb) ^ 1.2255
RsGlaso = 5
End Function


It works if I type formulas into spreadsheet, but if I defined it as custom
function it returns #VALUE
Click to expand...
 
I don't get the #VALUE error unless

API < 0: API ^ 0.989 fails,
T = 0: division by 0 in the Rs= line,
Pb <= 0: Log(Pb) fails,
or
Pb > about 72.5: (14.1811 - 3.3093 * Log(Pb)) ^ 0.5 fails.

On the other hand, since you put RsGlaso = 5, the function will, if
it doesn't error, always return 5. That can't be right...
 
LOG(3811)=3.5810389
*3.3093=11.850732
14.811-11.850732=2.9602678
SQR(2.9602678 >0) = 1.72
I didn't understand if you made calculating mistake or tried to say
something else...
================================================================
Dana DeLouis said:
It appears to me that you will get a value error when taking the square root
of a negative number.
KorekcioniPb = 10 ^ (2.8869 - (14.1811 - 3.3093 * Log(Pb)) ^ 0.5)
Click to expand...

Perhaps re-written as:
KorekcioniPb =
10^((28869 - 100*Sqr(141811 - 33093*Log(Pb))) / 10000)

I think you should get a Value error when Pb is at your test value of 3811
because this causes a negative square root.
Looks to me that Pb should be in the range of 0 - 72.619...

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


Juggernnath said:
Function RsGlaso(gama, API, T, Pb)
' Application.Volatile False

Dim Rs As Double, KorekcioniPb As Double
'gama = gas gravity - input testData=0.732
'API = ro [API] - input testData=38
'T - [F] - input testData=180
'Pb - [psi] - input testData=3811

KorekcioniPb = 10 ^ (2.8869 - (14.1811 - 3.3093 * Log(Pb)) ^ 0.5)
'Rs = gama * (((API ^ 0.989) / (T ^ 0.172)) * KorekcioniPb) ^ 1.2255
Rs = gama * (((API ^ 0.989) / (T ^ 0.172)) * KorekcioniPb) ^ 1.2255
RsGlaso = 5
End Function


It works if I type formulas into spreadsheet, but if I defined it as custom
function it returns #VALUE
Click to expand...
Click to expand...
 
In VBA, LOG() is the natural log function. In XL, LOG(x) is the log
to base x, with the default being base 10.

If you want log base 10 in VBA, use Log(x)/Log(10)

?Log(3811)/Log(10)
3.58103894877217
 
Hi. Looks like J.E. caught the problem here. :>) Here are some
additional ideas for consideration:

Sub Demo()
'VBA
Debug.Print Log(3811)

Worksheet Function
Debug.Print WorksheetFunction.Log(3811, 10)
Debug.Print WorksheetFunction.Log10(3811)
End Sub

returns...
8.24564690087386
3.58103894877217
3.58103894877217

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


Juggernnath said:
LOG(3811)=3.5810389
*3.3093=11.850732
14.811-11.850732=2.9602678
SQR(2.9602678 >0) = 1.72
Click to expand...

<snip>
 
Sorry, I thought of something like that, but I was stupid and quickly
checked the function in Worksheet. I didn't know that Log isn't the same
function in Sheet and in VBA.
 
Back
Top