Ask Your Question
1

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
1

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

Comments

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)

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
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 939 times

Last updated: Jul 27 '15