[Solved] Use of Calc "Defined Name" in Basic

[Herb40]

There does not appear to be any integration of the Calc "Defined Name" capability with OO Basic; that is, there appears to be no way of accessing a Calc Defined Name value in a Basic macro. Is there some capability that I am just overlooking?

Thanks for the replies. Yes, I was overlooking the capability.

[Lupp]

You will have to use the document's NamedRanges property as in the following gravely reduced example:

Code: Select all

oDoc = ThisComponent
oRanges = oDoc.NamedRanges
oNamedRange = oRanges.getByName("TestName")
a = oNamedRange.getContent()

I never actually used the named ranges from a macro. Read Andrew Pitonyaks texts on the macro topic (the "book" and the "macro document", both available in odt and in pdf from: http://www.pitonyak.org/oo.php) and search for "NamedRanges" therein if still interested. You may face some problems, however, I think.

[FJCC]

Things like this might also be useful

Code: Select all

  oNamedRanges = ThisComponent.NamedRanges
  oObj1 = oNamedRanges.getByName("TheCell")
  oReferredCells = oObj1.getReferredCells()
  

Code: Select all

  oSheets = ThisComponent.getSheets()
  oObj2 = oSheets.getByName("Sheet1")
  oCellRangeByName = oObj2.getCellRangeByName("TheCell")

[Villeroy]

This is how you get the named cell range in most cases without knowing the sheet name:

Code: Select all

Function getNamedCellRange(doc,strName)
xName = doc.NamedRanges.getByName(strName)
getNamedCellRange = xName.getReferredCells()
End Function

The function returns the named cell range even when you move that range, rename or move its containing sheet.
The function returns Null if the range refers to something that is not a absolutely addressed range with 5 $-signs as in $SheetName.$A$1:$X$999 or 3 $-signs for a reference to a single cell as in $SheetName.$A$1.
Any other type of reference with relative elements or without sheet name is ambiguous.
Names may also include constant values, functions and other names.

[Herb40]

As a relatively new Calc user who uses Defined Names strictly for single cells, I never thought of them as being NamedRanges, so that is what made the material in Andy's book difficult to find. For those like me who use NamedRanges in such a limited way, here is what it takes to use them for single cells:

Suppose I have Defined a Name for a cell and called it "Mode". To get or set the value of cell Mode by using its name, I would need something like:

oDoc = ThisComponent
ModeCell = oDoc.NamedRanges.getByName("Mode").getReferredCells().getCellByPosition(0,0)
s = ModeCell.getString() ' or
v = ModeCell.getValue() ' or
f = ModeCell.getFormula() ' or
ModeCell.setString("Howdy") ' or
ModeCell.setValue(v) ' or
ModeCell.setFormula("=SUM(A1:A10)")

You could use the variable name Mode instead of ModeCell if you wished, but is important to remind yourself that what you have is a cell whose contents must be accessed or changed as shown here.