Runtime Error 5 in calculation. Driving me crazy

[acotgreave]

HI,
I'm trying to calculate a profit growth in one of my Reports.

My function is as follows.

Private Function Rate(vStart As Variant, vEnd As Variant, vYears As Variant)
    Rate = ((vEnd / vStart) ^ (1 / vYears) - 1)
End Function

When I call it with the following values I get a RunTime error 5 "Invalid Procedure Call":

    vEnd = 1738206
    vStart = -21430
    vYears = 3

If I replace the variables with the values, I still get the error:

Rate = ((1738206 / -21430) ^ (1 / 3) - 1)

However, if I replace the division calculations, I don't:

Rate = -81.1108726084928  ^ -0.666666666666667

This is driving me crazy. The function works with other values, and I can't work out why these three values are causing me the problem.

Can anyone help me out. It's pretty urgent!

Thanks

 

[DaveJHPT]

Without checking this, it could be because you're using variants and passing in longs, so it's not converting the longs to doubles (or something).

Try

Rate = ((CDbl(vEnd) / CDbl(vStart)) ^ (1 / CDbl(vYears)) - 1)

Dave

 

[Bat17]

1) Rate is a built in function and you should not use it to anme your function

2)From help for ^ operator

result Required; any numeric variable.
number Required; any numeric expression.
exponent Required; any numeric expression.

Remarks

A number can be negative only if exponent is an integer value. When more than one exponentiation is performed in a single expression, the ^ operator is evaluated as it is encountered from left to right.

Not sure why hard coding the Exponent does not generate an error though! I suspect that it may be rounding it first which would not give you the correct value then.

Peter

 

[acotgreave]

Ah, that makes sense.

I wittled it down and found the following simpler ways to generate the error.

Private Sub TestPower()
    Dim x As Double
    Dim y As Double
    Dim res As Double
    
    x = -3
    y = -0.6
    ' this won't work. can't have a negative decimal as the exponent
    res = x ^ y
    
    x = 3
    y = -0.6
    ' this WILL work. All i did was change the sign of the x value to positive
    res = x ^ y
    
    ' this will work, even though it contradicts the help file entry on ^
' and it appears that it doesn't do any rounding, because the answer is
' correct
    res = -3 ^ -0.6
End Sub

All variables are now doubles.

I'm stumped. Fortunately, I think I can work around it in a different way, but it's now niggling at me, and I wonder if this is some kind of error/bug in VBA?

I'm using Access 2000 in Windows XP, BTW.

Any thoughts, anyone?

Andy

 

[Bat17]

Hve you checked out the built in finance functions in Access to see if any do what you want?
(They all all Dutch to me )

Peter

 

[acotgreave]

Hi,
I've worked around it now, so it's not a critical problem.

BUT, it's the sad kind of thing that'll keep me awake at night! If anyone can answer WHY the simplified sample code I posted above doesn't work when using variables, I'll be a happy man...

Andy