Named ranges disappear in Calc

English
#1

Thanks for any help :slight_smile:

I had dozens of named ranges in a large spreadsheet with multiple sheets. Today there are only 2. I’ve lost 4 or 5 at a time in the past, never this many at once. Any ideas why they disappear? They no longer show up in “Manage Names” dialog. There were both global and sheet-specific ranges. Formulas show “#NAME?” error, no way to tell which name was used where.

Am I exceeding some practical limit to size of a spreadsheet or number of text boxes, formulas, charts, sheets, number of named ranges, number of references to named ranges, formatting, etc.? Thousands of formulas are affected, hundreds of them are unique (not the same from row to row). This has been going on for a few years in the main spreadsheet I use, costing a few hours per month. I have not even tried to use named ranges in other LO spreadsheets (used them a LOT in Excel and never had this problem). Is it best to avoid them altogether?

If my spreadsheet is corrupted, any advice on how to salvage what I can and re-create my spreadsheet in a new file? I also lose values from random cells once in a while. I’ve run extensive memory and disk tests on my PC and other programs work fine. I’m using LO 7.2.6 (recently upgraded to this version from 7.2.5, maybe that caused most names to be lost?)

I’m on Win 10. LO with this problem spreadsheet and another large one is only using 135 MB of memory. The spreadsheet on disk is 2.32 MB.

#2

In which file format do you save?

#3

I have exercised Named Ranges and Named Expressions to a practical extreme in LO 7.3 on Win 10 without particular problems. There are practical limits to how deeply you can reference Named Expressions within one another (especially if you want the sheet to fully calculate on load or with a single Ctrl+Shift+F9), but I haven’t seen any disappearing names.

If you rename a copy (for safety) of your ODS as a ZIP and look at content.xml within that zip folder, you’ll see that Named Expressions are stored in an obvious node:

<table:named-expressions>
   <table:named-range table:name="TheName" table:base-cell-address="$Sheet1.$B$3" table:cell-range-address="$Sheet1.$B$3"/>
</table:named-expressions>

That might be one place to look for corruption.

How to list all named ranges including those with sheet scope?
#4

This file has always been saved in .ods format. Is there a more appropriate format? I normally don’t have a reason to share this file or others with anyone else.

1 Like
#5

Joshua4 – Thanks, not sure what I’m looking for but both the remaining named ranges appear to have the same syntax as your example. I thought I might find some incomplete entries, but only two instances matching the two names I see in the “Manage Names” dialog within the program. One is about 1/3 of the way through the file, the other is close to the end. I don’t think I’m doing any nesting of ranges but often have multiple or repeated ranges within a single formula.

#6

No. ODF .ods is the native file format. I think the question was more targeted against whether you save in alien old binary .xls format for example, where limits could had been hit.
If you always saved in .ods I also don’t think it got anything to do with the file format (unless something was saved broken and thus wasn’t read back in the next time).
Anyway, without the proper file and any history you could remember what may have gone wrong when working with the document there’s not much to say. Also “I also lose values from random cells once in a while” sounds very odd. You do not by chance share the document with two instances of programs (any, LibreOffice or others) that try to work on it simultaneously and cross each other?

#7

erAck, thanks for you help. I only use LO Calc to edit the file. And I’ve never edited two instances at once :slight_smile:

Over time it seems I’ve just come to expect data loss from LO, and hope I notice it while I can still recover from backups. But losing dozens of named ranges and the hundreds of formulas that depend on them is simply more than I want to try to fix. It seems the problem is unique to me, so whether it something else on my PC or something about how I use LO, it isn’t working for me.

So I’m once again looking for a good alternative to MS Office, which I also quit using due to numerous repeatable-but-never-fixed bugs, and no way to report them for either product, as a simple end user. Last time I tried to report what I thought was a repeatable bug in LO I got chastised by support staff (as in ‘quit complaining about problems, it’s free and they don’t pay us enough to care’), so never again!

#8

Could you please provide a bug number, or another reference to that exchange? That sounds bad.

Note that there is no “support staff” in LibreOffice project at all. The project is run by volunteers; most of the time, when people think they talk to “staff”, they talk to other users, who have their own points of view (some rare times to developers, who also reply as volunteers, and whose PoVs are their own, not necessarily reflecting “official project PoV” - if that kind of thing ever existed); and people tend to blame the project, when it’s their fellow users who disagree - an irony.

1 Like
#9

I, too, have a similar long-standing problem, only comments to cells are lost: disappear chaotically and without trace forever. The cause is not figured out.
But I have learned how to deal with it. So let’s do it this way.
Create an extra sheet and fill in the table with the range name, reference (range of formula expression), and scope (and range options: NamedRangeFlag Constants)*. Let’s create a little macro that will quickly restore all the names according to your table. Save it in the ‘My Macros & Dialogs’ library.
If you are interested in this method, I will suggest a macro.
It’s a half measure. The problem will be solved someday, but you don’t have to leave LibreOffice.

* You can automate this process of name copying as well.

#10

mikekaganski: Thanks for the explanation about the “support staff”! Sorry, that was probably 2 years ago. I no longer have a record of it. As I recall, the issue I found was not resolved, but while exchanging messages with whoever was handling the bugzilla process, I added a list of about 10 other issues, trying to be helpful, hoping they would someday be fixed. That sent the other person over the edge and resulted in the rather insulting reply I got.

I understand it’s volunteers and appreciate the help they give. I’ve been able to contribute some donations and still do that, but my experience with bug reporting caused me to quit trying to help improve LO.

#11

Yes, please do, if it’s a straightforward task. I use named expressions extensively, and I’d like the extra security. Plus, I can see some modified use cases.

For my part, here is how to dump the existing ranges to start the table:

Sub TableOfGlobalNamedRanges()
	Dim Document As Object
	Dim OutputSheet As Object
	Dim NamedRanges As Object
	Dim OutputAddress As New com.sun.star.table.CellAddress
	Dim Index As Integer

	Rem GlobalScope.BasicLibraries.loadLibrary("XrayTool")

	Document = ThisComponent
	NamedRanges = Document.NamedRanges
	OutputSheet = Document.getSheets().getByName("NamedRanges")
	OutputAddress = OutputSheet.getCellByPosition(0,0).CellAddress

	NamedRanges.outputList(OutputAddress)

End Sub

This is only for global named ranges. Unfortunately, it looks like outputList always outputs global ranges, even if called on the named range collection of a specific sheet. I’ll see if I can’t post an iteration over the ranges scoped to a specific sheet later on.

2 Likes
#12

eeigor: Thanks, I think I see where you’re going with this, a quick way to restore named ranges that get deleted.

While your macro would be helpful, it only resolves the small part of the problem. I would still need to restore all the formulas that depended on the ranges. That needs a manual compare with an older copy of the file, cell-by-cell for each formula with an error, and right now would take many hours if not days. So I’ve decided that named ranges are simply not worth the effort and will never use them again. I’m deleting all the formulas that depended on them. I’ll live without the information those formulas provided, or re-develop some of them only as needed. Sad, because named ranges make formulas so much easier to write, read and debug.

Like you, I have also lost comments with no clue as to the cause and no pattern as to location or sheet or type of cell. I just notice they’re missing. So that’s also something I’ve been using less and less. Thinking about ways to keep comments in a separate text file, but that seems a big, needless, step backward.

So, having to find work-arounds for basic features, or remembering which features to avoid, and still having basic corruption like cell contents vanishing… none of that makes me want to start anything new with LO except very simple, short-term tasks.

It seems like the people here are helpful and I would like to contribute also, but I think it’s time to move on to a program that works.

#13

Yes, almost. ScNamedRangesObj calls ScDocFunc::InsertNameList, and the latter outputs the named ranges visible on that sheet - first sheet-local, then global. The XNamedRanges object itself has no effect here - likely an artifact from times when there was only global set of named ranges. I would suppose that now it would be possible to change that (or extend the interface, with some XNamedRanges having InsertNameList2).

Have you filed a bug report yet?

#14 
Sub Test_ShowLocalRangeNames()
'''	Sheet level names.

	Dim sh As Object, rg As Object
	Dim sh_names$()  'document sheet names
	Dim rg_props$()  'name & content of sheet (local) named ranges
	Dim i&, j&
	Dim s$

	ReDim sh_names(ThisComponent.Sheets.Count - 1)
	With ThisComponent.Sheets
		For i = 0 To .Count - 1
			sh_names(i) = .getByIndex(i).Name
		Next
	End With
	Call SortArrayInPlace(sh_names)

	s = "Sheet Name – NamedRange Name – Content:"
	For i = 0 To UBound(sh_names)
		sh = ThisComponent.Sheets.getByName(sh_names(i))
		If sh.NamedRanges.Count Then
			s = s & Chr(10) & sh.Name & ":"

			ReDim rg_props(sh.NamedRanges.Count - 1, 0 To 1)  'Name & Content
			With sh.NamedRanges  'stored sorted by name
				For j = 0 To .Count - 1
					rg = .getByIndex(j)
					rg_props(j, 0) = rg.Name
					rg_props(j, 1) = rg.Content
					s = s & Chr(10) & Chr(9) & rg_props(j, 0) & Chr(10) & Chr(9) & Chr(9) & rg_props(j, 1)
				Next j
			End With
			Erase rg_props
		End If
	Next i
	MsgBox s, Title:=ThisComponent.Title
End Sub  'Test_ShowLocalRangeNames

Function SortArrayInPlace(ByRef aIn())
'''	Sorts the given array in ascending order, case insensitive (A to Z), in place.
'''	Argument:
'''		aIn(): 1D input array.

	On Local Error GoTo Failed
	Dim i%, j%, tmp

	' Sort it.
	For i = LBound(aIn) To UBound(aIn)
		For j = i + 1 To UBound(aIn)
			If UCase(aIn(i)) > UCase(aIn(j)) Then
				tmp = aIn(j): aIn(j) = aIn(i): aIn(i) = tmp
			End If
		Next j
	Next i
	SortArrayInPlace = True

Failed:
End Function  'SortArrayInPlace

NOTE: Range names are stored in sorted form, but sheet names have to be ordered independently.

Other props

1 Like
#15

I need some time to grasp the goal of this grumpy ~60locs…
basically the same as:

doc = XSCRIPTCONTEXT.getDocument()
out = []
for sheet in doc.Sheets:
    out.append((sheet.Name, [(entry.Name, entry.Content) for entry in sheet.NamedRanges]))
print(sorted(out))
#16

There are no a list comprehension (or generator) & sorted function (or list.sort method to sort in place) in LO Basic. But you got the idea right: Name & Content.

#17

And here it is for both Global- and Sheet-scoped named expressions. This dumps onto a single sheet in column pairs starting with global and moving across each sheet, which @eeigor even has alphabetized.

OutputNamedRanges.ods (15.1 KB)

Unfortunately, it sounds like the OP says even the name formulas within each of the cells using named ranges disappear. If that is the case, well, this may be more useful for project development than as a backup. I guess I’ll be even more vigilant about file backup until I’m satisfied the OP has some particular unfortunate file corruption.

#18

No, they (names) are replaced by #REF! in formulas, I suppose.

#19

Actually the name itself should be preserved but the formula result is #NAME? and if a named expression with the same name is created the formula is recompiled and works again.

#20

erAck: I can confirm that recreating a named range with the same name and scope, does NOT fix the formulas that have the #NAME? error (at least not in my file). The formulas contain #NAME? in every place that a lost named range was referenced. I opened a copy of the .ods file renamed to .zip, and searched for some of the missing range names. They do not appear in the file. So you had an interesting idea that gave me hope, thanks! I do appreciate all the ideas and discussions here :slight_smile:

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.