Dear all,

I encounter a few tiny problems in the following code...

Problems:

- run-time error 6, overflow at "theta_1 = WorksheetFunction.Sum(theta_1N) / WorksheetFunction.Sum(theta_1D)" (due to the amount (1284) elements?)

- the subscripts are also out of range for the parameters with index u (histrate(u), theta_1N(u) etc.)

Any ideas how to resolve these? Many thanks in advance...

Private Sub data()
Dim Rm, theta_1, theta_2, theta, sigma, dt, x, y
Dim u As Long
Dim histrate As Variant, theta_1D As Variant, theta_1N As Variant, theta_2N As Variant

dt = 1

Application.Calculation = xlManual

histrate = Worksheets("data").Range("B2:B1285")
x = LBound(histrate)
y = UBound(histrate)

Rm = WorksheetFunction.Average(histrate)
Sheets("data").Cells(2, 3).Value = Rm

ReDim histrate(x To y) As Variant
ReDim theta_1N(x To y) As Variant
ReDim theta_1D(x To y) As Variant
ReDim theta_2N(x To y) As Variant

For u = x To y

If x = 1 Then 'do nothing
Else
theta_1N(u) = (histrate(u) - histrate(u - 1)) * (Rm - histrate(u - 1)) / histrate(u - 1)
theta_1D(u) = (Rm - histrate(u - 1)) ^ 2 / histrate(u - 1)
End If

Next u

theta_1 = WorksheetFunction.Sum(theta_1N) / WorksheetFunction.Sum(theta_1D)
theta = theta_1 / dt
Sheets("data").Cell(2, 11).Value = theta

For u = x To y

If x = 1 Then 'do nothing
Else
theta_2N(u) = (histrate(u) - (theta_1 * Rm + histrate(u - 1) * (1 - theta_1))) ^ 2 / histrate(u - 1)
End If

Next u

theta_2 = WorksheetFunction.Sum(theta_2N) / WorksheetFunction.Count(histrate)
sigma = WorksheetFunction.Sqr(theta_2 / dt)
Sheets("data").Cell(2, 12).Value = sigma

Application.Calculation = xlAutomatic

End Sub