Oct 02 2022 04:36 AM
Oct 02 2022 04:36 AM
This is a desperate attempt to contact Microsoft, whom I seem no longer able to contact in any shape or form. (My ability to log "service requests" as TEAMS admin has vanished.) If any reader here knows how to log bugs with Microsoft, then please let me know / log this bug on my behalf.
Description of the bug:
We have a very complex production environment based on large Excel sheets. After some recent code changes, I now occasionally receive random results from my calculations. An investigation into the issue leads to breaks in the calculation tree. These breaks occur around (named) global relative references, eg A4=CellU1, where CellU1 is defined as “=!A3”.
I have not been able to reproduce the breaking calculation tree in a noddy sheet. HOWEVER, I witnessed very similar behaviour about two years ago when once I used Application.CalculateFull from within VBA. The relative references back then RELIABLY broke. I did not think much of it because Application.Calculate DID NOT produce these errors, so at the time that was good enough for me. And during these past two years I never observed this problem within the normal calculation tree (F9).
I recalled this experience when I recently spent a day investigating this problem through our production environment. I am glad to report that I was able to reproduce the problem again with two very noddy spreadsheets. While the bug generation once more relies on VBA, I think you can see from the astonishing results that the bug itself does not relate to VBA. I think when you trace down the bug I reproduce here, you will also fix the bug that affects our calculation trees.
How to reproduce the bug:
Attached are two tiny spreadsheets. Load ExcelBug2.xlsx into your Excel session. It is trivial. The sheet uses =CellU1 to propagate down “Hello” and “World!”. Nothing remarkable (if we ignore my use of these very arcane references). Now load into the same Excel session ExcelBug.xlsm. Enable Macros. Trust me, the macros here are extremely basic. The Auto_Open function is principally designed to force your Excel into Calculation/Manual and to turn off AutoSave. (This is done in our production sheets for performance reasons.) Whether this has any bearing on the bug is for you to work out.
There is a second macro called “showProblem”. Analyse it (ALT-F11). It simply multiplies INPUT by 2 and then triggers Application.CalculateFull. I have linked this macro to a Button in the spreadsheet. Be brave – press it. As often as you like. You will see INPUT double and OUTPUT will show the right result.
Now switch back to ExcelBug2 within the same session. It too recalculates whenever you press the Button. I get this picture:
This is of course total garbage. When I press ALT-CTRL-F9 (ie trigger a full recalc from the keyboard), I get:
This is the expected result. (A simple F9 will not find there, since Excel thinks it is all done calculating.)
Evaluation of the bug:
Looking at the garbage, it would seem to me that CellU1 in ExcelBug2 is evaluated within the wrong worksheet context. It looks like these relative references find themselves referring to the sheet from within which the calculation was started when their anchor reference should of course be their own cell coordinates (inc worksheet). (I have not found out which random source our spreadsheets draw from when pressing F9.)
Work-around for this bug:
The desired functionality can be retained and the integrity of the calculation restored by demoting the global name into the local scope of each sheet using it. That is, we must replicate the same definition umpteen times over within a workbook. I developed a VBA script to do this across our domain. Not pretty, but needs must.
Please fix with urgency. Thank you.
Oct 03 2022 01:46 AM - edited Oct 03 2022 01:47 AM
@ecovonrein I'm afraid this is a known bug. I have reported this decades (!) ago with no effect. I think I can safely say this bug is not going to be fixed. Here's a mention (by me) in my range names article from 2010 http://web.archive.org/web/20100625131543/http://www.jkp-ads.com/Articles/ExcelNames10.asp
My advice: Do not use range names using this structure unless there is NO VBA code triggering calculations.
Oct 03 2022 01:50 AM
@ecovonrein Further search has proven that I've mentioned this issue as far back as November 2003: http://web.archive.org/web/20031213212213/http://www.jkp-ads.com/ExcelNames.htm
Oct 03 2022 03:33 AM
@Jan Karel PieterseThanks. This is greatly discouraging. But perhaps my post was misleading. I can reproduce the problem using VBA. The actual problem however is within regular Excel. We recently get random numbers from just pressing F9 - no VBA interaction at all. It is positively scary.
Oct 03 2022 03:39 AM
Oct 03 2022 05:48 AM - edited Oct 03 2022 06:06 AM
No. There is no VBA event code "onCalculate". Just plain F9, totally random errors. It is intolerable, frankly. My feeling is that the VBA trigger example is just a reliable way to highlight an underlying bug. I don't know what my spreadsheets do to provoke this bug, but it does not involve VBA macros (eg Application.CalculateFull, which I use in my demonstration).
The calculation tree does involve VBA *functions* (UDF).
Oct 03 2022 06:42 AM - edited Oct 03 2022 06:43 AM
What do those UDF's look like? I could not reproduce a UDF to cause this bug.
However, even if there is no OnCalculate code, *any* VBA code that triggers a calculation (such as changing a cell value) will cause this bug.
Oct 03 2022 06:49 AM - edited Oct 03 2022 06:49 AM
I hear you. And there is none such. We have many UDFs but they are perfectly harmless PUBLIC FUNCTION(...) AS VARIANT. I agree with you and am similarly unable to reproduce this bug other than by resorting to Application.CalculateFull. But I avoided this stuff and it is no use: the error is now with us in the regular calculation tree. We have no macros (that change cells). It is extremely scary. (I have one enterprise value hopping from 3 mio to 5 mio at random.)
What is still more annoying that there appears to be no route to get Microsoft to acknowledge the bug and track progress.
Oct 03 2022 06:53 AM - edited Oct 03 2022 06:53 AM
Sending a frown from Help, feedback does find its way to MSFT. Include an email address so they can reach out to you.
Oct 03 2022 06:54 AM
Oct 03 2022 07:03 AM
Oct 03 2022 07:11 AM - edited Oct 03 2022 07:18 AM
"The bug only happens once a VBA change (or a VBA calculate command) triggers the name(s) to recalculate." That's what I thought. I assure you, it doesn't. Again, we use no macros (after Auto_Open). We use a variety of UDFs, and have done for years. Lots of loan calculations etc. The environment is very complex (involving many large spreadsheets) and hence not of the "let me show you" variety. Yet even within our own environment, the error is not predictable. We value companies, each company is a sheet and all the sheets are the same. Which sheet might fail at any one point, we cannot say.
The fracture is always in the same place, though. There is a cascade of cashflows that propagates via a row of formulas =CellU3. Sometimes, for the first 4 columns Excel pulls down the right numbers (from 3 rows up). Then, from column 5 to 25, there is nothing. Completely blank. I can press F9 all I like, nothing happens. Then I go into column 5 and press F2-Enter. The right number appears. Often, when I now press F9, Excel suddenly "realizes" that it must recalculate the formulas in that row, and all is well after. But I had occasions where I needed to press F2-Enter on EVERY SINGLE cell in that row before I got the correct result. It is crazy.
PS: I hope to have fixed this issue now with the work-around I mention. Demoting CellU3 into each local scope seems to fix the issue - fingers crossed. Which ties in with my evaluation of the origins of this bug, which appears to be the wrong worksheet context. By demoting the global names, I am now explicitly supplying the worksheet context.
Oct 04 2022 02:48 AM