[Solved] Request for comments: Macros for defined names

[MrProgrammer]

I have thought that it would be helpful to have a way to manage defined names in Calc using spreadsheet tools like Find & Replace, Text to Columns, spreadsheet functions, etc. rather than using the basic Insert → Names → Define dialog. Using the dialog, names must be edited one at a time. There is no provision for changing all of them at once according to some pattern, say removing sheet names.

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.

Edit: 2025-11-15: Remove first draft of macros to ensure people don't use it.  See Macros for Defined Names topic.

[Alex1]

I tried the macros in LibreOffice with non-English language settings and they seem to work as expected.
On import with only absolute references the active cell in the table doesn't seem to matter.
On my first attempt to import, an unspecified error occurred and the DefinedNames sheet was cleared. I couldn't reproduce this.

[MrProgrammer]

I tried the macros in LibreOffice with non-English language settings and they seem to work as expected.

Thank you for testing them in LibreOffice. I only tested with OpenOffice. I hadn't considered the language setting when I was writing the macros. Perhaps the only change needed for Spanish, French, German, etc. would be to translate the English error messages or to use a different name for the DefinedNames sheet and the column headings in it.

On import with only absolute references the active cell in the table doesn't seem to matter.

This makes sense to me. When using Import → Names → Define, the active cell position shouldn't affect an absolute reference with $Sheet.$Column$Row, since it has no relative references which need to know the active cell. And this type of definition won't need to use active cell positions when created by ImportDefinedNames. I believe in both cases the name definition still stores the active cell positions; it just doesn't use them.

On my first attempt to import, an unspecified error occurred and the DefinedNames sheet was cleared. I couldn't reproduce this.

Hmmm. I don't see how the ImportDefinedNames subroutine can alter any cells. It clears the Thiscomponent.NamedRanges entries (the Remove all named ranges loop) but shouldn't affect cell contents. If an error occurred at the beginning of the addNewByName loop, this would have the effect of clearing the name definitions but not rebuilding them from the spreadsheet rows, however it shouldn't clear the sheet.

I tried a bit to see if the method would tolerate bad data in the DefinedNames spreadsheet rows and it always accepted what I had, even though the resulting name definition was unusable. However I didn't try setting column A (the name) to spaces, or giving it bad active cell positions, or, setting the type to (say) -42, or other bogus data. It's very possible this mischief can cause the subroutine to fail with an unexpected error. It may be hard to deal with unexpected errors. My experience has been that trapping, in Basic using ON ERROR GOTO label, works fine when Basic detects the error, but it is unreliable for errors which are detected inside UNO API calls.

Running ExportDefinedNames by accident instead of ImportDefinedNames would clear the DefinedNames sheet and rebuild it with known name definitions, losing any edits which had been made.

[cwolan]

For now: just two minor comments.

(1)

I tried the macros in LibreOffice with non-English language settings and they seem to work as expected.

Thank you for testing them in LibreOffice. I only tested with OpenOffice.

In LibreOffice, the macro does not take into account the range limited to the sheet (scope).
⠀

LO_rangeScope.png (71.02 KiB) Viewed 446 times

⠀

(2)

The code defines a constant named DefinedNames. In the macro comments, Defined_Names appears twice (1st and 7th REM line).
In the 7th REM line, one sees two "the".

[MrProgrammer]

The code defines a constant named DefinedNames. In the macro comments, Defined_Names appears twice (1st and 7th REM line).
In the 7th REM line, one sees two "the".

An eariler draft used Defined_Names throughout. I seem to have overlooked some comments at the top when I changed the sheet name to DefinedNames.

In LibreOffice, the macro does not take into account the range limited to the sheet (scope).

I didn't know that LibreOffice had added a feature which changes how defined names work. When I create a topic in the Code Snippets forum I will note that these macros are for OpenOffice. If I move to LibreOffice in the future I will create a second version of these macros for that suite. I think that in LibreOffice the macros above will just ignore any sheet-specific names; no export, no import. For LibreOffice spreadsheets with no sheet-specific names these macros should be suitable.

[cwolan]

As per LibreOffice 3.4: Release Notes :
⠀

sheet-local_scope.png (28.42 KiB) Viewed 416 times