The Insert → Names → Insert → Insert All dialog can export a list of the defined names in a spreadsheet, say names in column A and Assigned To values in column B. Unfortunately the Insert → Names → Create dialog can't use that list to import the saved names, since it uses the addresses of the cells in column B, not their values, when creating the names in column A. The Create dialog only builds names for single cells, not for ranges, nor for formula expressions.
As an attempt to improve the situation, I have created two Basic macros ExportDefinedNames and ImportDefinedNames. When run from a spreadsheet, ExportDefinedNames creates a new sheet in it, DefinedNames, showing all the names which have been created with the Insert → Names dialog. This is a fairly straightforward macro which shows each name with its reference or formula. This list may be helpful in understanding names in a spreadsheet created by someone else.
The Insert → Names → Define dialog always suggests an absolute reference to the active cell as the Assigned To value, so many beginners don't realize that defined names can use relative references just like formulas can. The name's value is relative to the cell which was active at the time Insert → Names → Define was called. When ExportDefinedNames runs, it saves the position of each definition's active cell in the Sheet, Column, and Row fields of the export. A name can have type attribute, assigned with Insert → Names → Define → More. The unusable list created by the Insert → Names → Insert → Insert All dialog does not show the active cell nor the type.
Someone who understands how defined names work can edit the DefinedNames sheet, changing the names or their attributes. Of course it would be wise to save an unaltered copy of that sheet before editing. Rows can edited, added, or deleted to change the list of saved definitions. Then ImportDefinedNames can read the updated list. It deletes every defined name in the spreadsheet, then loads the new definitions. The Sheet, Column, and Row fields are used during import to restore the name with the proper active cell reference.
After running ImportDefinedNames with altered definitions, the names are updated, but any formulas which used them are not. For example, if name HrWk is changed to the more descriptive HoursForWeek, formula =MIN(HrWk;40) is not changed and will produce a #NAME? error until it's corrected, either by retyping it or by using Edit → Find & Replace. The very same problem is present when updating the HrWk name with Calc's Insert → Names → Define dialog. I don't know of any good solution except intelligent use of the Find & Replace dialog. Find & Replace will be simple to use if the names which have been created consist of characters which will not otherwise appear in a formula or value. Replacing SA or sa will be difficult. Replacing _SA_ or _sa_ will be easy.
In theory, some SuperDuperImportDefinedNames macro could examine and change cell formulas. However that macro would have to parse the formulas to determine if formula text with characters which match the name is, in fact, that name. If name SUM is being replaced by TOTAL, the macro shouldn't replace function SUM() with TOTAL(), nor name SUM..1 with TOTAL..1, nor text string """""&SUM&""" with """""&TOTAL&""". But it should replace concatenation expression """"""&SUM&"""" with """"""&TOTAL&"""". Changing formulas by macro seems to me to be a quite complex task.
I have done a bit of testing to see what happens when edits to the rows create unsuitable defined names. For example, names are not allowed to match cell addresses; the name A1 is not allowed because Calc could not tell if formula =A1 refers to the cell or the name. However the addNewByName API method provided for creating names with a macro seems to accept A1, and creates an unusable defined name. I haven't tested to see what happens when the type or active cell positions are invalid, for example, non-numeric, or how the method handles bogus formulas like )/F987654321:. I'll just say: Expect trouble if bad edits create improper names or name attributes. Garbage in, garbage out
Here is a listing of these macros. After reviewing comments in this topic I will create a new topic for these macros in the Code Snippets forum.
Rem Sheet Defined_Names has six columns:
Rem A = 0 B = 1 C = 2 D = 3 E = 4 F = 5
Rem 1 "Date" Date " -- Active Cell -- " "Count="N
Rem 2 "Name" "Type" "Sheet" "Column" "Row" "Formula"
Rem n Name Type Sheet Column Row Formula
Rem Type is a combination of FILTER_CRITERIA=1 PRINT_AREA=2 COLUMN_HEADER=4 ROW_HEADER=8;
Rem For import of the the Defined_Names sheet following rules apply:
Rem The data should be a contiguous range of non-empty cells beginning in A1,
Rem for example, like the layout of the sheet created by ExportDefinedNames
Rem Rows 1 and 2 are ignored, Columns past F are ignored
Rem Name should be a valid defined name; Type should be 0 to 15
Rem Sheet, Column, and Row should be integers
Rem Formula should be a cell, cell range, or suitable Calc formula expression
Option Explicit
' V1R1M0 2025-10-27
Const NAMES = "DefinedNames" ' Name of names sheet
Const CN = 0 : Const CT = 1 : Const CS = 2 ' Subscripts for Name, Type, Sheet
Const CC = 3 : Const CR = 4 : Const CF = 5 ' Subscripts for Column, Row, Formula
Sub ExportDefinedNames ' Create names of merged cell ranges
Const NODEL = "Unable to delete sheet " ' Error message
Const NOINS = "Unable to insert sheet " ' Error message
Dim sheets As Variant ' Sheets in document {Spreadsheets}
Dim nsheet As Variant ' Names sheet {SheetCellRange}
Dim view As Variant ' Document view {SpreadsheetView}
Dim dnames As Variant ' Defined names {NamedRanges}
Dim dname As Variant ' A defined name {NamedRange}
Dim ca As New com.sun.star.table.CellAddress ' Address of the cell: Sheet, Column, Row
Dim da() As Variant ' Data array for defined names sheet
Dim n as Long ' Count of defined names
Dim d As Long, d2 As Long ' Indices for defined names
sheets = ThisComponent.Sheets ' Sheets in spreadsheet
If sheets.hasByName(NAMES) Then sheets.removeByName(NAMES) ' Delete any existing sheet called "DefinedNames"
If sheets.hasByName(NAMES) Then MsgBox NODEL & NAMES : Exit Sub ' Exit if unable to delete sheet
sheets.insertNewByName(NAMES,sheets.Count) ' Add names sheet at end
If Not sheets.hasByName(NAMES) Then MsgBox NOINS & NAMES : Exit Sub ' Exit if unable to insert sheet
nsheet = sheets.getByName(NAMES) ' Names sheet
dnames = Thiscomponent.NamedRanges ' Defined names in spreadsheet
n = dnames.Count : ReDim da(n+1,5) ' Get count; Set dimensions for data
da(0,0) = "Date" : da(0,1) = Format(Date,"YYYY-MM-DD") ' First header row in new sheet ...
da(0,2) = " " : da(0,3) = " " : da(0,4) = " " ' ... as a contiguous non-empty ...
da(0,5) = "Count=" & Format(n,"0") ' ... range as required by ImportDefinedNames
da(1,CN) = "Name" : da(1,CT) = "Type" : da(1,CS) = "Sheet" ' Second header row ...
da(1,CC) = "Column" : da(1,CR) = "Row" : da(1,CF) = "Formula" ' ... in sheet
For d = 0 to n-1 : dname = dnames(d) ' Loop through defined names
d2 = d+2 : ca = dname.ReferencePosition ' Array row; Reference sheet/column/row
da(d2,CN) = dname.Name : da(d2,CT) = dname.Type ' Store data ...
da(d2,CS) = ca.Sheet : da(d2,CC) = ca.Column ' ... from defined name ...
da(d2,CR) = ca.Row : da(d2,CF) = dname.Content ' ... in data array
Next d ' Done with defined names
nsheet.getCellRangeByPosition(CN,0,CF,d2).DataArray = da ' Put data in sheet at A1:Fn
For d = 0 to 5 ' Loop through columns
nsheet.Columns(d).OptimalWidth = True ' Set optimal width for column
Next d ' Done with columns
nsheet.getCellRangeByPosition(CS,0,CR,0).merge(True) ' Merge C1:E1 (above Sheet/Column/Row)
nsheet.getCellByPosition(CS,0).String = "-- Active Cell --" ' Put merged cell heading in C1
view = ThisComponent.CurrentController ' Document view
view.ActiveSheet = nsheet ' Point view at names sheet
view.freezeAtPosition(CN,2) ' Freeze view at A3
End Sub
Sub ImportDefinedNames ' Create defined names from sheet data
Const NODNS = "Spreadsheet doesn't have sheet " ' Error message
Const MULTR = "Use only one contiguous non-empty cell range in sheet " ' Error message
Const NOTA1 = "Range doesn't begin at A1 in sheet " ' Error message
Dim sheets As Variant ' Sheets in document {Spreadsheets}
Dim nsheet As Variant ' Names sheet {SheetCellRange}
Dim ranges As Variant ' Set of ranges with data {SheetCellRanges}
Dim range As Variant ' One of the ranges with data {SheetCellRange}
Dim cell As Variant ' A cell in a range {Cell}
Dim dnames As Variant ' Defined names {NamedRanges}
Dim ca As New com.sun.star.table.CellAddress ' Address of the cell: Sheet, Column, Row
Dim cra As New com.sun.star.table.CellRangeAddress ' Address of the range cell: Sheet, Column, Row
Dim drows() As Variant ' Rows from defined names sheet (Objects)
Dim dcols() As Variant ' Columns of row (Doubles or Strings)
Dim d as Long, e As Long ' Row positions
sheets = ThisComponent.Sheets ' Sheets in spreadsheet
If Not sheets.hasByName(NAMES) Then MsgBox NODNS & NAMES : Exit Sub ' Exit if unable to locate sheet
nsheet = sheets.getByName(NAMES) ' Names sheet
With com.sun.star.sheet.CellFlags ' Access cell flags
ranges = nsheet.queryContentCells(.VALUE+.STRING+.FORMULA) ' Get used cells
End With ' Done with cell flags
If ranges.Count > 1 Then MsgBox MULTR & NAMES : Exit Sub ' Exit if more than one range in sheet
range = ranges(0) : cra = range.RangeAddress : e = cra.EndRow ' Get its location and end row (offset 0)
If cra.StartColumn <> 0 Or cra.StartRow <> 0 Then ' Unexpected data layout
MsgBox NOTA1 & NAMES : Exit Sub ' Display message and exit
End If ' End layout check
dnames = Thiscomponent.NamedRanges ' Defined names in spreadsheet
For d = dnames.Count-1 to 0 Step -1 ' Remove all named ranges
dnames.removeByName(dnames(d).Name) ' Remove one defined name
Next d ' End of named range loop
If e < 2 Then Exit Sub ' No defined names to add
drows = nsheet.getCellRangeByPosition(CN,2,CF,e).DataArray ' Get row array from A3:Fn
For d = 0 to e-2 ' Loop through row array
dcols = drows(d) ' Get the row's column array
ca.Sheet = dcols(CS) : ca.Column = dcols(CC) : ca.Row = dcols(CR) ' Create cell address structure
dnames.addNewByName(dcols(CN),dcols(CF),ca,Clng(dcols(CT))) ' Create defined name
Next d ' End row loop
End Sub