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

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

[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?

[Villeroy]

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')

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

[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

[Villeroy]

AB => AB This one is OK since this isn't

In Excel AB:AB refers to entire column AB (column #28).

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

[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