Ask Your Question

Modify named range in Calc [closed]

asked 2013-10-07 11:41:28 +0100

jheath gravatar image

I would like to modify a named range in Calc. This named range is being used as a Source for another cell's Validity selection list. So I'm afraid if I delete the named range and recreate it (which I'm pretty sure I know how to do), this Source will change to #REF, and it will be kind of a pain to get it back. I'm thinking that if there were a way to modify the named range "under the hood", then I wouldn't lose this link in the first place.

While we're at it, let's use this question as a learning opportunity for understanding the API docs. So I have the following:

oNamedRange = ThisComponent.NamedRanges.getByName("DataList")

I know that this gives us a NamedRange so we look in the API docs:

This shows us only 2 Attributes/Properties (TokenIndex and IsSharedFormula). But I know that there are more Properties available, e.g. Content (which gives a String of the absolute reference of the range) and ReferredCells which gives me an XCellRange back that I can use to access the cells in the range. I have used these other Properties in the past, and when I peek into the object with MRI, I see them (and many others) there. So where does the API tell me about those other Properties? I assume they are inherited from other classes, but if open the other links in the little inheritance diagram at the top of the API page, I don't find much information. E.g. under XNamed, it just has functions to get or set the name. XCellRangeReferrer does have a getReferredCells function (but I can't seem to find the CellRangeReferrer page to see the ReferredCalls Property directly), but on the NamedRange page it specifically says that this method only works if the named range contains a single cell range address. (But it seems like I have used ReferredCells for multi-cell ranges before). So where do I find the other Properties shown by MRI? Or am I missing a link on the API page somewhere?

So back to my original question, it seems like when I am looking at the XNamedRange in MRI, there is Content and ReferredCells which point to the range that was named. But I assume I can't just change ... (more)

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-10 21:32:32.668028

2 Answers

Sort by » oldest newest most voted

answered 2013-10-08 14:57:59 +0100

JohnSUN gravatar image

You need a method setContent. Small example

Sub SetNewRangeToNamedRange(nameOfRange, newRange)
Dim oNamedRanges As Variant
Dim oNamedRange As Variant
    oNamedRanges = ThisComponent.NamedRanges
    oNamedRange = oNamedRanges.getByName(nameOfRange)
End Sub

I call it with

SetNewRangeToNamedRange("Short", ThisComponent.getSheets().getByIndex(0).getCellRangeByPosition(2, 4, 2, 25)

and it change my test range "Short"

edit flag offensive delete link more


Thanks! In the API docs, it says setContent method "sets the content of the named range". Does that mean it will change the reference to another place (what I want), or change to content of the named range's current reference to match the given range (more like setData)? Thank you for confirming that it is the former, and what I want. At the moment, my code deletes the named range and recreates it, and that also seems to work (my reference doesn't go to #REF in that short moment).

jheath gravatar imagejheath ( 2013-10-10 09:16:05 +0100 )edit

I have confirmed in my code that setContent does change the range referred to (not the "content" of the current range), so this solves my problem. I have marked the answer as correct. Thank you again JohnSUN!

jheath gravatar imagejheath ( 2013-10-10 11:59:23 +0100 )edit

answered 2013-10-07 16:06:22 +0100

m.a.riosv gravatar image

Instead of delete the range name, modify it through [Ctrl+F3] Menu/Insert/Names/Manage. Change the name and Ok.

edit flag offensive delete link more


Thanks for this suggestion. This is exactly what I want to do, but I'm looking for a way to do it via a LO Basic macro. If I Record a Macro and do the steps you suggested, they don't get properly recorded into the macro (as often seems to be the case), to be copied into my code. So is there a way to do that in a Basic macro?

jheath gravatar imagejheath ( 2013-10-07 17:53:20 +0100 )edit

I can not help with basic. Maybe recording a macro, can help to find what you want.

m.a.riosv gravatar imagem.a.riosv ( 2013-10-07 20:55:48 +0100 )edit

Question Tools

1 follower


Asked: 2013-10-07 11:41:28 +0100

Seen: 5,070 times

Last updated: Oct 08 '13