Modify named range in Calc

[jheath]

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:

http://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1sheet_1_1NamedRange.html

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 them; I would need a Method to change the range pointed to by the name. Again in MRI I can look at the various Methods listed for XNamedRange. The things I can set are: setContent, setReferencePosition, setType, setName, setPropertyValue, setTokens. The first 3 of these are described in XNamedRange, but don’t appear to address my need, and the others don’t either. So am I back to needing to delete and recreate the named range, and then fixing up the reference to it in the Validity selection list source?

Thanks for your input, Jeff

[JohnSUN]

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)
	oNamedRange.setContent(newRange.AbsoluteName)
End Sub

I call it with

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

and it change my test range “Short”

[jheath]

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]

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!

[mariosv]

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

[jheath]

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?

[mariosv]

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