LLExporter

Factory

FindOpenWorkbook #

find-open-workbook

The workbook of an Excel instance already open on a path, or Nothing

Signature:

Private Function FindOpenWorkbook(ByVal filePath As String, _
                                  ByVal excelApp As Application) As Workbook

Parameters:

• filePath: String. Full path to look for.
• excelApp: Application. The instance whose open workbooks are searched.

Returns: Workbook. The open workbook, or Nothing when the path is open nowhere.

MissingLinelistSheet #

missing-linelist-sheet

The first sheet a linelist carries and a workbook misses

Signature:

Private Function MissingLinelistSheet(ByVal wkb As Workbook) As String

The export walks read the dictionary, the export definitions, the passwords, the geobase and the temp sheet, so those five names are what marks a workbook as a linelist here.

Parameters:

• wkb: Workbook. The workbook to look through.

Returns: String. The first missing sheet name, or an empty string when all five are there.

InternalSheetOf #

internal-sheet-of

An internal worksheet of a workbook under either of its names

Signature:

Private Function InternalSheetOf(ByVal wkb As Workbook, _
                                 ByVal newName As String, _
                                 ByVal oldName As String) As Worksheet

A linelist generated before the internal-sheet rename carries the old trailing name, so the new name is looked for first and the old one answers when it is absent.

Parameters:

• wkb: Workbook. The workbook to look through.
• newName: String. The name the sheet carries today.
• oldName: String. The name an older linelist gave it.

Returns: Worksheet. The worksheet, or Nothing when the workbook has neither name.

Public Accessors

SourceWorkbook #

source-workbook

Source linelist workbook

Signature:

Public Property Get SourceWorkbook() As Workbook

Returns: Workbook. The linelist workbook bound to this exporter.

SourceWorkbook #

source-workbook-set

Assign the source linelist workbook

Signature:

Public Property Set SourceWorkbook(ByVal wb As Workbook)

Parameters:

• wb: Workbook. The workbook to store.

Seal #

seal

Prevent further changes to setup-only properties

Signature:

Public Sub Seal()

MarkSourceOpened #

mark-source-opened

Record that this instance opened its source workbook

Signature:

Public Sub MarkSourceOpened()

Set at creation only, by CreateFromFile, before the seal.

GuardNotSealed #

guard-not-sealed

Raise when a setup-only property is written after sealing

Signature:

Private Sub GuardNotSealed(ByVal propName As String)

Parameters:

• propName: String. The name of the property being guarded.

Export Methods

SheetTagOf #

Get the sheet type tag from worksheet-level HiddenNames.

Signature:

Private Function SheetTagOf(ByVal sh As Worksheet, _
                            Optional ByVal nameKey As String = SHEET_TYPE_KEY, _
                            Optional ByVal allowOldLayout As Boolean = False) As String

The read goes through HiddenNames.QuickValue: three COM crossings for the one name asked for, with no instance behind it. HiddenNames.Create walks every tracked name of the sheet first and reads three properties off each, and a data entry sheet holds hundreds of names. This routine runs once per data sheet in AddShowHide and once more in AddDataSheets, so the walk was paid twice per sheet for two strings. A linelist generated before the sheet metadata moved into hidden names carries no name to read, and QuickValue answers the default rather than raising. The old layout wrote the same two words into a formatted cell instead, so the cell is read when the name is absent. The fallback is asked for, not automatic. AddDataSheets wants it: the sheet type is all it needs and the data is lost without it. AddShowHide does not, because the layer it would then go on to write needs table_name as well, and that key has no old-layout fallback. Turning it on there would trade a layer quietly missing for a raise that costs the whole export.

Parameters:

• sh: Worksheet. The worksheet to query.
• allowOldLayout: Optional Boolean. When True, an absent sheet_type name is read from the cell the old layout used. Defaults to False.

Returns: String. The sheet type tag (HList, VList, etc.).

OldSheetTypeCell #

old-sheet-type-cell

The sheet type of a linelist older than the hidden names

Signature:

Private Function OldSheetTypeCell(ByVal sh As Worksheet) As String

Hlist and Vlist wrote the sheet type into the third cell of the first row and the table name into the fourth, both formatted LinelistHiddenCell so nobody would see them. The two words are the ones the hidden name carries today, so the cell is read as it stands and nothing is translated.

This is only ever reached when the worksheet carries no sheet_type name at all. A sheet that has one never comes here, so a modern file cannot be read wrong by a cell that happens to hold something else.

Parameters:

• sh: Worksheet. The data entry worksheet to query.

Returns: String. The tag the old layout wrote, or an empty string.

Private - Source Object Accessors

SourceDictionary #

source-dictionary

Get a dictionary from the source workbook

Signature:

Private Function SourceDictionary(ByVal nbExp As Long) As LLdictionary

Parameters:

• nbExp: Long. Number of export columns to configure.

Returns: LLdictionary. Dictionary instance from the Dictionary sheet.

ExportObject #

export-object

Get the export configuration from the source workbook

Signature:

Private Function ExportObject() As LLExport

Returns: LLExport. Export configuration from the Exports sheet.

PasswordsObject #

passwords-object

Get passwords from the source workbook

Signature:

Private Function PasswordsObject() As Passwords

Built once and held, and answering Nothing rather than raising when the file will not take one.

A LINELIST OLDER THAN THE PROTECTION MATRIX

Passwords.Create validates the sheet and demands T_ProtectedSheets, a table this version added. A linelist generated before it carries T_Keys, the five named ranges and no protection matrix, so the factory refuses a password sheet that is complete for everything an export wants from it. The class itself is not loosened -- it is baked into the workbook rather than imported, so a change there reaches nothing until every workbook is re-pasted by hand. The tolerance lives here instead, in a class that imports.

What the absence costs is small and bounded. An export-all file name is composed without a passwords object at all; a numbered export loses its version suffix and cannot be given the private key. Both are guarded where they read.

Returns: Passwords. The held handler, or Nothing when the file carries none.

ReportGeoWarning #

report-geo-warning

Log what the geobase export got past rather than stopped on

Signature:

Private Sub ReportGeoWarning(ByVal geo As LLGeo)

Read after an export that succeeded. LLGeo fills its LastFailure on a refusal it carried on from -- the headers it could not put back -- and leaves it empty otherwise, so a value here is a warning and never the reason for a failure. On a failure the RecordFailure handler already folds the same account into this class''s own, so nothing is said twice.

Parameters:

• geo: LLGeo. The manager that just wrote the geobase.

ReportPasswordsRefusal #

report-passwords-refusal

Say in the workbook''s log that this file carries no usable passwords

Signature:

Private Sub ReportPasswordsRefusal(ByVal refusal As String)

Written as a warning rather than a failure: the export goes on to write its file, and the user needs to know why the name lost its version suffix and why no export of this file can be password protected.

Parameters:

• refusal: String. What Passwords.Create said when it refused.

UserLog #

user-log

The log of the workbook being exported, built once

Signature:

Private Function UserLog() As LLLog

Built on first use and held, the way FilteredData and LLGeo hold theirs. A workbook that will not take a log answers Nothing, and the Nothing is the state rather than a raise.

Returns: LLLog. The held log, or Nothing.

StartStepWatch #

start-step-watch

Open the stopwatch of an export walk

Signature:

Private Sub StartStepWatch()

Called once at the top of each of the four walks, right after the handler is armed. The log holds the stopwatch, so nothing is kept here, and a workbook that will not take a log simply goes untimed.

MarkStep #

mark-step

Name the step the walk is about to take, and time the one before it

Signature:

Private Function MarkStep(ByVal stepName As String) As String

Answers the name it was given, so each walk keeps the one line per step it already had:

atStep = MarkStep("reading the dictionary")

The name goes on reaching RecordExportFailure exactly as it did, and the timing rides along. An untimed walk answers the name and does nothing else.

Parameters:

• stepName: String. What the walk is about to do.

Returns: String. The name it was given.

LogStepTimes #

log-step-times

Write the step times of a walk into the log

Signature:

Private Sub LogStepTimes(ByVal action As String, ByVal source As String)

Called on both exits of every walk: the line is as much wanted when an export refuses as when it finishes, and on a refusal it carries the step that refused with the seconds it had spent. The failure path reaches this AFTER RecordExportFailure has kept its words and the caller has read the error into its own variables, so clearing Err in here costs nothing.

Parameters:

• action: String. The action code the walk logs under.
• source: String. The method that ran the walk.

RecordExportFailure #

record-export-failure

Keep in words what an export refused on, before the raise leaves

Signature:

Private Sub RecordExportFailure(ByVal what As String, ByVal atStep As String, _
                                ByVal errNumber As Long, _
                                ByVal errSource As String, _
                                ByVal errMessage As String)

An error loses its words on the way out of this class: the caller reads the name of the method it called and nothing of what actually refused. Every export therefore records its own account here, and the caller reads it back through LastFailure.

ExportGeo carried this alone for a while, which is why a failing migration answered an empty LastFailure and the form had nothing to log.

Parameters:

• what: String. Which export this is, for the opening words.
• atStep: String. The step the export was on.
• errNumber: Long. The error number.
• errSource: String. The error source.
• errMessage: String. The error description.

Returns: LLGeo. Geo instance from the Geo sheet.

Private - Output Workbook Management

HostApplication #

host-application

The Excel instance the source linelist lives in

Signature:

Private Function HostApplication() As Application

Every output workbook is added there, so an export runs in the instance that holds its source, whichever instance that is.

Returns: Application. The Application of the source workbook.

CreateOutputWorkbook #

create-output-workbook

Create a new minimized output workbook

Signature:

Private Function CreateOutputWorkbook() As Workbook

ONE EMPTY SHEET, NOT THE USER'S TEMPLATE

Workbooks.Add with no argument builds from whatever default template the user has. A template carrying several sheets and a long style list costs on every export and again on every save, and the extra sheets travelled into the output: the walks name Worksheets(1) as the placeholder and delete that one, so sheets two and three of a template would have gone out with the file. xlWBATWorksheet gives a one-sheet workbook with nothing in it.

CALCULATION IS HELD HERE, BY THIS ROUTINE

Adding a workbook can put Application.Calculation back to what that workbook carries, so a mode set before the walk does not survive the Add. LLGeo.ExportToFile holds the mode itself for the same reason and says so. ApplicationState.ApplyBusyState asks for manual, but it asks inside an On Error Resume Next and reports success either way, so a caller cannot know the mode it got. The prior mode is kept on the instance and SaveWorkbook and CloseAll put it back.

Returns: Workbook. The newly created workbook.

RestoreCalculation #

restore-calculation

Put the calculation mode back to what the walk found

Signature:

Private Sub RestoreCalculation()

Called at both ends of an export: SaveWorkbook when the file is written, and CloseAll when the caller tidies up after a failure. A walk that never reached CreateOutputWorkbook held nothing, and calcHeld is False, so nothing is set. A caller that had already asked for manual gets manual back, which is a no-op.

SaveWorkbook #

save-workbook

Save and close the output workbook

Signature:

Private Sub SaveWorkbook(ByVal outwb As Workbook, ByVal filePath As String, _
                         ByVal fileFormat As Long, ByVal password As String)

Saves the workbook to the specified path with the given format and optional password, then closes it without saving again.

Parameters:

• outwb: Workbook. The workbook to save.
• filePath: String. Full path including file name and extension.
• fileFormat: Long. Excel file format constant (xlOpenXMLWorkbook or xlExcel12).
• password: String. Password to apply, or vbNullString for none.

BuildFilePath #

build-file-path

Build a full file path from folder, name, and extension

Signature:

Private Function BuildFilePath(ByVal folderPath As String, ByVal fileName As String, _
                               ByVal fileExt As String) As String

Parameters:

• folderPath: String. Target folder path.
• fileName: String. File name without extension.
• fileExt: String. File extension without dot.

Returns: String. The assembled file path.

Private - Dictionary Preparation

ExcludedVariables #

excluded-variables

The variables a custom export leaves out

Signature:

Private Function ExcludedVariables(ByVal dict As LLdictionary, ByVal expObj As LLExport, _
                                   ByVal exportNumber As Long) As BetterArray

Reads three cells of the export row and answers the names of the variables the export drops: the geo variables of every admin level "admin levels" leaves out, the geo p-code and geo concat variables when "include p-codes" is off, and every variable flagged as a personal identifier when "include personal identifiers" is off.

ONE LEVEL OWNS THREE ROWS

LLdictionary.AppendGeoLines writes a visible name row, a hidden p-code row and a hidden concat row for each of the four levels, and a level left out drops all three. The health facility rows are left alone: hf and geo_pcode_hf belong to no admin level, and an export keeps them whatever the cell asks for.

WHY THIS READS AND NEVER WRITES

The exclusion used to be done by clearing the export column of the linelist's own Dictionary worksheet, so one export with p-codes off took those variables out of that export for good and turning the flag back on could not bring them back. The dictionary is the file the whole linelist is generated from and it is what a migration ships, so the loss travelled to the next linelist. The names answered here are dropped from the copies the export builds, and the source sheet keeps what the setup put in it.

Parameters:

• dict: LLdictionary. The source dictionary.
• expObj: LLExport. The export configuration.
• exportNumber: Long. Which export definition to read.

Returns: BetterArray. The variable names to drop, empty when the export keeps everything.

KeptAdminLevels #

kept-admin-levels

The admin levels one export row asks for

Signature:

Private Function KeptAdminLevels(ByVal cellText As String, _
                                 ByRef someDropped As Boolean) As Boolean()

The "admin levels" cell holds the levels joined with a plus, written admin1+admin2. An empty cell asks for every level.

THE CELL IS HONOURED ONLY WHEN THE WHOLE OF IT READS

A cell holding one chunk this cannot read answers every level, the way an empty cell does. An export that silently drops geo columns over a typo is worse than one that keeps them all, and SetupErrors.CheckExports reports that same cell on the setup side, before a linelist is ever built from it.

Parameters:

• cellText: String. What the Exports row holds.
• someDropped: Boolean. Written True when a level is left out.

Returns: Boolean(). One entry per level, 1 to 4, True for a level to export.

AdminLevelOfChunk #

admin-level-of-chunk

The level one chunk of the "admin levels" cell holds

Signature:

Private Function AdminLevelOfChunk(ByVal chunk As String) As Long

The chunk is trimmed and read without regard to case, so Admin2 and admin2 both answer 2. Anything else answers 0, and the caller reads that as a cell it cannot honour. SetupErrors.IsAdminLevelsCell holds this same rule.

Parameters:

• chunk: String. One piece of the cell, between two plus signs.

Returns: Long. The level, 1 to 4, or 0 when the chunk holds something else.

GeoLevelOf #

geo-level-of

The admin level one dictionary row belongs to

Signature:

Private Function GeoLevelOf(ByVal controlValue As String, _
                            ByVal mainLabel As String) As Long

LLdictionary.AppendGeoLines writes three rows per level and this reads all three back. The visible name row carries the control geo1 to geo4. The two hidden rows carry the control formula, and their level sits in the main label, written geo_pcode_adm2 or geo_concat_adm2. A row belonging to no admin level answers 0, and the health facility rows are among them: hf and geo_pcode_hf name no level.

Parameters:

• controlValue: String. The control cell of the row.
• mainLabel: String. The main label cell of the row.

Returns: Long. The level, 1 to 4, or 0 when the row names none.

ColumnValues #

column-values

Read a one-column range into a 1-based array

Signature:

Private Function ColumnValues(ByVal rng As Range) As Variant

A range of one cell answers a scalar rather than an array, so that case is built by hand. Everything else is one crossing for the whole column.

Parameters:

• rng: Range. A range one column wide.

Returns: Variant. A 1-based array of the cell values.

WithoutExcludedRows #

without-excluded-rows

Drop the excluded variables from a dictionary block

Signature:

Private Function WithoutExcludedRows(ByVal dictData As BetterArray, _
                                     ByVal excluded As BetterArray) As BetterArray

The block carries its headers on the first row, so the variable name column is found by its header and every row below is kept or dropped on the name it holds. The block is answered unchanged when the export excludes nothing or when the block carries no variable name column.

Parameters:

• dictData: BetterArray. A dictionary block, headers included.
• excluded: BetterArray. The variable names to drop.

Returns: BetterArray. The block with the excluded rows removed.

TopRowsOf #

top-rows-of

Copy the first rows of a two-dimensional block

Signature:

Private Function TopRowsOf(ByVal block As Variant, ByVal firstRow As Long, _
                           ByVal lastRow As Long, ByVal firstCol As Long, _
                           ByVal lastCol As Long) As Variant

ReDim Preserve reaches the last dimension alone, so a block that lost rows is copied into a block of the right height instead.

Parameters:

• block: Variant. The source block.
• firstRow: Long. First row to copy.
• lastRow: Long. Last row to copy.
• firstCol: Long. First column of the block.
• lastCol: Long. Last column of the block.

Returns: Variant. A block holding the rows asked for.

PrepareTempDictionary #

prepare-temp-dictionary

Write dictionary data to the temp sheet for export processing

Signature:

Private Sub PrepareTempDictionary(ByVal dict As LLdictionary, _
                                  ByVal expName As String, _
                                  ByVal fullExport As Boolean, _
                                  ByVal excluded As BetterArray)

When fullExport is True, copies the entire dictionary. When False, filters on the export column name to include only rows assigned to that export. The variables the export excludes are dropped from the block in memory. Writes the result to the __temp sheet and adds a blue font tag to mark the dictionary as prepared.

Parameters:

• dict: LLdictionary. The source dictionary.
• expName: String. The export column name to filter on, or vbNullString for full.
• fullExport: Boolean. When True, copies the entire dictionary unfiltered.
• excluded: BetterArray. Variable names to drop from the copy.

ExportDictionaryFromTemp #

export-dictionary-from-temp

Create an export dictionary from the temp sheet

Signature:

Private Function ExportDictionaryFromTemp(ByVal nbExp As Long) As LLdictionary

Parameters:

• nbExp: Long. Number of export columns.

Returns: LLdictionary. Dictionary built from the __temp sheet.

SortExportDictionary #

sort-export-dictionary

Sort the export dictionary by table name and export column

Signature:

Private Sub SortExportDictionary(ByVal expDict As LLdictionary, ByVal expName As String)

Parameters:

• expDict: LLdictionary. The dictionary to sort.
• expName: String. The export column name to use as secondary sort key.

Private - Metadata and Sheets

AddMetadataSheets #

add-metadata-sheets

Add Metadata, Choices, and Translations sheets to the output workbook

Signature:

Private Sub AddMetadataSheets(ByVal outwb As Workbook, ByVal dict As LLdictionary, _
                              ByVal nbExp As Long)

Parameters:

• outwb: Workbook. The output workbook.
• dict: LLdictionary. The source dictionary.
• nbExp: Long. Number of export columns.

AddDictionaryTranslations #

add-dictionary-translations

Put the dictionary translations into the output, either layout

Signature:

Private Sub AddDictionaryTranslations(ByVal outwb As Workbook)

The current layout keeps them in Tab_Translations on the LinelistTranslation sheet, and LLTranslation.ExportDictionary writes them out from there onto a sheet called Translations.

THE LAYOUT BEFORE THAT ONE

A linelist older than that table keeps its dictionary translations on a worksheet of its own, already called Translations, and carries no Tab_Translations at all. LLTranslation.Create demands that table and refuses the whole sheet without it, so the export used to stop here.

Skipping is not the answer: the Translations sheet of an export file is what carries every translated label to whatever reads the file back, and an export without one loses all of them silently. So the older layout is read where it actually sits and copied straight across, the same way Metadata and Choices are copied above.

Parameters:

• outwb: Workbook. The output workbook being built.

CopyOldTranslationsSheet #

copy-old-translations-sheet

Copy the dictionary translations of an older linelist across

Signature:

Private Sub CopyOldTranslationsSheet(ByVal outwb As Workbook, _
                                     ByVal refusal As String)

The destination name is the one ExportDictionary would have used, so a file built this way is shaped like every other export and needs nothing special to read it back.

Parameters:

• outwb: Workbook. The output workbook being built.
• refusal: String. What LLTranslation.Create said when it refused.

ReportTranslationsWarning #

report-translations-warning

Log which layout the dictionary translations came from

Signature:

Private Sub ReportTranslationsWarning(ByVal detail As String)

Parameters:

• detail: String. What to tell the user.

AddDictionary #

add-dictionary

Add the dictionary to the output workbook

Signature:

Private Sub AddDictionary(ByVal outwb As Workbook, ByVal dict As LLdictionary, _
                          ByVal nbExp As Long, ByVal exportColumn As String, _
                          ByVal excluded As BetterArray)

Exports the dictionary filtered by exportColumn, drops the rows this export excludes from the copy, then marks the output dictionary as prepared with a blue font tag.

WHY THE EXCLUDED ROWS ARE DROPPED HERE

The export column of the source sheet decides what this copy carries, and that column is left as the setup wrote it. So the copy arrives holding the p-codes and the personal identifiers the export leaves out, and the rows are taken off the copy. The file then lists the variables its data sheets carry, and the linelist keeps its own dictionary whole.

Parameters:

• outwb: Workbook. The output workbook.
• dict: LLdictionary. The source dictionary.
• nbExp: Long. Number of export columns.
• exportColumn: String. The export column to filter on, or "all".
• excluded: BetterArray. Variable names this export leaves out.

DropExcludedFromCopy #

drop-excluded-from-copy

Take the excluded variables off an exported dictionary

Signature:

Private Sub DropExcludedFromCopy(ByVal outDict As LLdictionary, _
                                 ByVal excluded As BetterArray)

The row numbers of the excluded variables are gathered off one block read, and the rows are deleted a run of neighbours at a time, from the bottom of the sheet up.

WHY THE ROW NUMBERS AND NOT ONE UNION

The rows used to be gathered by calling Union once per excluded variable. Union rebuilds the whole area list every time it is called, so a dictionary that leaves out E variables paid a cost that grows with E squared: 200 identifiers meant about 20,000 area copies before a single row was deleted.

The names are already in hand as a block, so the walk keeps the row numbers instead. They come out in order, so neighbouring numbers make a run and each run is one Delete of a resized block. A dictionary whose excluded variables sit together -- the usual shape, since the p-codes and the identifiers are grouped -- comes down to a handful of calls.

The runs are deleted from the last one backwards. Deleting a row moves every row under it up, so working from the bottom leaves the row numbers still to be used untouched.

Parameters:

• outDict: LLdictionary. The dictionary of the output workbook.
• excluded: BetterArray. Variable names to remove.

AddCustomDropdowns #

add-custom-dropdowns

Add custom dropdowns to the Choices sheet in the output workbook

Signature:

Private Sub AddCustomDropdowns(ByVal outwb As Workbook)

Walks the dropdown registry of the source workbook and writes the values of each custom dropdown onto the output Choices sheet. Silently exits when no custom dropdown sheet exists.

The walk goes through DropdownLists.AllDropdowns, so the key carries the dropdown name the registry holds. The header cell of the ListObject was read before, and that cell holds the name with every space turned into an underscore, so a dropdown called contact type went into the file under a name the import side could not update. ChoiceKeys builds the key both ends use.

Parameters:

• outwb: Workbook. The output workbook.

AddMetadataTags #

add-metadata-tags

Add metadata tags to the Metadata sheet in the output workbook

Signature:

Private Sub AddMetadataTags(ByVal outwb As Workbook, ByVal scope As Byte, _
                            ByVal exportNumber As Long, _
                            ByVal includeShowHide As Boolean, _
                            ByVal keepLabels As Boolean, _
                            ByVal useFilters As Boolean)

Appends export_type, export_date, editable_labels, show_hide, and epiweekstart tags to the Metadata sheet variable/value table. These tags control import behaviour when the exported file is re-imported.

Parameters:

• outwb: Workbook. The output workbook.
• scope: Byte. ExportScope value (ExportScopeCustom or ExportScopeMigration).
• exportNumber: Long. The export number for custom exports.
• includeShowHide: Boolean. When True, tags show_hide as "update on import".
• keepLabels: Boolean. When True, tags editable_labels as "update on import".
• useFilters: Boolean. When True, the data came from the filtered companions.

ExportOptionsTag #

export-options-tag

The options of one export, written as one line the import reads back

Signature:

Private Function ExportOptionsTag(ByVal scope As Byte, ByVal exportNumber As Long, _
                                  ByVal useFilters As Boolean) As String

The Exports worksheet stays in the linelist, so a file has no way of saying how it was written unless the export writes it down. Every option is a name: value pair and the pairs are joined with a pipe, so the import side splits on the pipe and then on the colon.

The two pairs the import cannot work without are variable names and header row. A file whose header row carries labels alone holds no variable names anywhere, so it can never be read back; a file carrying both has its variable names on row 2 and reads back once the import knows to look there.

Parameters:

• scope: Byte. ExportScope value.
• exportNumber: Long. The export number, ignored for a migration.
• useFilters: Boolean. Whether the data came from the filtered companions.

Returns: String. The options of this export on one line.

OnePair #

one-pair

One name: value pair of the export options line

Signature:

Private Function OnePair(ByVal optionName As String, ByVal optionValue As String, _
                         Optional ByVal whenEmpty As String = "no") As String

An option the Exports row left empty is written as no, so the reader on the other side never has to tell an absent option from a false one. An option whose empty cell means something else says so: "admin levels" left empty asks for every level, and no would read as none of them.

Parameters:

• optionName: String. The option name.
• optionValue: String. What the Exports row holds for it.
• whenEmpty: Optional String. What an empty cell is written as. Defaults to no.

Returns: String. The pair.

AddShowHide #

add-show-hide

Export the show/hide choices with their sizes to the output workbook

Signature:

Private Sub AddShowHide(ByVal outwb As Workbook)

Builds a __show_hide sheet in the output workbook and asks ShowHideStore to provision its table. Every data sheet contributes each layer it has: HList and CRF for a data entry sheet, Printed for its companion, VList for an info sheet. The choices the user made are read out of the source workbook first, so the file carries what the user sees rather than what the dictionary authored.

The CRF layer used to be left out of both ends of the migration, so a CRF sheet lost its visibility and its sizes on every round trip.

Parameters:

• outwb: Workbook. The output workbook.

SaveOneShowHideLayer #

save-one-show-hide-layer

Write one layer of one sheet into the export store

Signature:

Private Sub SaveOneShowHideLayer(ByVal outStore As ShowHideStore, _
                                 ByVal dict As LLdictionary, _
                                 ByVal layer As Byte, _
                                 ByVal sheetName As String, _
                                 ByVal sourceSh As Worksheet)

Reads the entry list from the dictionary, folds in whatever the user chose in the running workbook, then saves it with the sheet so the sizes and the header directions travel too.

Parameters:

• outStore: ShowHideStore. The store on the output workbook.
• dict: LLdictionary. The source dictionary.
• layer: Byte. A ShowHideWorksheetLayer value. Which layer to write.
• sheetName: String. The base sheet name.
• sourceSh: Worksheet. The sheet of that layer in the source workbook.

CompanionSheet #

companion-sheet

Find a prefixed companion sheet of the source workbook

Signature:

Private Function CompanionSheet(ByVal sheetName As String) As Worksheet

Parameters:

• sheetName: String. The full sheet name, prefix included.

Returns: Worksheet. The sheet, or Nothing when the workbook has none.

BaseTableNameOf #

base-table-name-of

The table name a sheet's PRINTSTART anchor is named after

Signature:

Private Function BaseTableNameOf(ByVal sh As Worksheet) As String

A printed companion stores its table name with the print_ prefix in front and the anchor is named after the base one, so the prefix comes off here.

Parameters:

• sh: Worksheet. The sheet to read.

Returns: String. The table name with no print_ prefix.

ImportCurrentShowHide #

import-current-show-hide

Fold the user's live choices into an entry list

Signature:

Private Sub ImportCurrentShowHide(ByVal entries As ShowHide)

Reads the show/hide store of the source workbook. A workbook that has never had the form opened carries an empty table, and the entry list then keeps the state the dictionary authored.

Parameters:

• entries: ShowHide. The entry list to update.

AddAnalysisSheets #

add-analysis-sheets

Add analysis worksheets to the output workbook

Signature:

Private Sub AddAnalysisSheets(ByVal outwb As Workbook, ByVal afterSheetName As String)

Copies the formats of the four analysis sheets (univariate, time series, spatial, spatio-temporal) in the source workbook to new sheets in the output workbook, removes gridlines, then asks AnaTabIds to recreate the named ranges -- values included -- and the charts of each sheet.

WHAT THE ERROR TRAP COVERS

The trap covers the clipboard steps alone. It used to cover the chart writing too, and the chart writing raised on its first statement for every scope, so the export reported success and delivered analysis sheets with no named ranges and no charts. Each scope is written under its own trap now and the failures are collected.

A SCOPE THAT LOSES ITS CHARTS STILL SHIPS ITS SHEET

The collected failures are written to the workbook log as a warning and the export carries on. A chart is one part of an analysis sheet; the values, the formats and the tables are written before the charts are attempted and they stand whatever the charts do.

This used to raise, so one scope that refused cost the user the whole file. A graph row that names no choice is a setup matter, and it left every export of that linelist with nothing to open. Owner decision, 2026-09-03.

Parameters:

• outwb: Workbook. The output workbook.
• afterSheetName: String. Name of the sheet to insert after.

ReportGraphsWarning #

report-graphs-warning

Say in the workbook''s log which analysis sheets got no charts

Signature:

Private Sub ReportGraphsWarning(ByVal failureText As String)

Written as a warning, and the export goes on to write its file. The values, the formats and the tables of the sheet are already on it by the time the charts are attempted, so a scope that loses its charts still carries most of what an analysis sheet holds.

The user needs the line to know why a sheet came out with no chart on it, because the file opens and reads as complete.

Parameters:

• failureText: String. The scopes that refused, each with its error text.

Private - Data Export

AddDataSheets #

add-data-sheets

Add data to each output sheet from source HList/VList worksheets

Signature:

Private Sub AddDataSheets(ByVal outwb As Workbook, ByVal expDict As LLdictionary, _
                          ByVal useLabels As Boolean, ByVal startLine As Long, _
                          ByVal useFilters As Boolean)

Iterates over the unique sheet names in the export dictionary, reads data from the corresponding source sheet, and writes it to the output sheet. For HList sheets, delegates to CustomTable.Export. For VList sheets, delegates to ExportVListData. Applies formatting to each output sheet.

THE DICTIONARY IS READ ONCE AND GROUPED IN MEMORY

The variable names of a sheet used to come off a real AutoFilter, and the labels of that same sheet off a second one, so every data sheet cost two filter applications, two SpecialCells reads and two filter removals on the export dictionary. The three columns those two filters read -- sheet name, variable name and main label -- are read here as three whole-column blocks, once for the whole export, and SheetRowsOf answers both lists per sheet out of memory. A file with eight data sheets goes from sixteen filter runs to three reads. The main label column is read only when the header format asks for labels. A dictionary that carries no such column used to reach the label filter in that case alone, so reading it unconditionally would raise ElementNotFound on a file that exports today.

Parameters:

• outwb: Workbook. The output workbook.
• expDict: LLdictionary. The export dictionary with variable assignments.
• useLabels: Boolean. When True, writes label row above data headers.
• startLine: Long. Data start row (2 when labels are on row 1).
• useFilters: Boolean. When True, reads from filtered companion sheets.

FilteredTableOf #

filtered-table-of

The filtered companion table of one data entry sheet

Signature:

Private Function FilteredTableOf(ByVal inpsh As Worksheet) As ListObject

The companion holds the rows the filters leave visible, and the export reads it when the user ticks the filtered box. Three things have to be there: the name in the sheet store, the sheet it names, and the table on that sheet. All three used to be read in one chained line, so a workbook missing any of them raised a bare Excel number and the box the user got named neither the sheet nor the piece that was missing. The companion name is read through HiddenNames.QuickValue. It is the one name this routine wants off the sheet, and building an instance walked every tracked name of a sheet that holds hundreds of them.

Parameters:

• inpsh: Worksheet. The data entry sheet being exported.

Returns: ListObject. The companion table.

Throws:

• ProjectError.ElementNotFound When any of the three is absent.

SheetRowsOf #

sheet-rows-of

The variable names and labels one data sheet owns

Signature:

Private Sub SheetRowsOf(ByRef sheetValues As Variant, _
                        ByRef nameValues As Variant, _
                        ByRef labelValues As Variant, _
                        ByVal sheetName As String, _
                        ByVal namesOut As BetterArray, _
                        ByVal labelsOut As BetterArray)

Walks the dictionary columns AddDataSheets holds in memory and fills the two lists of one sheet in a single pass. This is what the two AutoFilter runs per sheet used to answer. The sheet names are compared without regard to case, because that is what AutoFilter did with its criteria, and the names being matched come off the same column through LLdictionary.UniqueValues. A cell holding an error value is stepped over rather than read, so one broken cell in the sheet name column cannot raise a type mismatch and cost the whole export.

Parameters:

• sheetValues: Variant. The sheet name column, 1-based.
• nameValues: Variant. The variable name column, 1-based, same rows.
• labelValues: Variant. The main label column, 1-based, same rows. Empty when the header format asks for no labels.
• sheetName: String. The sheet whose rows are wanted.
• namesOut: BetterArray. Filled with the variable names, in row order.
• labelsOut: BetterArray. Filled with the main labels, in the same order.

LabelsForWrittenColumns #

labels-for-written-columns

The label row that lines up with the columns an export wrote

Signature:

Private Function LabelsForWrittenColumns(ByVal allLabels As BetterArray, _
                                         ByVal headersList As BetterArray, _
                                         ByVal skipped As BetterArray) As BetterArray

The dictionary answers one label per variable, in the same order as the variable names, so the two lists match until the export skips one. Dropping the label of every skipped variable is what keeps the row over its data. Both lists are handed in. They come off the one grouping pass AddDataSheets makes over the dictionary columns it holds, so this routine reads no worksheet.

Parameters:

• allLabels: BetterArray. The main labels of the sheet, in row order.
• headersList: BetterArray. The variable names the export was asked for.
• skipped: BetterArray. The variable names the export could not write.

Returns: BetterArray. One label per column written, in column order.

ExportVListData #

export-vlist-data

Export VList data (variable/value pairs) to the output sheet

Signature:

Private Sub ExportVListData(ByVal inpsh As Worksheet, ByVal outsh As Worksheet, _
                            ByVal headersList As BetterArray, _
                            ByVal useLabels As Boolean)

Writes one row per variable: the variable name in column A, its label beside it when the header format asks for labels, then the value.

THE THREE HEADER FORMATS ON A VLIST SHEET

Owner decision, 2026-08-01: the shape is variable | label | value, and the label column is written in the two label formats alone.

What it replaces was wrong in two ways at once. The data start ROW was fed into a column index, so the variable names + variable labels format wrote the pair into columns B and C and left column A empty; and in the same format the variable name was overwritten by the label, so the file carried no variable names at all. LLImporter.ImportVListSheet resolves each row through currsh.Range(varName), so every row of such a file landed in "not imported". Column A now always holds the variable name, whatever the format.

THE BLOCK IS BUILT IN MEMORY AND WRITTEN ONCE

The routine used to write the sheet a cell at a time. Per variable it resolved inpsh.Range(varName) twice, read two values off it and wrote up to three cells -- about seven crossings each, for as many variables as the info sheet carries.

Now the named range is resolved once and held, the pair of reads comes off that one range, and the whole sheet goes out in a single ToExcelRange. A VList of 300 variables goes from about 2,100 crossings to about 900, and the write end of it to one.

The clear covers the block this routine owns rather than the whole grid. The output sheet is added empty by CreateOutputWorkbook, so there is nothing else on it to clear, and Cells.Clear reaches all 17 billion cells to say so.

Parameters:

• inpsh: Worksheet. The source VList worksheet.
• outsh: Worksheet. The output worksheet.
• headersList: BetterArray. Variable names to export.
• useLabels: Boolean. When True, writes a label column beside the names.

Private - Formatting Helpers

FormatOutputSheet #

format-output-sheet

Apply standard formatting to an output data sheet

Signature:

Private Sub FormatOutputSheet(ByVal outsh As Worksheet)

Sets row heights, column widths, font, header styling, and auto-fits columns. Wrapped in error handling to prevent failures on protected or special sheets.

THE BLOCK THAT WAS WRITTEN, NOT THE WHOLE GRID

The row height, the column width and the font used to go onto .Cells: 1,048,576 rows, 16,384 columns, the whole grid of every data sheet. That costs twice. It costs here, and it costs again at SaveAs, because a sheet whose whole grid carries a row height, a width and a font is written out with a format record for every row and every column. A ten-sheet migration paid it ten times. DataSheet.Export went through this already and formats its used range only.

The block is read off UsedRange, which is one crossing and answers whatever AddDataSheets has just written. It is anchored back at A1, so a sheet whose first written row sits below the header band still comes out formatted from the top, the way the whole-grid pass left it.

The .Rows(1) lines stay as they are. A header band across one row is cheap, and it is what makes the first row read as a header on a sheet with no data under it.

Parameters:

• outsh: Worksheet. The worksheet to format.

RemoveGridLines #

remove-gridlines

Remove gridlines from an analysis output sheet

Signature:

Private Sub RemoveGridLines(ByVal sh As Worksheet)

Parameters:

• sh: Worksheet. The worksheet to update.

Private - Error Handling

ThrowError #

throw-error

Raise a ProjectError-based exception

Signature:

Private Sub ThrowError(ByVal errNumber As ProjectError, ByVal message As String)

Wrapper around Err.Raise that standardises the source to CLASS_NAME.

Parameters:

• errNumber: ProjectError. The error code to raise.
• message: String. Human-readable description of the failure.

Throws:

• ProjectError. Always raises the specified error.

• AnalysisOutput.cls
• AnaTabIds.cls
• ChoiceKeys.cls
• ExportButton.cls
• LLImporter.cls
• LLdictionary.cls
• LLGeo.cls
• Linelist.cls
• LinelistSpecs.cls
• LLDataEntry.cls
• LLLog.cls
• LLTranslation.cls
• DiseaseExporter.cls
• SetupErrors.cls
• LinelistRun.bas
• TestExportOtherLinelist.bas
• TestLLExporter.bas
• TestLLExporterTiming.bas
• TestLLImporter.bas
• TestLLImportTiming.bas
• TestLLdictionary.bas
• TestLLGeo.bas
• TestLinelistRun.bas
• TestLLLog.bas
• TestSetupErrors.bas
• TestShowHide.bas