Page 1 of 1

[Solved] Display cell name

PostPosted: Sat Jun 11, 2016 12:05 am
by wadem101
I would like to show the cell names I "defined" in referenced cells. Something like the FORMULA() function.

For example: A1<--$11.00, A2<--$22.00 and A3<--=SUM(A1:A2) showing "$33.00".
Now -- with cell A3 selected, I do: "Insert - Names - Define. The Define Names (Ctrl+F3)"
... and enter "line20a"
(Note that when cell A3 is selected by itself, "line20a" shows in the range box.)
(Note that when 4 cells are selected A3,A4,B3 & B4, "A3:B4" shows in the range box)

In cell B6, I enter the formula =line20a, now it shows "$33.00".
If I enter =FORMULA(B6) in B7, it shows "=line20a".

Is there a function like CELLNAME(A3), that I could enter in B5, for example,
that would show "line20a" ?

Re: Display cell name

PostPosted: Sat Jun 11, 2016 2:20 am
by acknak
Greetings and welcome to the community forum!

Sorry, I don't know of any way to get access to the name definitions from a formula.

You can insert the names and definitions as text: Insert > Names > Insert > Insert all

Re: Display cell name

PostPosted: Sat Jun 11, 2016 10:25 am
by Zizi64
You can display the "user defined name" of a cell (or a cellrange) by a short macro code:

Code: Select all   Expand viewCollapse view
Function GetCellUDName(sAbsName as String) as string

Dim oRanges as object
Dim oNamedCell as object
Dim iNMCount as integer
Dim i as integer

   GetCellUDName   = "***"
   oRanges = ThisComponent.NamedRanges
   iNRCount = oRanges.GetCount
   For i = 0 to iNRCount-1
      oNamedCell = oRanges.GetByIndex(i)
'Use the XrayTool to display the properties of the objects.
'xray oNamedCell
      if oNamedCell.Content = sAbsName then
         GetCellUDName   = oNamedCell.Name
      end if
   next i
End Function


Example file with the macro:
MyCellName.ods
(11.63 KiB) Downloaded 53 times


Created and tested in LibreOffice 4.4.7

Re: Display cell name

PostPosted: Sat Jun 11, 2016 1:11 pm
by Villeroy
menu:Insert>Names... [Insert All]

Re: Display cell name

PostPosted: Wed Jun 15, 2016 4:42 am
by wadem101
Zizi64's short macro code looks promising.

I downloaded the file "MyCellName.ods". Opening it gave a few problems:
1. I am now running Apache OpenOffice 4.1.2
2. Java (Version 8 Update 91).
3. I've lowered the Tools == OpenOffice -- Security -- Macro Security to "Medium"
4. I had to edit the line: xray oNamedCell with a ' to make it a comment like the CODE: window.

Now it works well enough.
I'll ask Zizi64 how to make it work relative instead of sheet and cell absolute in quotes.

Thank you
I'll mark this as "[Solved]"

Re: [Solved] Display cell name

PostPosted: Wed Jun 15, 2016 6:42 am
by Zizi64
1. I am now running Apache OpenOffice 4.1.2

My macro works in AOO 4.1.2 too.

2. Java (Version 8 Update 91).

The java version is not relevant in this case.

3. I've lowered the Tools == OpenOffice -- Security -- Macro Security to "Medium"

If you copy the macro code into the MyMacros/Standard library, then not need to change the security level, and the macro will available for every spreadsheets. Otherwise the Medium level is safe: you can enable (manually) the running of the macros stored in the documents. You can examine the macro code before it will run.

4. I had to edit the line: xray oNamedCell with a ' to make it a comment like the CODE: window.

You need install and use an object inspection tool (like the XrayTool or the MRI) if you want work with macros and the API. The object inspection tools can display the properties, methods, interfaces of the used objects in your macro code.

Re: [Solved] Display cell name

PostPosted: Wed Jun 15, 2016 6:49 am
by Zizi64
I'll ask Zizi64 how to make it work relative instead of sheet and cell absolute in quotes.


The object "oNamedCell" has a property named "Content". It contains the absolute name of the named cellrange. See the attached picture created by the XrayTool (and by hit Alt-PrtSrcr and Irfan View software)
You need "purge" the name string, if you want use the macros with relative references.

Xray an object.png