Re: [libreoffice-users] Renaming a Calc range

See in-line replies below.

On 10/25/25 22:30, John Kaufmann wrote:
> Joe- Thanks for following up on this:
>
> On 2025-10-25 14:31, Java Joe wrote:
> > I'm not qualified to explain why both Named Ranges and Database 
> > Ranges exist, but I've used both of them extensively in some fairly 
> > complex spreadsheets.
>
> If you have used both extensively, can you recall why you used Named 
> Ranges? [I have always named Database Ranges to have a way to refer to 
> a table organizing some data used by the spreadsheet operation, and 
> can't think of any other reason to name a table of data.]


I used database ranges at first because I didn't know of Named Ranges.  
I attended a Spreadsheet seminar where they used Named Ranges.  After 
that I started using Named Ranges for things like naming individual 
cells so I can reference that by Name (example: TaxRate).  Reason: It's 
faster.  Just select range, type name into Name Box and (!!bug??) Hit 
Enter Twice.

However for anything that I might want to manipulate programmatically or 
use Auto Filters on I use Database Ranges, because it's easier to do via 
Basic.

Key Differences:  A Database Range has options for Column Headers and 
Totals Row (useful if using Auto Filters on a table), where a Named 
Range has a Scope option (which I haven't found useful yet, I always use 
default "global" scope).


> > I just did a quick test, and there is a relatively simple way to 
> > change the name of a DatabaseRange; by using a Macro (Basic Code).
> >
> > Enter the following subroutine in one of the document's Basic modules 
> > (create one if none exist), replace the "WrongRange" and 
> > "CorrectName" text with what your range is currently named and what 
> > you want it to be named (but retain the quotation marks), then 
> > execute it (F5).  There is no visual indication anything happened, 
> > but on my version 25.2.0.3 (Debian 12 OS) this does indeed rename the 
> > range, and attempting to execute it a second time results in a "No 
> > Such Element" error.
> >
> > ``` Basic
> >
> > Sub RenameDatabaseRange
> > ThisComponent.DatabaseRanges.getByName("WrongRange").setName("CorrectName") 
> >
> > End Sub
> >
> > ```
> By itself, that does not change any references using "WrongRange", 
> does it? - or any sorts or filters using "WrongRange"?  IOW, doesn't 
> Regina's bug stand as filed on this?


You are correct.  Sorry, I missed that detail.  Any formula references 
do NOT update and formulas are now broken.

Writing a routine to update formulas would be possible, but likely slow 
and cumbersome for a file of any size.


> > This indicates that the ability to rename a range exists, it's just 
> > not implemented in the GUI.
> >
> > Beware that this code is for a Database Range, a Named Range is 
> > accessed in an entirely different manner in Basic code.
>
> Interesting.
>
> Thanks, John

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy