Create New Named Range
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Summary
This page provides a LibreOffice Basic macro procedure that creates a new named range referencing $Sheet1.$B$3:$D$6. It is an example drawn from Chapter 13 ("Calc as a Database") of the 7.0 Calc Guide.
Description
In a macro, a named range is accessed, created, and deleted using the NamedRanges service of a Calc document. This service has a number of methods associated with it, the following of which are particularly useful for creating named ranges:
- getByName(Name)
- Returns the range or expression with the specified name.
- getElementNames()
- Returns an array of all named ranges in the current document.
- hasByName(Name)
- Returns a boolean: TRUE if a range with this name is in the current document, and FALSE otherwise.
- addNewByName(Name, Content, Position, Type)
- Adds a new named range to the current document. This method has four arguments:
- Name – A string that contains the name of the new range.
- Content – A string that contains the range address or formula expression being named.
- Position – The base address for relative cell references.
- Type – A combination of flags that specify the type of named range being defined. These flags are listed in the following table. This parameter defaults to zero for any common named range.
| 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. |
As an example, the following code uses the above methods to check if a range named MyNRange exists. If it does not exist, then the macro creates a range with the name and sets it to access the cell range B3:D6.
Code
Sub AddNamedRange()
Dim oRange ' The created range.
Dim oRanges ' All named ranges.
Dim sName$ ' Name of the named range to be created.
Dim oCell ' Cell object.
Dim s$
sName$ = "MyNRange"
oRanges = ThisComponent.NamedRanges
If NOT oRanges.hasByName(sName$) Then
REM Setting the base address for relative cell references
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 a string that defines the formula
REM or expression to be used.
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.
s$ = "$Sheet1.$B$3:$D$6"
oRanges.addNewByName(sName$, s$, oCellAddress, 0)
End If
REM Get 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
This Calc spreadsheet contains the above LibreOffice Basic code.