Macros/Calc/ba019/es

This page is a translated version of the page Macros/Calc/ba019 and the translation is 2% complete.

TDF LibreOffice en español Document Liberation Project Blogs comunitarios Weblate Nextcloud Redmine Preguntas y respuestas Donar

Other languages:

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.

com.sun.star.sheet.NamedRangeFlag constant group reference
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     ' <span lang="en" dir="ltr" class="mw-content-ltr">The created range.</span>
  Dim oRanges    ' <span lang="en" dir="ltr" class="mw-content-ltr">All named ranges.</span>
  Dim sName$     ' <span lang="en" dir="ltr" class="mw-content-ltr">Name of the named range to be created.</span>
  Dim oCell      ' <span lang="en" dir="ltr" class="mw-content-ltr">Cell object.</span>
  Dim s$

  sName$ = "MyNRange"
  oRanges = ThisComponent.NamedRanges
  If NOT oRanges.hasByName(sName$) Then
    REM <span lang="en" dir="ltr" class="mw-content-ltr">Setting the base address for relative cell references</span>
    Dim oCellAddress As new com.sun.star.table.CellAddress
    oCellAddress.Sheet = 0     '<span lang="en" dir="ltr" class="mw-content-ltr">The first sheet.</span>
    oCellAddress.Column = 1    '<span lang="en" dir="ltr" class="mw-content-ltr">Column B.</span>
    oCellAddress.Row = 2       '<span lang="en" dir="ltr" class="mw-content-ltr">Row 3.</span>

    REM <span lang="en" dir="ltr" class="mw-content-ltr">The first argument is the range name.</span>
    REM <span lang="en" dir="ltr" class="mw-content-ltr">The second argument is a string that defines the formula</span>
    REM <span lang="en" dir="ltr" class="mw-content-ltr">or expression to be used.</span>
    REM <span lang="en" dir="ltr" class="mw-content-ltr">The third argument specifies the base address for</span>
    REM <span lang="en" dir="ltr" class="mw-content-ltr">relative cell references.</span>
    REM <span lang="en" dir="ltr" class="mw-content-ltr">The fourth argument is a set of flags that define</span>
    REM <span lang="en" dir="ltr" class="mw-content-ltr">how the range is used, but most ranges use 0.</span>
    REM <span lang="en" dir="ltr" class="mw-content-ltr">The fourth argument uses values from the</span>
    REM <span lang="en" dir="ltr" class="mw-content-ltr">NamedRangeFlag constants.</span>
    s$ = "$Sheet1.$B$3:$D$6"
    oRanges.addNewByName(sName$, s$, oCellAddress, 0)
  End If
  REM <span lang="en" dir="ltr" class="mw-content-ltr">Get the created named range.</span>
  oRange = ThisComponent.NamedRanges.getByName(sName$)

  REM <span lang="en" dir="ltr" class="mw-content-ltr">Print the string contained in cell $Sheet1.$B$3</span>
  oCell = oRange.getReferredCells().getCellByPosition(0,0)
  Print oCell.getString()
End Sub

This Calc spreadsheet contains the above LibreOffice Basic code.