Forum Discussion

Iron Contributor

Oct 02, 2022

Excel Bug with global relative references

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 ...

ExcelBug2.xlsx9 KB

ExcelBug.xlsm18 KB

Silver Contributor

Oct 03, 2022

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.

ecovonrein
Iron Contributor
Oct 03, 2022
I agree, this link describes the identical bug. However, we see the calculation tree most recently breaking in large Excels without any VBA triggering calculations. (This did not happen until most recent formula changes.) I am fairly certain that the reason our Excels are failing corresponds to the bug exhibited thru the VBA trigger, because the values that randomly pop up in those cells are simply totally implausible.
- JKPieterse
  Silver Contributor
  Oct 03, 2022
  The question is, is there VBA code that runs and hence causes this issue?
  - ecovonrein
    Iron Contributor
    Oct 03, 2022
    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).

Resources