Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

long format problem in a function

Status
Not open for further replies.

sabascal

IS-IT--Management
Aug 20, 2002
85
GB
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, " ")))
Exit Function
End If
Next
CAGR = Sqr((EndValue / StartValue - 1))
On Error Resume Next
End Function
'=============================
 
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, " ")))
Exit Function
End If
Next
CAGR = ((EndValue / StartValue - 1)) ^ (1 / YrTotal) - 1
On Error Resume Next
End Function
 
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
 
Hi Skip,

You and me on this one [wink]

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
 
Tony,

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

So, sabascal, If you had put a break let say on line...
Code:
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
 
My stupid mistake was:
function was suppose to return a Long while I was trying to return a double
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top