[Solved] Persistent leading _ range names [Range Functions]

Discuss the spreadsheet application
Post Reply
jslilley
Posts: 4
Joined: Mon Dec 17, 2007 8:26 pm

[Solved] Persistent leading _ range names [Range Functions]

Post by jslilley »

I use range names in Calc for address labels. I was using '=concatenate()' and 'text to columns' to prepare my list for a database and somehow my names beginning with C and R have picked up a leading underscore. ( "_Camp; _Cape; _Road" etc.) I have tried copying and pasting and manually renaming the ranges but the underscore will not go away. I have tried using Insert+Names+Define+Modify but still an underscore! I have tried copying the sheet and pasting onto a new Calc file both as a simple paste and a paste special no formula and still get underscores.

Many thanks to all.
Villeroy's macro did the trick. Now I have to go and delete the '_C...' and '_R...' references and I am back to where I was.
Last edited by jslilley on Fri Jan 04, 2008 4:17 pm, edited 1 time in total.
TerryE
Volunteer
Posts: 1401
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: persistent leading underscore in range names

Post by TerryE »

Any chance of attaching a sample spreadsheet, so that we can have a look? You can probably delete all rows bar one and scramble any cells in the remaining row that you can consider as sensitive?
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
User avatar
Villeroy
Volunteer
Posts: 31292
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: persistent leading underscore in range names

Post by Villeroy »

TerryE wrote:Any chance of attaching a sample spreadsheet, so that we can have a look? You can probably delete all rows bar one and scramble any cells in the remaining row that you can consider as sensitive?
I rolled my own. It is an annoyance if you want to use automatically created names.

jslilley, did you try the alternative method Menu:Insert>Names>Labels?
You access labeled ranges by single quoted labels as in =SUM('Camp')
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
TerryE
Volunteer
Posts: 1401
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: persistent leading underscore in range names

Post by TerryE »

This is what Microsoft calls a "feature by design". In other words it implements a bizarre piece of functionality correctly.

Based on a code scan, the problem is caused because ScDocFunc::CreateOneName is called for each name and Range pair, This in turn validates each name to ensure that it isn't a valid range name (you don't want to define the Range A1 do you?). This is turn parses the name using ScRange::Parse with for three variants CONV_OOO, CONV_XL_A1, CONV_XL_R1C1 to check that this isn't a valid Range name. Note this last one: we are attempting to throw out valid Excel RC notation names. This is done is a local routine in sc/source/core/tool/address.cxx and herein lies your problem.

The scratagy adopted is to replace any : and . in the name with an _. Then if the starting substring is a valid reference then the name is prefixed with a _ character. Hence we have:
  • A1B1XX => _A1B1XX since A1 is a valid reference
    A33B44 => _A33B44 since A33 is a valid reference
    AB => AB This one is OK since this isn't
    R1C1 => _R1C1 This one is a valid RC reference
    RC => _RC So is this one RC means the current cell
    RC[2] => _RC_2_ So is this one; RC is scanned again as above
    RFRED => _RFRED So is this one; R means the current row
    Camp => _Camp So is this one; C means the current column
    Sheet1.23 => Sheet1.23 This one is OK
    Sheet123 => Sheet123 This One is OK
    Sheet2.A1 => Sheet2_A1 This one is converted by an underscore, so is not recognised as a reference and is therefore not given a prefix
Interestingly, in the case of Insert->Names->Define, the entire name is valdated but only against OOo conventions, so you can go in and manually change _Camp (or more strictly insert a copy at Camp, then delete _Camp, etc.)
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
TerryE
Volunteer
Posts: 1401
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: persistent leading underscore in range names

Post by TerryE »

I couldn't find any Issues relating to this, so I have raised a new one:

http://www.openoffice.org/issues/show_bug.cgi?id=84979
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
User avatar
Villeroy
Volunteer
Posts: 31292
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: persistent leading underscore in range names

Post by Villeroy »

TerryE wrote:AB => AB This one is OK since this isn't
In Excel AB:AB refers to entire column AB (column #28).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
TerryE
Volunteer
Posts: 1401
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: persistent leading underscore in range names

Post by TerryE »

Yes, but AB:AB first gets translated to AB_AB and AB_AB is not a valid reference and therefore does not receive the _ prefix. As I said in the Issue: bizarre. The rule makes sense only if the string in entirety would otherwise be a valid reference. Why should In6553x7 receive a _ prefix and In65537x not? I am talking about rational reason here, not "because that's what the algo says." :lol:
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
User avatar
Villeroy
Volunteer
Posts: 31292
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: persistent leading underscore in range names

Post by Villeroy »

Thank you for filing the issue in such detailed and precise manner.
A simple macro to rename the _RC names.

Code: Select all

REM  *****  BASIC  *****
REM rename all named references starting with _c, _r, _C or _R
REM if the new name without leading _ does not exist already.
REM see http://www.openoffice.org/issues/show_bug.cgi?id=84979
Sub fix_RCNames()
	oRefs = thisComponent.NamedRanges
	aNames() = oRefs.getElementNames()
	For i = 0 to uBound(aNames())
		sName = aNames(i)
		if (instr(1, sName, "_r", 1)=1) OR (instr(1, sName, "_c", 1)=1) then
			sNew = mid(sName, 2)
			if NOT oRefs.hasByName(sNew) then
				oRange = oRefs.getByName(sName)
				oRange.setName(sNew)
			endif
		endif
	Next i
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply