NEW from "Help! Formulas Disappearing After Every Save" (from 2020)

[corentint]

Hi everyone.

No offence, but sometime I find some of the follow-up questions to a query, not to mention some of the answers, derisive and patronizing.

I follow up here on the longtime ago (2020) question above. (this Thread)

But first, some background:

1. I have been using extensively Excel for 25 years and my files are all xlsm (with the odd special file format xlsb sometimes)
2. I have the latest updated Excel version of Office 365 ( Microsoft® Excel® pour Microsoft 365 MSO (Version 2207 Build 16.0.15427.20182) 64 bits ))
3. I operate under W10 , Version 21H2 sub version 19044.1766 (this might be old by a few days, updates are constrained by week on my PC for sanity reasons).
4. I have a reasonably fast and modern computer, and, at any rate, Excel is super fast on it.
5. I have been programming VBA for my excel work, for almost 15 years. I consider myself no pro, but fluent (last program made contains upward of 5000 lines of code excluding comments).

And here is the simple problem, not exactly as in 2020, but still immensely odd and frustrating:

1. I write in an Excel sheet the follow formula, at the end, but outsidethe named range :
   1. =Max(TotMEB). The named range contains only numeric values. It gives the right answer.
   2. About 5 columns to the right, I write another such: =Max(TotColor) (again outside the named range, all numerics in the range).
   3. Both cells containing the formulae are named ranges consisting of the one only cell. (as a matter of fact, around 95% of cells used in my Excel sheets are named ranges - much more flexible).
   4. Immediately, if I go back to my original formula =Max(TotMEB), I find it replaced by the result as value in the cell that had the formlae!
   5. The format of all cells is the standard form; I do not detect any caracter impeding the formula, which start with =
   6. No save file has been done. But if I save the file, that does not prevent this phenomena to happen, unless, of course, I close and reoppen the file, in which case formulae are still there. Thus this seems to happen when writing several formulae in succession on a sheet.
   7. If I recreate the formula again, it appear to stay put, but NOT allways.
   8. Note that this happens on same sheet, and, between formula editings, I do not navigate elsewhere.
   9. I have also had the case of writing a formula on one sheet, then another one on another sheet, only to find out later that one or several have reverted to values only. And yes, this might happen once or twice in the dead of night, through fatigue, but it cannot be recurring like this all the time, especially when i am aware of it.
2. That being said:
   1. It could be an involuntary keyboard of mouse gesture (although I have no mouse gesture programmed).
   2. I could be lunatic.
   3. However, since this has happened several time in the last year or so, I consider myself no that lunatic, and further I am on the lookout for this sort of shenanigans now
   4. When I spot this happening, and do the correction (sometime twice in row) which appears to eventually work.
   5. If I save and reopen the file, the formulae are still there.
3. However, this had not happened lately until today, and as I edited a sheet for something else, I suddenly realized several of my formulae cells had again been (when?, search me) converted to values.
4. This makes Excel completely unreliable, if this remains pervasive.
5. Now, about that file containing the sheet in question:
   1. There is extensive VBA programming behind it, but the VBA only refers to named ranges to get value, it does NOT change values. (verified)
   2. There are NO VBA sheet events on that sheet.
   3. There are no Userform events refering to anything on this sheet.
   4. The VBA code refers to that sheet only for reading range values i.e. {declared variable x} = range("TotMEB"), VBA does not return a type incompatibillty error, although I fail to see that relevant as it would only be relevant to executing the VBA code and affect the sheet cells.)

The question is therefore: how could this happen in Excel?

Please, no answers of the type "you could simply have VBA calculate the MAX for the range instead of the Excel formula". I have seen that written in the past! If I wanted to do that, I would have done it, duh.
And this is only one example given, I have had other formulae that behave this way from time to time on other sheets, AND in other Workbooks.
The only thing that I can seem to remember peculiar to this is that they are always short formulae, not monster formulae!

Thank you for meditating about this and possibly having some good answers to share!

 

[RoryA]

No offence, but

And yet followed by something likely to give offence...

Anyway, are there any userforms present (loaded or not) in any open workbooks that have controls bound to a sheet using the Controlsource property?

 

[corentint]

Well, very sorry if offence taken, but my extensive searches on forums and sites have consistently yielded flaky comments and answers even if many times, in the repository of anwsers some at least were good to very good. I did not point specifically to the MR Excel forums, which, thankfully, I find reasonnably good during my short tenure here.

Yes, I recently (few days ago) added a textbox which did, exceptionnally use the Control source property to bind a cell from a worksheet. Recently, thus well after noticing my described problem, but still... At least, this is a point of interest and I can certainly fix this by changing it programmatically - and for reference, I do not usually use this control source property , preferring direct program control over data in userforms and objetct.

Why, then, and how could this be a possible source of the described problem?
And by the way, thanks for a quick answer.

Regards

 

[RoryA]

I did not point specifically to the MR Excel forums

No, but you're posting here, and you didn't mention other forums, so the natural inference is that you are talking about this forum.

Why, then, and how could this be a possible source of the described problem?

Because binding a control on a form in such a way will cause exactly the behaviour you describe. The control on the form will be populated with the value of the cell, and in turn that will update the cell to the value of the control, overwriting its formula.

 

[corentint]

Correct for both. I appologize for the first, should have made a precision. So much is crap on the internet that the good stuff get burried in.
I effectively discovered that this binding of userform and cell that I used only a few days ago did in fact erase the formula in the cell - so now I understand this part of the process. I will have to scout all other instances of this (and there are maybe a dozen cells with formulae where I have this problem) and make sure they are not bound using the property. I did not think that I was using this property, but I maybe wrong. Some of the coding was done a year ago. I will let you know.

Regards

 

[RoryA]

Bear in mind it can be caused by any userform in any open workbook, not just in the workbook where the cells are being overwritten. It's the main reason I never use that property.

 

[corentint]

Bear in mind it can be caused by any userform in any open workbook, not just in the workbook where the cells are being overwritten. It's the main reason I never use that property.

Well, thank you again. In effect, I discovered five instances of this property being used by me. All have been cancelled and replaced by more code independant of this property. And I fully agree, this property is dangerous.

Have a nice day.