How to name a cell in calc using macro

[gmant]

Is there any way to give a cell a defined name using a macro?

[doug]

The solution requires familiarity with the naming functionality in Calc. In short, this is a ThisComponent level property. Here is how to add new named elements via Basic macro:

oSheets = ThisComponent.Sheets
oSheet = oSheets.getByIndex(0)
oRange = oSheet.getCellByPosition(0,0)

tcNamedRanges = ThisComponent.NamedRanges
tcNamedRanges.addNewByName("just_first_cell", oRange.AbsoluteName, oRange.getCellAddress(), 1)

The third argument expects the Cell address. Should be possible to name a range. Change the arguments as follows; note only changing second argument:

oRange = oSheet.getCellByPosition(0,0)
oRangeSpan = oSheet.getCellRangeByPosition(0,0,1,1)
tcNamedRanges.addNewByName("first_two_cells", oRangeSpan.AbsoluteName, oRange.getCellAddress(), 1)

For API reference to determine arguments, see this page.

You can then call the range as follows:

oSheets = ThisComponent.Sheets
oSheet = oSheets.getByIndex(0)
oRange = oSheet.getCellRangeByName("just_first_cell")
MsgBox oRange.AbsoluteName

The range also is visible in Insert → Names → Manage

edit: added parentheses to methods per comment.

(if this answers your question, please accept the answer by clicking the check () to the left)

[gmant]

The code was working fine but then I updated libreoffice to 4.4.4.3 and started to get a runtime exception on the following line:

tcNamedRanges.addNewByName(“just_first_cell”, oRange.AbsoluteName, oRange.getCellAddress, 1)

Adding Parentheses to getCellAddress fixed the runtime error.

tcNamedRanges.addNewByName(“just_first_cell”, oRange.AbsoluteName, oRange.getCellAddress(), 1)

[doug]

Did not encounter the issue testing on LO 5 rc3, but yes, that is a method so parentheses are in order.

[Niki]

I wanted to access the named range from another workbook within a formula and struggled for a bit so here is how it works.
Referencing another LibreOffice Calc file works as follows:

=‘file:///C:/My%20Folder/Some%20Folder/File.ods’#$Sheet1.A58

It is obvious that the filename is in single quotation marks. The hashtag references cells in the specified file. The cells can be referenced using the syntax Sheet.A1 or directly with the named range:

=‘file:///C:/My%20Folder/Some%20Folder/File.ods’#just_first_cell