Associating a range to a name

From Apache OpenOffice Wiki
Jump to: navigation, search



In a Calc document, a range refers to a contiguous group of cells containing at least one cell. You can associate a meaningful name to a range, which allows you to refer to the range using the meaningful name. You can create either a database range, which has some database-like functionality, or a named range, which does not. A name is usually associated with a range for one of three reasons:

  1. Associating a range with a name enhances readability by using a meaningful name.
  2. If a range is referenced by name in multiple locations, you can point the name to another location and all references point to the new location.
  3. Ranges associated to a name are shown in the Navigator, which is available using the F5 key or clicking on the NavigatorIcon.png icon. The Navigator allows for quick navigation to the associated ranges.

Named range

The most common usage of a named range is, as its name implies, to associate a range of cells to a meaningful name. For example, I created a range named Scores, and then I used the following equation: =SUM(Scores). To create a named range, select the range to define. Use Insert > Names > Define to open the Define Names dialog. Use the Define Names dialog to add and modify one named range at a time.

Define a named range.

In a macro, a named range is accessed, created, and deleted using the NamedRanges property of a Calc document. Use the methods hasByName(name) and getByName(name) to verify and retrieve a named range. The method getElementNames() returns an array containing the names of all named ranges. The NamedRanges object supports the method addNewByname, which accepts four arguments; the name, content, position, and type. The macro in Listing 1 creates a named range, if it does not exist, that references a range of cells.

Listing 1. Create a named range that references $Sheet1.$B$3:$D$6.

Sub AddNamedRange()
  Dim oRange     ' The created range.
  Dim oRanges    ' All named ranges.
  Dim sName$     ' Name of the named range to create.
  Dim oCell      ' Cell object.
  Dim s$
 
  sName$ = "MyNRange"
  oRanges = ThisComponent.NamedRanges
  If NOT oRanges.hasByName(sName$) Then
    REM I can obtain the cell address by obtaining the cell
    REM and then extracting the address from the cell.
    Dim oCellAddress As new com.sun.star.table.CellAddress
    oCellAddress.Sheet = 0     'The first sheet.
    oCellAddress.Column = 1    'Column B.
    oCellAddress.Row = 2       'Row 3.
 
    REM The first argument is the range name.
    REM The second argument is formula or expression to
    REM use. The second argument is usually a string that
    REM defines a range.
    REM The third argument specifies the base address for
    REM relative cell references.
    REM The fourth argument is a set of flags that define
    REM how the range is used, but most ranges use 0.
    REM The fourth argument uses values from the
    REM NamedRangeFlag constants (see Table 2).
    s$ = "$Sheet1.$B$3:$D$6"
    oRanges.addNewByName(sName$, s$, oCellAddress, 0)
  End If
  REM Get a range using the created named range.
  oRange = ThisComponent.NamedRanges.getByName(sName$)
 
  REM Print the string contained in cell $Sheet1.$B$3
  oCell = oRange.getReferredCells().getCellByPosition(0,0)
  Print oCell.getString()
End Sub

The method addNewByname() accepts four arguments; the name, content, position, and type. The fourth argument to the method addNewByName() is a combination of flags that specify how the named range will be used. The most common value is 0, which is not a defined constant value.

Table 2. com.sun.star.sheet.NamedRangeFlag constants.

Value Name Description
1 FILTER_CRITERIA The range contains filter criteria.
2 PRINT_AREA The range can be used as a print range.
4 COLUMN_HEADER The range can be used as column headers for printing.
8 ROW_HEADER The range can be used as row headers for printing.

The third argument, a cell address, acts as the base address for cells referenced in a relative way. If the cell range is not specified as an absolute address, the referenced range will be different based on where in the spreadsheet the range is used. The relative behavior is illustrated in Listing 2, which also illustrates another usage of a named range—defining an equation. The macro in Listing 2 creates the named range AddLeft, which refers to the equation A3+B3 with C3 as the reference cell. The cells A3 and B3 are the two cells directly to the left of C3, so, the equation =AddLeft() calculates the sum of the two cells directly to the left of the cell that contains the equation. Changing the reference cell to C4, which is below A3 and B3, causes the AddLeft equation to calculate the sum of the two cells that are to the left on the previous row.

Listing 2. Create the AddLeft named range.

Sub AddNamedFunction()
  Dim oSheet          'Sheet that contains the named range.
  Dim oCellAddress    'Address for relative references.
  Dim oRanges         'The NamedRanges property.
  Dim oRange          'Single cell range.
  Dim sName As String 'Name of the equation to create.
 
  sName = "AddLeft"
  oRanges = ThisComponent.NamedRanges
  If NOT oRanges.hasByName(sName) Then
    oSheet = ThisComponent.getSheets().getByIndex(0)
    oRange = oSheet.getCellRangeByName("C3")
    oCellAddress = oRange.getCellAddress()
    oRanges.addNewByName(sName, "A3+B3", oCellAddress, 0)
  End If
End Sub

Tip.png Listing 2 illustrates two capabilities that are not widely known. A named range can define a function. Also, the third argument acts as the base address for cells referenced in a relative way.


Select the range containing the headers and the data and then use Insert > Names > Create to open the Create Names dialog, which allows you to simultaneously create multiple named ranges based on the top row, bottom row, right column or left column. If you choose to create ranges based on the top row, one named range is created for each column header—the header is not included in the named range. Although the header is not included in the range, the text in the header is used to name the range.

Define a named range.

The macro in Listing 3 creates three named ranges based on the top row of a named range.

Listing 3. Create many named ranges.

Sub AddManyNamedRanges()
  Dim oSheet    'Sheet that contains the named range.
  Dim oAddress  'Range address.
  Dim oRanges   'The NamedRanges property.
  Dim oRange    'Single cell range.
 
  oRanges = ThisComponent.NamedRanges
  oSheet = ThisComponent.getSheets().getByIndex(0)
  oRange = oSheet.getCellRangeByName("A1:C20")
  oAddress = oRange.getRangeAddress()
  oRanges.addNewFromTitles(oAddress, com.sun.star.sheet.Border.TOP)
End Sub

The constants in Table 3 determine the location of the headers when multiple ranges are created using the method addNewFromTitles().

Table 3. com.sun.star.sheet.Border constants.

Value Name Description
0 TOP Select the top border.
1 BOTTOM Select the bottom border.
2 RIGHT Select the right border.
3 LEFT Select the left border.
Documentation caution.png It is possible to create multiple named ranges with the same name. Creating multiple ranges with a single command increases the likelihood that multiple ranges will be created with the same name—avoid this if possible.


Database range

Although a database range can be used as a regular named range, a database range also defines a range of cells in a spreadsheet to be used as a database. Each row in a range corresponds to a record and each cell corresponds to a field. You can sort, group, search, and perform calculations on the range as if it were a database.

A database range provides behavior that is useful when performing database related activities. For example, you can mark the first row as headings. To create, modify, or delete a database range, use Data > Define Range to open the Define Data Range dialog. When you first define a range, the Modify button shown in the example is labeled New.

Define a database range.

In a macro, a database range is accessed, created, and deleted from the DatabaseRanges property. The macro in Listing 4 creates a database range named MyName and sets the range to be used as an auto filter.

Listing 4. Create a database range and an auto filter.

Sub AddNewDatabaseRange()
  Dim oRange 'DatabaseRange object.
  Dim oAddr  'Cell address range for the database range.
  Dim oSheet 'First sheet, which will contain the range.
  Dim oDoc   'Reference ThisComponent with a shorter name.
 
  oDoc = ThisComponent
  If NOT oDoc.DatabaseRanges.hasByName("MyName") Then
    oSheet = ThisComponent.getSheets().getByIndex(0)
    oRange = oSheet.getCellRangeByName("A1:F10")
    oAddr = oRange.getRangeAddress()
    oDoc.DatabaseRanges.addNewByName("MyName", oAddr)
  End If
  oRange = oDoc.DatabaseRanges.getByName("MyName")
  oRange.AutoFilter = True
End Sub


Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY).
Personal tools