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 ?

 

[kingman29]

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

Why it's seem to you that it's not a problem if the excel recalculate ?
when you create a Workbook , is that recalculate when re-open it ?
so this what I neeed,
I hope you understand what I need
Thank you and sorry

 

[Joe4]

I am afraid that there may be a language barrier working here, so I am not fully understanding what you are saying the problem is.

Please answer these questions "Yes" or "No":
1. Is the recalculation when you open the file happening automatically, or are you having to trigger the calculation manually?
2. Are all the values showing the correct value after the recalculation is finished?

A few other questions:
3. Exactly how many sheets/rows/columns of data do you have?
4. Approximately how many cells have formulas?

Note that there is an issue with volatility in using User Defined Functions: Volatile Functions in Excel VBA
One option may be to consider changing these calculations to hard-coded values before closing the file (which can be done via VBA). I do not know if that will work for you, because I do not know if the existing data and formulas need to remain dynamic or not.

Based on the amount of data and relationships you have between your data, I suspect that the large overriding problem is that you may not be using the correct tool for the job. It appears that you may really have a Relational Database model (lots of interconnected data related to each other).

If that is the case and you have a large amount of data, Excel is often a poor choice of tools to use for something like that, as that is not really what it was designed for (it can do it, but performance will often lag, and data may be cumbersome to maintain). A relational database program like Microsoft Access or SQL handles Relational Database models much better than Excel does.

 

[kingman29]

I am afraid that there may be a language barrier working here, so I am not fully understanding what you are saying the problem is.



Note that there is an issue with volatility in using User Defined Functions: Volatile Functions in Excel VBA
One option may be to consider changing these calculations to hard-coded values before closing the file (which can be done via VBA). I do not know if that will work for you, because I do not know if the existing data and formulas need to remain dynamic or not.

Based on the amount of data and relationships you have between your data, I suspect that the large overriding problem is that you may not be using the correct tool for the job. It appears that you may really have a Relational Database model (lots of interconnected data related to each other).

If that is the case and you have a large amount of data, Excel is often a poor choice of tools to use for something like that, as that is not really what it was designed for (it can do it, but performance will often lag, and data may be cumbersome to maintain). A relational database program like Microsoft Access or SQL handles Relational Database models much better than Excel does.

Please answer these questions "Yes" or "No":
1. Is the recalculation when you open the file happening automatically, or are you having to trigger the calculation manually? = Yes it is recalculate automaticly
2. Are all the values showing the correct value after the recalculation is finished? = Yes all values showing are correct

A few other questions:
3. Exactly how many sheets/rows/columns of data do you have? = in my real File I have 76 Sheets , and every sheets have 176 Columns and 180 Rows
4. Approximately how many cells have formulas? = between 10.000 and 13.000 cells

 

[kingman29]

I think it is very simple what I want
this question maybe give my problem
Why when I use Excel Formulas (VLookup, HLookup, NBCAR, Power, ... etc ) save and close the file, after re-open the file we see the exact values and Workbook never recalculate
And why When we create our custom Function (Formulas) we see every time we closed the file after saving , and re-opening it, we see that the file start from the beginging and recalculate ?

 

[Joe4]

I just created a small file with a User Defined Function and tested it out, and do not have any "recalculation" going on when I open the file. I think the issues is the amount of data that you have. It is also important to note that User Defined Functions usually do not perform as well as the native Excel functions, especially when it comes to volatility.

If you have 76 sheets of data with lots of lookup formulas, then what you actually have is a Relational Database. While you can use Excel as a relational database, that is not what it was intended for, and is very clunky and cumbersome (you can see the performance issues you are experienced). For work such as this, it is much better to use a program that was designed for Relational Databases, like Microsoft Access or SQL.

 

[kingman29]

I just created a small file with a User Defined Function and tested it out, and do not have any "recalculation" going on when I open the file. I think the issues is the amount of data that you have. It is also important to note that User Defined Functions usually do not perform as well as the native Excel functions, especially when it comes to volatility.

If you have 76 sheets of data with lots of lookup formulas, then what you actually have is a Relational Database. While you can use Excel as a relational database, that is not what it was intended for, and is very clunky and cumbersome (you can see the performance issues you are experienced). For work such as this, it is much better to use a program that was designed for Relational Databases, like Microsoft Access or SQL.

Hello
I can't use Microsoftt SQL and this for one reason that in ly company Rules there is a structure refuse to use any software that The administration didn't accept it,
so Can you send me a code of your function ?
maybe I did a mistake in my function

 

[Joe4]

Can you send me a code of your function ?

That will not be very helpful at all. It was a very simple example that just added two values together.

It sounds to me like there may not really be an issue at all. "Recalculation" isn't a bad or incorrect thing.
If it is returning the correct results, nothing may be wrong. That fact that it takes a little while for it to run may be due to the amount of data and formulas you have in your workbook.
That may just be the price you have to be willing to pay in order to try to use Excel as a Relational Databse.

There may be other ways to go about this in Excel, but it may involve a complete redesign of your program. That would probably involve a total review of all your project, all your data and your goals (what you are trying to accomplish). That is probably something that may be a bit much to expect from a free site, and more likely falls under the category of something you would hire a consultant to do.

I don't think there is much else I can offer on this.