storing function vba code value when ends

[kingman29]

Hello
I have this vba functions codes :

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 90
     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
On Error Resume Next
deversement = (-Charge_Centre_Principal * Application.WorksheetFunction.VLookup(Centre1, Vlrange1, Vlcolone, 0)) * (1 - puissance2(TauxCP))
End Function

I have a problem with it
that when the calculation is done , and close the Excel File, and open it again, I see that the function start from the zero , and I found that the cells is not like the final result (before close the file)
is that any way to storing the results even close the file and open it again ?

 

[Joe4]

How/where are you calling/using this function?
Are you saving your file before closing it, to save the updates you made?

One other thing you may want to do is to add the line:

Application.Volatile

under each of the "Public Function..." lines.

 

[kingman29]

How/where are you calling/using this function?
Are you saving your file before closing it, to save the updates you made?

One other thing you may want to do is to add the line:

Application.Volatile

under each of the "Public Function..." lines.

This function VBA code writing in a Model
I called this function in Excel cells of corse
And yes I saved the file before closing
I added the code you gave, And I have the same problem
Is that any way to upload here the file?

 

[Joe4]

I have a problem with it
that when the calculation is done , and close the Excel File, and open it again, I see that the function start from the zero

Where exactly are you seeing this?
Do you have a formula somewhere on your sheet that is using this?
Note that you would have the store the value in a cell somewhere if you want to retain its value.
VBA itself will not retain it from session to session.

Is that any way to upload here the file?

You can post images here, but not files.
If you wish to share you file, you would need to upload it to a file sharing site, and then provide a link here.

 

[kingman29]

Where exactly are you seeing this?
Do you have a formula somewhere on your sheet that is using this?
Note that you would have the store the value in a cell somewhere if you want to retain its value.
VBA itself will not retain it from session to session.

You can post images here, but not files.
If you wish to share you file, you would need to upload it to a file sharing site, and then provide a link here.

this is what happened : After a moment when I opened the file ; the result apear :

test.xlsm
	N	O	P	Q	R
86	-	-	-	-	-
87	-	-	- 99 616 094.74	59 795.04	-
88	14 290 181.18	-	-	271 713.89	-
89	2 356 087.22	-	-	44 165.77	-
90	-	-	-	261 958.74	-
91	-	-	-	- 263 407 290.98	13 994 370.44
92	-	-	-	-	- 13 994 370.44
93	-	-	-	-	-
94	-	-	-	-	-
95	-	-	-	-	-
96	1 434 140.05	-	19 923 218.95	156 123.74	-
97	-	-	19 923 218.95	17 400.86	-
98	1 126 824.32	-	19 923 218.95	26 527.74	-
99	-	-	-	2 254 851.75	-
100	-	-	19 923 218.95	9 390.61	-
101	-	-	-	24 661.46	-
102	-	-	-	14 207.62	-
103	-	-	-	-	-
104	-	-	-	51 713 033.56	-
105	-	123 911.26	-	-	-
106	-	-	-	2 316.06	-
107	-	-	-	5 336.30	-
108	-	-	-	4 864.95	-
109	-	-	-	4 024.90	-
Déverssement

Cell Formulas
Range	Formula
N86:N109	N86	=IFERROR(deversement($N$36,C86,'Taux Rapport d''activité'!$B$7:$BT$165,33,'Taux Rapport d''activité'!$AJ$31),0)
O86:O109	O86	=IFERROR(deversement($O$42,C86,'Taux Rapport d''activité'!$B$7:$BT$165,O$8,'Taux Rapport d''activité'!$AU$37),0)
P86,P88:P109	P86	=IFERROR(deversement($P$87,C86,'Taux Rapport d''activité'!$B$7:$BT$165,P$8,'Taux Rapport d''activité'!$AB$82),0)
Q92:Q109,Q86:Q90	Q86	=IFERROR(deversement($Q$91,$C86,'Taux Rapport d''activité'!$B$7:$BT$165,Q$8,'Taux Rapport d''activité'!$G$86),0)
R93:R109,R86:R91	R86	=IFERROR(deversement($R$92,$C86,'Taux Rapport d''activité'!$B$7:$BT$165,R$8,'Taux Rapport d''activité'!$H$87),0)
P87	P87	=-SUM(E87:O87)-SUM(Q87:BV87)
Q91	Q91	=-SUM(E91:P91)-SUM(R91:BV91)
R92	R92	=-SUM(E92:Q92)-SUM(S92:BV92)

and the photo show when I closed the file and opened it again
the result Disappears and after a moment it appear
and look at the arrows :
when the iteration finish, the result appear

 

[kingman29]

the name of sheets is in French language

 

[Joe4]

the result Disappears and after a moment it appear
and look at the arrows :
when the iteration finish, the result appear

Are you saying that when you first open the file, the results disappear for a moment, but then re-appear?
So if the are, in fact, re-appearing, are they correct?
If so, then is there still really an issue (perhaps it takes a few seconds for it to go through the iterations)?

 

[kingman29]

Are you saying that when you first open the file, the results disappear for a moment, but then re-appear?
So if the are, in fact, re-appearing, are they correct?
If so, then is there still really an issue (perhaps it takes a few seconds for it to go through the iterations)?

Yes Exactly ,
I will explain
In my work they was using this formula :

Charges d'exploitation.xlsm
	O
42	- 99 475 394.90
43	-
44	-
45	14 921 309.23
46	-
47	-
Déverssement

Cell Formulas
Range	Formula
O42	O42	=-SUM(E42:N42)-SUM(P42:BV42)
O43:O47	O43	=-$O$42*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-($O$42*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-(($O$42*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-(((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-(((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-(((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-(((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-((((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-(((((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-((((((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-(((((((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-((((((((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-(((((((((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-((((((((((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-(((((((((((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-((((((((((((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-(((((((((((((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-((((((((((((((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-(((((((((((((((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-((((((((((((((((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-(((((((((((((((((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-((((((((((((((((((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-(((((((((((((((((((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-((((((((((((((((((((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-(((((((((((((((((((((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)-((((((((((((((((((((((((((((($O$42*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*'C150'!$F$45)*VLOOKUP(C43,'C150'!$C$15:$F$174,4,0)

Look How the formula is long ?
so I decided to create a VBA Code to reduce the Risk of any mistakes
so the original file with this formulaa work normalyy, when we close the Excel file and open it again , we see the result , and not an iteration
but when i use my function and close the file
Excel calculat from the begining

 

[Joe4]

But if it is returning the correct value (after a moment), then I do not understand what the issue is.

 

[kingman29]

But if it is returning the correct value (after a moment), then I do not understand what the issue is.

The issue is not he recalculate , I want not the excel recalculate, I want Excel save the Result even After close
beceause the File is 62 Mb , when it recalculate it take a time
second thing it show me there is a error on the Forumla