Value error

[Peter89]

Hi,

I have implemented the following code in vba excel and i have a problem.The code that i have implemented is the Erlang B traffic formula.I have two inputs:the traffic A and n=channel number.When i introduce for the traffic an input greater than 100 i have a value error.For inputs from 0 to 100 the formula give's me the expected value ,but for values greater than 100 ,the error occurs.

I hope that one of you can clear me.
Thanks.

Private Const GoS As Double = 0.001



Private Function Erl_B(A As Double, n As Integer) As Double
Dim BlRate As Double
Dim numarator As Double
Dim numitor As Double
Dim x As Integer



Do
numarator = A ^ n / WorksheetFunction.fact(n)
For x = n To n
numitor = numitor + A ^ x / WorksheetFunction.fact(x)
Next x
BlRate = numarator / numitor
n = n + 1

Loop Until (BlRate <= GoS)

Erl_B = n - 1

End Function

[AD_Taylor]

I get the VALUE error when, either A = 103 or n = 138.

I converted the Function to a corresponding Sub so that it could show error messages and debug correctly. The code returned an overflow error, basically meaning the values had gone too high. With A = 103 and n = 137 this part:

A ^ (n - 1)

gave this value, once n had increased to n = 154:

9.20651743378656E+307

If we were to evaluate 'A ^ n' as in your actual code the value would actually be outside of the range that Double type variable can store

From the Microsoft site (http://msdn.microsoft.com/en-us/library/x99xtshc.aspx) :

Double Data Type holds double-precision floating-point numbers that range in value from ... 4.94065645841246544E-324 through 1.79769313486231570E+308 for positive values.

As you can see once we go above 'E+308', we get the problem.

Unfortunately, I can't suggest a fix here because the values are simply outside the range that VBA can work with.


Heres the code I used to find the problem:

Sub prTEST()

Dim A               As Double
Dim n               As Integer

Dim BlRate          As Double
Dim numarator       As Double
Dim numitor         As Double
Dim x               As Integer

A = Range("A2").Value
n = Range("B2").Value

Do

Debug.Print (A ^ (n - 1))
    numarator = A ^ n / WorksheetFunction.Fact(n)
    numitor = numitor + numarator
    
    BlRate = numarator / numitor
    
    n = n + 1

Loop Until (BlRate <= GoS)

MsgBox n - 1

End Sub

Run that code with your erroring values and you'll see the result of the previous 'n' value in the immediate window.