Create New Named Range
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.
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:
- Returns the range or expression with the specified name.
- Returns an array of all named ranges in the current document.
- 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.
|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.
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.