long format problem in a function

[sabascal]

Hi all,

I keep having format problem with this very simple function.
Could you help?

'=========================
Function CAGR(Values As range) As Long
YrTotal = Values.Count
StartValue = Values(0)
EndValue = Values(YrTotal)

For Each cell In Values
If Application.IsErr(cell.Value) Then
ErrCode = CStr(cell.Value)
CAGR = CVErr(Right(ErrCode, Len(ErrCode) - InStr(ErrCode, " &quot))
Exit Function
End If
Next
CAGR = Sqr((EndValue / StartValue - 1))
On Error Resume Next
End Function
'=============================

 

[SkipVought]

Hi,

What do you mean be format problems?

Please be more specific!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sabascal]

Apologize,

when calling this formula from a spreadheet on range with containing 1,2,3,4,5,6,7,8 It returns a double = 0.00000000000
when it should be returning me 7^(1/8) =1.27....

Don't understand why.
I tried with declaring my variables as Double. Don't work neither.
latest version:
'==============
Function CAGR(Values As range) As Long
Dim YrTotal As Double, StartValue As Double, EndValue As Double
YrTotal = Values.Count
StartValue = Values(0)
EndValue = Values(YrTotal)

For Each cell In Values
If Application.IsErr(cell.Value) Then
ErrCode = CStr(cell.Value)
CAGR = CVErr(Right(ErrCode, Len(ErrCode) - InStr(ErrCode, " &quot))
Exit Function
End If
Next
CAGR = ((EndValue / StartValue - 1)) ^ (1 / YrTotal) - 1
On Error Resume Next
End Function

 

[SkipVought]

Put a break in you function and run it and see what's happnin' in the Watch Window!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[TonyJollans]

Hi sabascal,

Skip is correct - some basic debugging would be a good idea, but a couple of quick points ..

1. Which bit of the code do you think should give you 7^(1/8) from {1,2,3,4,5,6,7,8}?

2. StartValue = Values(0) should give an error. You have no error checking in the function so if you are not getting an error reported it must be being trapped by a higher level trap. Do you perhaps have an On Error Resume Next in the calling routine? If so, it could be that the function is not returning zero (or anything else) and you are picking up the initial value of the variable you are trying to assign the result of the function to.

Enjoy,
Tony

 

[SkipVought]

Tony,

I see where he's getting 7^(1/8) - 1 = 0.275373107

He probably has some type conversion problems.


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[TonyJollans]

Hi Skip,

You and me on this one

I looked again and now I see it. I was looking at the code in the first post, not the second one.

It'll still bomb looking for the zeroth cell in the range though - and not get as far as returning anything.

Enjoy,
Tony

 

[SkipVought]

Tony,

You're right, Values(0) is out of bounds

So, sabascal, If you had put a break let say on line...

If Application.IsErr(cell.Value) Then

then you could use the watch window to cahce the values in...

YrTotal
StartValue
EndValue

and step from there if necesary.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sabascal]

My stupid mistake was:
function was suppose to return a Long while I was trying to return a double