Ask Your Question

How to name a cell in calc using macro

asked 2015-07-25 13:16:23 +0200

gmant gravatar image

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2015-07-25 15:10:11 +0200

doug gravatar image

updated 2015-07-27 13:17:07 +0200

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 (image description) to the left)

edit flag offensive delete link more


The code was working fine but then I updated libreoffice to 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)

gmant gravatar imagegmant ( 2015-07-27 12:08:37 +0200 )edit

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

doug gravatar imagedoug ( 2015-07-27 13:12:50 +0200 )edit

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:


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:


Niki gravatar imageNiki ( 2019-11-28 19:30:14 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-07-25 13:16:23 +0200

Seen: 1,211 times

Last updated: Jul 27 '15