Wrong computation inside a loop

Question

All, I haven't encountered something as frustrating as this is before. Your help with regards to this EXTREMELY NAIVE problem would be appreciated. It seems that the problem is very simple, but I dove too much into it that my eyes can't catch the stupid mistake.

(Sample Code is pasted below - I used a simplified version). I simply have a two-column array X, and I fill the values in this array using a simple loop. All parameters s, t, N are constant and I retrieve them from other cells in the sheet, so you can just ignore them.

The problem In the second loop where I fill in elements of array X. I fill in the second column with linearly increasing values (0.01, 0.02, 0.03, ...., 30) , and the first column should be some function of these values in the second column and the constants N, s, t. Sounds simple!

The issue is the values in the first column come out wrong for some reason!! I verified it both in Excel and Matlab. For example, I took values of the constants N, s,t, and I plugged in 0.01 and computed the first column value both in Excel and Matlab, they both gave the same correct result (-0.1113). When I run the VBA code, it gives a different value (0.36 - I am 500% sure that -0.1113 is the correct answer). EVEN MORE ODD, I inserted a BREAK POINT at the Next j statement, and for j = 1, I verified all the values of all the constants, all correct, but X(j,1) is still 0.36. What drives me crazy, is that when I paste the very same formula that i use in my code for X(j,1), and paste it in the Immediate Window, it gives the correct value (-0.113) .. THE VERY SAME FORMULA.. ZERO CHANGES!!!!

Is there any intuitive reason as what could possibly cause this problem? I checked my code 1 million times to make sure there are no duplicates in parameter names, or some parameters taht do not get reset. Nothing!

*ANY * input would be highly appreciated. Thank you very very much, and i am sorry it is such a dumb question.

Sub Misc()

Dim N As Integer                
Dim s As Integer                
Dim t As Integer                
Dim j As Integer                
Dim i As Integer                
Const Step As Double = 0.01     
Const B As Integer = 30         
Dim X() As Double               


N = Range("AB1").End(xlDown).Row                      
s = WorksheetFunction.RoundDown(Sheets("Replacement").Range("AB1"), -1)     ' Compute S (Smallest meter reading, rounded down to nearest 10)
t = WorksheetFunction.RoundUp(Sheets("Replacement").Range("AB" & N), -1)    ' Compute T (Largest meter reading, rounded upto nearest 10)
Sheets("Replacement").Range("AD1").Value = s                                ' Export S and T to Sheet "Replacement" for subsequent use in Goal Seek
Sheets("Replacement").Range("AE1").Value = t

        For i = 1 To N Step 1
            Sheets("Replacement").Range("AC" & i).Formula = "=ln(AB" & i & ")"       ' Logarithm of the meter readings
        Next i

        ReDim X(1 To (B / Step), 1 To 2) As Double                                  ' Second column has values of Beta, First column has F(Beta), where F() is given by Beta MLE

        For j = 1 To (B / Step)
             X(j, 2) = j * Step
             X(j, 1) = (X(j, 2) * (((N / ((t ^ X(j, 2)) - (s ^ X(j, 2)))) * ((t ^ X(j, 2) * (WorksheetFunction.Ln(t))) - (s ^ X(j, 2) * (WorksheetFunction.Ln(s))))) - (WorksheetFunction.Sum(Range("AC1:AC" & N))))) - N

        Next j

Range("A1").Resize(Ubound(X,1),Ubound(X,2)).Value = X

End Sub

Answer 1

The function

WorksheetFunction.Sum(Range("AC1:AC" & N))

will return the sum for the specified range in the active sheet. Try setting the active sheet (or specifying it in the expression) to ensure that you reference the correct sheet.