Named Ranges

From Apache OpenOffice Wiki
Jump to: navigation, search

A named range is a named formula expression, where a cell range is just one possible content. Thus, the content of a named range is always set as a string.

Named ranges

The collection of named ranges is accessed using the document's NamedRanges property. A new named range is added by calling the interface's addNewByName() method. The method's parameters are:

  • The name for the new named range.
  • The content. This must be a string containing a valid formula expression. A commonly used type of expression is an absolute cell range reference like "$Sheet1.$A1:$C3".
  • A reference position for relative references. If the content contains relative cell references, and the named range is used in a formula, the references are adjusted for the formula's position. The reference position states which cell the references are relative to.
  • The type of the named range that controls if the named range is included in some dialogs. The type must be a combination of the constants:
    • If the FILTER_CRITERIA bit is set, the named range is offered as a criteria range in the "Advanced Filter" dialog.
    • If the PRINT_AREA, COLUMN_HEADER or ROW_HEADER bit is set, the named range is selected as "Print range", "Columns to repeat" or "Rows to repeat" in the Edit Print Ranges dialog.

The addNewFromTitles() method creates named ranges from header columns or rows in a cell range. The enum parameter selects which named ranges are created:

  • If the value is TOP, a named range is created for each column of the cell range with the name taken from the range's first row, and the other cells of that column within the cell range as content.
  • For BOTTOM, the names are taken from the range's last row.
  • If the value is LEFT, a named range is created for each row of the cell range with the name taken from the range's first column, and the other cells of that row within the cell range as content.
  • For RIGHT, the names are taken from the range's last column.

The removeByName() method is used to remove a named range. The outputList() method writes a list of all the named ranges into the document, starting at the specified cell position.

The service accesses an existing named range. The interface changes the name, and the interface changes the other settings. See the addNewByName description above for the meaning of the individual values.

If the content of the name is a single cell range reference, the interface is used to access that cell range.

The following example creates a named range that calculates the sum of the two cells above the position where it is used. This is done by using the relative reference "G43:G44" with the reference position G45. Then, the example uses the named range in two formulas.

 // insert a named range xDocProp = (
     UnoRuntime.queryInterface(, xDocument);
 Object aRangesObj = xDocProp.getPropertyValue("NamedRanges"); xNamedRanges = (
     UnoRuntime.queryInterface(, aRangesObj); aRefPos = new;
 aRefPos.Sheet = 0;
 aRefPos.Column = 6;
 aRefPos.Row = 44;
 xNamedRanges.addNewByName("ExampleName", "SUM(G43:G44)", aRefPos, 0);
 // use the named range in formulas
 xSheet.getCellByPosition(6, 44).setFormula("=ExampleName");
 xSheet.getCellByPosition(7, 44).setFormula("=ExampleName");
Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages