how to save personnel function results ?

[kingman29]

Hello again
I created a personnel function
I have two problems
First one, that when I close the File, and oped it again, the function calculating the results
second one if i double click at any cell in the sheet and click enter I see that all cell witch contains the Function refreshing all the cells results
(Try to double click at any empty cell and move to another with one click, and focus in the cells witch have the function formulas, you will see that the function is refreshing after moving the click to another cell)

So
How make Excel file save the results ?
and how to stop the refreshing the functions when I double click the results
Excel File Example

 

[mikerickson]

The link to your Google Sheet requires access.

I don't know what you mean by "personal function". I first thought that you meant a VBA UDF, but Google sheets doesn't support that.

 

[kingman29]

The link to your Google Sheet requires access.

I don't know what you mean by "personal function". I first thought that you meant a VBA UDF, but Google sheets doesn't support that.

I Mean a custom Vba function
try this link please

projet test deversement.xlsm

drive.google.com

Thank you

 

[mikerickson]

Could you post the code?

 

[kingman29]

Could you post the code?

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(ByVal Charge_Centre_Principal, ByVal Centre1 As String, ByVal Vlrange1 As Range, ByVal Vlcolone As Double, ByVal 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

Thank you

 

[mikerickson]

It sounds like Excel is treating your UDF just like every other function. Calculate at close and the results in the sheet should be saved.

 

[kingman29]

It sounds like Excel is treating your UDF just like every other function. Calculate at close and the results in the sheet should be saved.

I will share another function (without VBA) the resuls never refresh if you double click on Empty cell and press Enter,
and it's save the results after closing
so in my function ,; It looping from the begining after double click on empty cell, so why the function refresh result if I do nothing *
I just double clock on empty cell and press enter,
do you understand me ?
download my file, double click on empty cell and press enter, you will see that D6:K14 will refressh itself