Stop looping If I get the results

[kingman29]

Option Explicit
Public Function puissance2(Taux_CPrincpal As Double) As Double
On Error Resume Next
Dim x As Double
     puissance2 = Taux_CPrincpal
       For x = 2 To 40
     puissance2 = (puissance2 + Application.Power(Taux_CPrincpal, x))
    Next x
End Function

Public Function deversement(Charge_Centre_Principal, Centre1 As String, Vlrange1 As Range, Vlcolone As Double, TauxCP As Double) As Double

Dim y As Double
For y = 2 To 40
deversement = (-Charge_Centre_Principal * Application.WorksheetFunction.VLookup(Centre1, Vlrange1, Vlcolone, 0)) * (1 + puissance2(TauxCP))

Next y

End Function

I have this code above for calculate a personnel function,
I have a problem that every time when I close the file and open it again , I see that the loop start from zero
and i see in the top of the file , that the File start calculating after few minutes , the Real result appear
Is that any way to make the looping stop if the result appear ? or anyway to make the loop stop and not refresh every time

Thank you

Note: The file is In French Language, and It's very heavy (64 Mb)

 

[StephenCrump]

Your function puissance2 is evaluating a geometric progression. So you could replace all cell formulae: =puissance2(x) with =(x^N-1)*x/(x-1) where N is 40 in this case.

Your function deversement doesn't make sense. You're looping from 2 to 40 and overwriting the result each time. And on each iteration of the loop, puissance2 will iterate 40 times. As it's written, you could replace with a formula: =-Charge_Centre_Principal*VLOOKUP(Centre1, Vlrange1, Vlcolone,)*(TauxCP^40-1)*TauxCP/(TauxCP-1).

With a file this large, I'd be looking to replace as many formulae/VBA calls as possible with values. One possible way to do this: have a row of formulae at the top of the worksheet. Copy the row down to all subsequent rows. Copy these results and paste values, and only update again with formulae when necessary.