Those anonymous DB ranges are automatically created one per sheet for whenever an AutoFilter or Sort is used on a sheet and no defined DB range was used and remember the settings, as such consider them temporary but stored. They are not supposed to be shown in the UI anywhere, nor are they included in the enumeration of DB ranges because they are handled only internally and not under user control. They can be accessed using the css::sheet::XUnnamedDatabaseRanges interface.
@erAck, I have to use it because of the missing property (there is one in Excel):
Function GetAutoFilterMode(Optional oDoc As Object, Optional nSheet%) As Boolean
''' Returns: True if the AutoFilter drop-down arrows are currently displayed on the sheet.
On Local Error GoTo Failed
Dim oUnnamedDBRanges As Object 'unnamed (anonymous) database ranges
If IsMissing(oDoc) Then oDoc = ThisComponent
If IsMissing(nSheet) Then
nSheet = oDoc.CurrentController.ActiveSheet.RangeAddress.Sheet
End If
oUnnamedDBRanges = oDoc.getPropertyValue("UnnamedDatabaseRanges")
With oUnnamedDBRanges
GetAutoFilterMode = .getByTable(nSheet).AutoFilter 'table -> ScDatabaseRangeObj
End With
Failed:
End Function
For me the problem is not solved at all.
Ubuntu 20.04, Libreoffice 7.6.2
It happened to me recently and many times on a large workbook with about 50 named ranges.
Each time it happened, I could not precisely determine when, how nor why because values in all cells are stored in file and are not recalculated again unless an ancestor cell changed or I explicitly ask to recompute all (Ctrl+Shift+F9 I think).
So each time I realize my file is corrupted, it is too late to investigate further…
Fortunately, I do not update formulae, only values.
So I copy my new values from the corrupted (newer) file to a backup (older) file…
I decided today to downgrade to latest 6.x.x version.
hopefully it can solve (temporarily) the problem.
I don’t much about Calc, but I had a similar problem and found that Calc deleted a RANGE from the list when I deleted the cells in that RANGE. When I CLEARED the cells (instead of DELETING them) there was no longer a problem.
When you use a formula such as =SUM(A1:A8) and you remove cells A1:A8, the formula becomes =SUM(A#REF!:A#REF!) because the referenced cells do not exist anymore. The same happens tp any named range where the cells have been removed.
This is expected spreadsheet behaviour since decades. The application tries to keep track of references when you insert cells, remove cells or when you move cells to another place.
If you don’t want this tracking for some reason, there are ways to evade the feature. For instance =SUM(INDIRECT(“A1:A8”)) sums the range that is described by the address string “A1:A8” and converted to a reference by the INDIRECT function.
Villeroy, I did not mean to imply that there was any problem with CALC. I encountered the unexpected behavior when RECORDING Macros. At the time, I did not know how to ‘clear the cells’, so I deleted them. I now know much better. I just thought that anybody else experiences this unexpected behavior, they might check to see that they may have accidentally deleted the cells in the range, thus causing CALC to remove the RANGE from the list…
It is still in the list of named ranges [Ctrö+F3]. In the attached document I named A1:A8 as “Named” and then deleted A1:A8. “Named” is still in the list of named ranges and in the name box left of the formula bar. It vanished from the navigator.
named_cells_deleted.ods (10.7 KB)