SectionBuilder

Builds all the sections, subsections and variables of one linelist worksheet. Given a sheet name and a dictionary start row, Build scans the dictionary for the rows of that sheet, groups them by section and subsection, hands each variable to a VarWriter, and writes the merged section and subsection headers with their formatting. The layer byte picks the HList layout or the VList one. Entries filed along the way leave through HasCheckings and CheckingValues.

ONE PASS OVER THE DICTIONARY

Build reads the five dictionary columns it groups on into arrays, in five crossings, and every boundary scan after that runs in memory.

ONE WRITER FOR THE SHEET

Build creates the VarWriter and hands it to every subsection. The writer holds a hidden name store, the dictionary block, the design format and the translated messages, and all four cover a whole sheet.

THE BOUNDARIES ARE LEFT BEHIND ON THE SHEET

Every section Build lays out is also recorded in a SectionMap, so that a later session can act on a whole section without walking the dictionary again. The map is cleared at the head of the build and one block is added per section, holding the first and last position and the section title.

The map is written on the main sheet alone. The printed companion carries the same columns and the CRF carries its own, and neither is offered a section action today.

Depends on: LinelistSpecs, LLdictionary, LLFormat, DropdownLists, VarWriter, SectionMap, Checking, Checking support, Row/column constants for section and subsection headers, The CRF companion places a section four rows above the first variable of the, section and a subsection three rows above.

Version: 1.2 (2026-08-06)

Instantiation

Create #

create

Create a new SectionBuilder instance

Signature:

Public Function Create(ByVal layer As Byte, _
                       ByVal specs As LinelistSpecs, _
                       ByVal wksh As Worksheet, _
                       Optional ByVal printWksh As Worksheet = Nothing, _
                       Optional ByVal crfWksh As Worksheet = Nothing, _
                       Optional ByVal dropdownObj As DropdownLists = Nothing, _
                       Optional ByVal customDropdownObj As DropdownLists = Nothing) As SectionBuilder

Factory method on the predeclared instance. Validates that specs and wksh are not Nothing and that the layer names a SectionBuilderMode member, then initialises a new SectionBuilder with the given layer, specifications, and optional companion worksheets and dropdown managers.

Parameters:

  • layer: Byte. SectionBuilderMode enum value (HList=1, VList=2).
  • specs: LinelistSpecs. The linelist specifications object.
  • wksh: Worksheet. The target main worksheet.
  • printWksh: Optional Worksheet. Printed companion worksheet (HList only).
  • crfWksh: Optional Worksheet. CRF companion worksheet (HList only).
  • dropdownObj: Optional DropdownLists. Dropdown lists manager.
  • customDropdownObj: Optional DropdownLists. Custom dropdown lists manager.

Returns: SectionBuilder. A fully initialised instance ready for use.

Throws:

  • ProjectError.ObjectNotInitialized When specs or wksh is Nothing.
  • ProjectError.InvalidArgument When layer names neither SectionBuilderMode member.
Entry Point

Build #

build

Build all sections, subsections, and variables for a sheet

Signature:

Public Sub Build(ByVal sheetName As String, ByVal startRow As Long)

Reads the dictionary columns it groups on into memory, then scans them for rows matching sheetName starting from startRow. Groups consecutive rows by their "main section" value, delegates to BuildSubSections for subsection grouping and variable writing, and applies merged-cell section header formatting. Stops when the sheet name changes or the dictionary ends.

Each section is also recorded in the sheet's SectionMap as it is laid out. The map is opened before the writer runs, because opening it reads every hidden name the sheet holds and the writer adds three to five of them per variable.

Parameters:

  • sheetName: String. The sheet name as it appears in the dictionary "sheet name" column.
  • startRow: Long. The first data row index in the dictionary for this sheet (1-based).
Checkings

HasMilestones #

has-milestones

Whether the writer filed any per-variable milestone

Signature:

Public Property Get HasMilestones() As Boolean

Returns: Boolean. True once a variable of this sheet was written.

MilestoneValues #

milestone-values

The writer's per-variable record

Signature:

Public Property Get MilestoneValues() As Checking

Handed on as the writer holds it. One writer serves the whole sheet, so no merge is owed here, and the store stays out of the __check worksheet.

Returns: Checking. The milestone entries, or Nothing.

VariablesWritten #

variables-written

How many variables the writer put on the sheet

Signature:

Public Property Get VariablesWritten() As Long

Returns: Long. The number of variables written.

SectionsPlaced #

sections-placed

How many sections were laid out on the sheet

Signature:

Public Property Get SectionsPlaced() As Long

Returns: Long. The number of sections placed.

Internal members (not exported)

Instance Sealing

Seal #

seal

Seal the instance against further setup writes

Signature:

Public Sub Seal()

GuardNotSealed #

guard-not-sealed

Guard a setup-only setter against post-seal writes

Signature:

Private Sub GuardNotSealed(ByVal propName As String)

Parameters:

Internal Setters

InternalLayer #

internal-layer

Assign the layer mode

Signature:

Public Property Let InternalLayer(ByVal layer As Byte)

Properties used by the factory only during instantiation.

Parameters:

InternalSpecs #

internal-specs

Assign the linelist specifications

Signature:

Public Property Set InternalSpecs(ByVal specs As LinelistSpecs)

Parameters:

InternalWksh #

internal-wksh

Assign the target main worksheet

Signature:

Public Property Set InternalWksh(ByVal wksh As Worksheet)

Parameters:

InternalPrintWksh #

internal-print-wksh

Assign the printed companion worksheet

Signature:

Public Property Set InternalPrintWksh(ByVal wksh As Worksheet)

Parameters:

InternalCRFWksh #

internal-crf-wksh

Assign the CRF companion worksheet

Signature:

Public Property Set InternalCRFWksh(ByVal wksh As Worksheet)

Parameters:

InternalDropdown #

internal-dropdown

Assign the dropdown lists manager

Signature:

Public Property Set InternalDropdown(ByVal dropdownObj As DropdownLists)

Parameters:

InternalCustomDropdown #

internal-custom-dropdown

Assign the custom dropdown lists manager

Signature:

Public Property Set InternalCustomDropdown(ByVal dropdownObj As DropdownLists)

Parameters:

Cached Accessors

Dictionary #

dictionary

Lazy-loaded dictionary accessor

Signature:

Private Property Get Dictionary() As LLdictionary

Lazy-loaded accessors for frequently used dependency objects.

Returns: LLdictionary. The linelist dictionary.

DesignFormat #

design-format

Design format accessor

Signature:

Private Property Get DesignFormat() As LLFormat

Held rather than resolved per call: every section and every subsection asks for it.

Returns: LLFormat. The formatting object from the specifications.

Dictionary Reading

LoadDictionaryColumns #

load-dictionary-columns

Read the columns the build groups on into memory

Signature:

Private Sub LoadDictionaryColumns()

Five crossings for the whole build. crf index is read under a guard because a dictionary that has not been prepared does not carry it, and the CRF companion is optional anyway.

ColumnValues #

column-values

Read a single-column Range as a 1-based list of values

Signature:

Private Function ColumnValues(ByVal columnRng As Range) As Variant

Range.Value hands back a plain value for one cell and a two-dimensional array otherwise. This flattens both cases so callers see one shape.

Parameters:

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

TextAt #

text-at

Read one entry of a loaded column as text

Signature:

Private Function TextAt(ByVal values As Variant, ByVal rowNumber As Long) As String

A dictionary cell can hold an error value or a Null, and CStr raises on both.

Parameters:

Returns: String. The text, or an empty string.

NumberAt #

number-at

Read one entry of a loaded column as a whole number

Signature:

Private Function NumberAt(ByVal values As Variant, ByVal rowNumber As Long) As Long

One rule for both index columns: a value that is empty or is not a number answers 0, and every caller tests for 0. column index and crf index used to be converted with a bare CLng, guarded at three of the eight sites that did it.

Parameters:

Returns: Long. The stored number, or 0.

Section Boundary Detection

FindSectionEnd #

find-section-end

Find the last row of a section

Signature:

Private Function FindSectionEnd(ByVal startRow As Long, _
                                 ByVal sectionName As String, _
                                 ByVal sheetName As String) As Long

Starting from startRow, scans forward until the section name or sheet name changes, then returns the row just before the change. When the end of the data is reached without a change, returns the last data row.

Parameters:

Returns: Long. The 1-based row index of the last row in the section.

Section Map

OpenSectionMap #

open-section-map

Open the sheet's section map and empty it

Signature:

Private Sub OpenSectionMap()

The boundaries of every section, left on the sheet for a later session. A section is a run of consecutive dictionary rows, and the map holds the first and last position of each run with the title alongside them. The reader of the map starts from a selected cell, so the position range is what it looks a section up by.

Called at the head of the build, before the writer adds its own names, and the clear is what stops a sheet rebuilt with fewer sections than last time from keeping the tail of the old map.

Depends on:

RecordSection #

record-section

Record the boundaries of one section in the map

Signature:

Private Sub RecordSection(ByVal sectionName As String, _
                           ByVal startRow As Long, _
                           ByVal endRow As Long)

The positions are the same column index values the headers were laid out on: a column number on an HList sheet, a row number on a VList one. A section whose first or last row carries no position is skipped, for the same reason FormatSection leaves it alone.

Parameters:

Subsection Processing

BuildSubSections #

build-sub-sections

Build all subsections within a section range

Signature:

Private Sub BuildSubSections(ByVal sectionStart As Long, _
                              ByVal sectionEnd As Long, _
                              ByVal sheetName As String, _
                              ByVal sectionName As String, _
                              ByVal writer As VarWriter)

Iterates through dictionary rows from sectionStart to sectionEnd, grouping consecutive rows by the "sub section" value. For each subsection group, delegates variable writing to BuildVariables and then applies subsection header formatting via FormatSubSection.

Parameters:

FindSubSectionEnd #

find-sub-section-end

Find the last row of a subsection

Signature:

Private Function FindSubSectionEnd(ByVal startRow As Long, _
                                    ByVal subSectionName As String, _
                                    ByVal sectionName As String, _
                                    ByVal sheetName As String, _
                                    ByVal maxRow As Long) As Long

Starting from startRow, scans forward until the subsection name, section name, or sheet name changes, then returns the row just before the change. Constrained by maxRow (the section boundary).

Parameters:

Returns: Long. The 1-based row index of the last row in the subsection.

Variable Building

BuildVariables #

build-variables

Build all variables within a subsection range

Signature:

Private Sub BuildVariables(ByVal subSecStart As Long, _
                            ByVal subSecEnd As Long, _
                            ByVal writer As VarWriter)

Walks the subsection and hands each variable name to the writer Build created for the sheet.

Parameters:

Depends on:

Section Formatting

FormatSection #

format-section

Format a section header

Signature:

Private Sub FormatSection(ByVal sectionName As String, _
                           ByVal startRow As Long, _
                           ByVal endRow As Long)

Dispatches to FormatHListSection or FormatVListSection based on the current layer.

Parameters:

FormatHListSection #

format-hlist-section

Format an HList section header

Signature:

Private Sub FormatHListSection(ByVal sectionName As String, _
                                ByVal startRow As Long, _
                                ByVal endRow As Long)

Merges cells horizontally across the column range at HLIST_SEC_ROW on the main sheet, writes the section name, and applies HListSection formatting. Repeats for the printed companion when available. Also writes a CRF section header when a CRF worksheet is present, using the first variable's "crf index" to calculate the CRF row offset. The CRF line is read inside the CRF guard, so a linelist with no CRF companion pays nothing for it.

Parameters:

FormatVListSection #

format-vlist-section

Format a VList section header

Signature:

Private Sub FormatVListSection(ByVal sectionName As String, _
                                ByVal startRow As Long, _
                                ByVal endRow As Long)

Merges cells vertically across the row range at VLIST_SEC_COL on the main sheet, writes the section name, and applies VListSection formatting. No companion sheets are involved for VList layers.

Parameters:

Subsection Formatting

FormatSubSection #

format-sub-section

Format a subsection header

Signature:

Private Sub FormatSubSection(ByVal subSectionName As String, _
                              ByVal startRow As Long, _
                              ByVal endRow As Long)

Dispatches to FormatHListSubSection or FormatVListSubSection based on the current layer.

Parameters:

FormatHListSubSection #

format-hlist-sub-section

Format an HList subsection header

Signature:

Private Sub FormatHListSubSection(ByVal subSectionName As String, _
                                   ByVal startRow As Long, _
                                   ByVal endRow As Long)

Merges cells horizontally across the column range at HLIST_SUBSEC_ROW on the main sheet, writes the subsection name, and applies HListSubSection formatting. Repeats for the printed companion when available. Also writes a CRF subsection header when a CRF worksheet is present.

Parameters:

FormatVListSubSection #

format-vlist-sub-section

Format a VList subsection header

Signature:

Private Sub FormatVListSubSection(ByVal subSectionName As String, _
                                   ByVal startRow As Long, _
                                   ByVal endRow As Long)

Merges cells vertically across the row range at VLIST_SUBSEC_COL on the main sheet, writes the subsection name, and applies VListSubSection formatting.

Parameters:

Error Handling

ThrowError #

throw-error

Raise an error with the class name as source

Signature:

Private Sub ThrowError(ByVal errNumber As Long, ByVal errorMessage As String)

Raises the number it is given with the class name as the source. Every guard in this class names a ProjectError member.

Parameters:

Checkings

HarvestWriterCheckings #

harvest-writer-checkings

Take the writer's entries once the sheet is built

Signature:

Private Sub HarvestWriterCheckings(ByVal writer As VarWriter)

The writer files one entry per problem, keyed by worksheet and variable, and Build merges them once when the sheet is done. The merge used to run per subsection against a writer whose keys started at 0 in every instance, so the second subsection of a sheet re-added key "0" and Checking.Add stopped the build.

Parameters:

LogSectionPlaced #

log-section-placed

File the milestone of one section laid out

Signature:

Private Sub LogSectionPlaced(ByVal sectionName As String, ByVal sheetName As String)

A sheet holds a few sections, so these entries go to the worksheet with the problems. The per-variable entries are the ones held back, and they travel through MilestoneValues.

Parameters:

Used in (9 file(s))