The change request was one line: raise the tax rate from 8% to 8.5%. The workbook generator, however, had the old rate baked in as the literal 0.08 inside fourteen formula strings assembled by Pascal code, plus two more occurrences in a template nobody remembered editing. Hard-coded constants and cell addresses inside generated formulas are the spreadsheet equivalent of magic numbers, except these magic numbers ship to customers and get audited by their accountants. Defined names exist to remove exactly this failure mode, and HotXLS — losLab's native Delphi library for XLS and XLSX files — exposes the name table of both formats with full create, find, and delete access.
This article shows how the two name stores work, why scope is the first decision rather than an afterthought, how cross-sheet formulas interact with names, and which built-in names you must leave alone.
Two name stores, one concept
HotXLS has separate class hierarchies for the two file formats, and each carries its own name collection. On the XLS side, TXLSWorkbook.GetNames returns an IXLSNames collection whose Add(Name, RefersTo, Visible) overload creates a name in the BIFF name table; individual entries come back as IXLSName objects with Name, RefersTo, a resolved RefersToRange, and a Delete method. On the XLSX side, TXLSXWorkbook.DefinedNames is a TXLSXDefinedNames collection with Add, FindByName and DeleteByName.
The lookup conventions differ in a way that bites during porting: the XLS collection's default Item property accepts a Variant, so both Names[0] and Names['TaxRate'] resolve, while the XLSX collection wants an explicit FindByName('TaxRate') call that returns nil when the name is absent. Code that assumes one convention compiles against the other facade only by accident.
Scope decides who can see the name
A defined name is either workbook-scoped — visible to formulas on every sheet — or sheet-scoped, visible only to formulas on its owning sheet. In the XLSX API the distinction is a single optional parameter: DefinedNames.Add(AName, AFormula) creates a workbook-level name, while Add(AName, AFormula, ASheetIndex) binds it to one sheet. Reading it back, TXLSXDefinedName.SheetIndex returns -1 for workbook scope and the 0-based sheet index otherwise.
Scope is also your collision policy. Excel permits a sheet-local Total on every sheet plus a workbook-level Total, and a formula on a given sheet resolves the local one first. Generated workbooks should exploit that deliberately: business assumptions that several sheets consume (tax rates, FX rates, reporting period) belong at workbook scope, while helper ranges that only one sheet's formulas reference are safer sheet-scoped, where they cannot shadow or be shadowed by anything else.
var
Book: TXLSXWorkbook;
Data, Summary: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Data := Book.Sheets.Add('Data');
Summary := Book.Sheets.Add('Summary');
// ... fill Data!A2:D100 with detail rows ...
Book.DefinedNames.Add('TaxRate', '0.08'); // workbook scope, a constant
Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100'); // workbook scope, a range
Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1); // scoped to sheet index 1 only
// XLSX formulas take no leading '='
Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
Book.SaveAs('model.xlsx');
finally
Book.Free;
end;
end;Note that a defined name does not have to point at a range at all. TaxRate above refers to the constant 0.08, which is the cleanest way to publish a business assumption: it appears once in Excel's Name Manager, every formula references it symbolically, and next quarter's rate change is a one-line edit to the generator.
The equals sign that is not there
The single most common porting bug between the two facades is the formula entry channel. XLS cells receive formulas through Value with a leading =; XLSX cells have a dedicated Formula property that takes the expression without the prefix. Write '=SUM(A1:A10)' into TXLSXCell.Formula and the equals sign becomes part of the stored expression text rather than a marker — the file will not behave the way the same string did on the XLS side.
var
Book: IXLSWorkbook; // interface-counted: do not Free
Names: IXLSNames;
begin
Book := TXLSWorkbook.Create;
// assume a sheet named 'Data' already holds the detail rows
Names := Book.GetNames;
Names.Add('TaxRate', '0.08');
Names.Add('Helper', 'Data!$A$2:$A$100', False); // False = hidden from the Name Manager
// XLS formulas go through Value, with the '=' prefix
Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
Book.SaveAs('model.xls');
end;Two more XLS-side details visible in that snippet: the sheets collection is 1-based (Sheets[1] is the first sheet, unlike the 0-based XLSX Sheets[0]), and the third Add parameter creates a hidden name — present in the file and usable by formulas, but invisible in Excel's Name Manager. Hidden names are the right vehicle for generator-internal plumbing that end users should not edit or delete by accident.
Cross-sheet references that survive editing
Both formula engines accept the standard cross-sheet syntax: Data!A1 for plain sheet names and 'Sheet With Space'!A1 with single quotes when the name contains spaces or punctuation. Inside a name's RefersTo text, absolute references (Data!$A$2:$D$100) are almost always what you want — a relative reference inside a defined name resolves relative to the cell using it, which is a deliberate Excel feature and a thorough source of confusion when it happens unintentionally.
The structural editing operations keep names consistent on the XLSX side: InsertRows and DeleteRows shift defined-name ranges along with cells, merges, hyperlinks and chart anchors, so a name pointing at Data!$A$2:$D$100 still covers the data block after the generator opens a gap above it. The caveat documented in the engine applies to formulas, though: row insertion adjusts only references that target the sheet being edited. A Summary formula referencing Data!D2:D100 is rewritten when rows are inserted into Data — but plan the verification step anyway, because the cheap way to know is to ask the engine:
// the calculation engine resolves names and cross-sheet references in-process
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
Log('net total checks out: ' + FloatToStr(V));Calculate evaluates an arbitrary expression against the current workbook state without saving anything, which makes it the natural assertion primitive for generator tests: compute the expected aggregate from the source data in Pascal, evaluate the workbook's own formula, and compare. The formula engine article covers what the engine evaluates, when, and how to extend it with custom functions.
The _xlnm names you must not redefine
Open a generated file's name table in a low-level inspector and you will find entries you never created: _xlnm.Print_Area, _xlnm.Print_Titles, and relatives. These are how OOXML (ECMA-376 / ISO 29500) represents print areas and repeated title rows — they are defined names with reserved identifiers. HotXLS manages them through the dedicated worksheet properties: setting PrintArea or PrintTitleRows writes the corresponding _xlnm.* entry for you.
The trap is adding a _xlnm.Print_Area entry manually through DefinedNames.Add while also setting the PrintArea property: the workbook ends up with conflicting definitions for a reserved name, and Excel's behavior on such files is not something to build a product on. Treat every identifier starting with _xlnm. as owned by the property layer; if you need to inspect print setup, read the properties, not the name table. The protection and page setup article covers the print-area properties in context.
Name-table questions that come up in code review
Do defined names survive XLS-to-XLSX conversion?
Not through the convenience bridge. SaveXLSWorkbookAsXLSX copies cell content and basic formatting, and the name table is not on its documented copy list — recreate names through DefinedNames.Add after conversion, which also gives you a chance to normalize their scopes.
What happens to formulas when a user renames a sheet in Excel?
Excel rewrites sheet references inside formulas and names during an interactive rename, so files edited in Excel stay consistent. The risk sits in your generator: if Pascal code builds formula strings from a sheet-name constant, renaming the sheet in one place and not the other produces a reference to a sheet that no longer exists. Keeping the sheet name in one Delphi constant used for both Sheets.Add and formula assembly removes the drift.
Should report templates use names or fixed addresses?
Names, for anything the filling code needs to find. A template whose total cell is named keeps working after a designer inserts three rows above it; a generator that writes to hard-coded B17 does not. The template report generation article builds on exactly this pattern.
The complete defined-names API for both formats, together with the formula engine reference, ships with the HotXLS Component.