SectionMap

The first and last position of every section of one worksheet, written when the sheet is built and read back whenever something needs to act on a whole section. Add records one block, Count, SectionNameAt, StartAt and EndAt read one back, IndexAtPosition finds the block a position falls in, and IndicesInRange finds every block a span touches. Clear drops the lot.

A SECTION IS A BLOCK OF POSITIONS

A block is identified by its position range, and the title is carried for a person to read. Two reasons for that way round:

The caller holds a cell address. The section toggle starts from what the user has selected, so the question it asks is "which section covers this column", and a position is what it has to ask with.

SectionBuilder groups CONSECUTIVE dictionary rows, so a run of rows is what a block means to the class that writes it. LLdictionary.Prepare sorts the dictionary by sheet and main section before it derives column index, which gives one run per title today. Keying on the range holds either way and costs the same.

WHAT A POSITION MEANS

The same thing it means to ShowHide and ShowHideLayout: a column number on an HList sheet and a row number on a VList one. Both come from the dictionary column index column, which is what SectionBuilder lays its headers out on.

WHERE THE MAP LIVES

In the hidden names of the sheet itself, one name per block:

section_map_count how many blocks the sheet carries section_map_ "||" for block n

One name carries one whole block. A defined name holds its value in its RefersTo formula and that string is length capped, so a sheet with thirty sections would run a single name holding them all past the cap. The section title is written last and truncated, which keeps the two numbers safe even when a designer writes a long one.

Depends on: HiddenNames, BetterArray, Checking, The hidden names the map is kept under, What separates the two numbers and the name inside one stored block, How many fields a stored block holds. The title is the last field, so the, split is capped here and a title carrying the separator arrives whole., How much of a section name is kept. A defined name holds its value in its, RefersTo formula and that string is length capped, so the name is cut before, it can push the two numbers out.

Version: 1.0 (2026-08-06)

Instantiation

Create #

create

Read the section map of one worksheet

Signature:

Public Function Create(ByVal wksh As Worksheet) As SectionMap

The factory and the load it runs.

A sheet with no map on it answers a Count of 0. Every reader tests the count before it reads a block, so an unbuilt sheet and a sheet built before this class existed both come back through the same path.

Parameters:

  • wksh: Worksheet. The sheet the map belongs to.

Returns: SectionMap. The map, ready to read and to add to.

Throws:

  • ProjectError.ObjectNotInitialized When the worksheet is Nothing.

Initialise #

initialise

Bind to a sheet and load the blocks it carries

Signature:

Public Sub Initialise(ByVal wksh As Worksheet)

Public because the factory calls it on a fresh instance.

Parameters:

  • wksh: Worksheet. The sheet the map belongs to.
Accessors

Wksh #

wksh

The worksheet this map belongs to

Signature:

Public Property Get Wksh() As Worksheet

What the map holds.

Count #

count

How many section blocks the sheet carries

Signature:

Public Property Get Count() As Long

SectionNameAt #

section-name-at

The title of one block

Signature:

Public Function SectionNameAt(ByVal index As Long) As String

Parameters:

  • index: Long. A one based block index.

Returns: String. The main section value the block was built from. A section the dictionary left blank answers an empty string.

Throws:

  • ProjectError.InvalidArgument When the index is out of range.

StartAt #

start-at

The first position of one block

Signature:

Public Function StartAt(ByVal index As Long) As Long

Parameters:

  • index: Long. A one based block index.

Returns: Long. The column number on an HList sheet, the row number on a VList one.

Throws:

  • ProjectError.InvalidArgument When the index is out of range.

EndAt #

end-at

The last position of one block

Signature:

Public Function EndAt(ByVal index As Long) As Long

Parameters:

  • index: Long. A one based block index.

Returns: Long. The column number on an HList sheet, the row number on a VList one.

Throws:

  • ProjectError.InvalidArgument When the index is out of range.
Lookup

IndexAtPosition #

index-at-position

The block one position falls in

Signature:

Public Function IndexAtPosition(ByVal position As Long) As Long

Finding the block a selection lands in.

Parameters:

  • position: Long. A column number or a row number.

Returns: Long. The one based block index, or 0 when no block covers the position.

IndicesInRange #

indices-in-range

Every block a span of positions touches

Signature:

Public Function IndicesInRange(ByVal firstPos As Long, ByVal lastPos As Long) As BetterArray

What a selection of more than one cell asks. A block is answered when it overlaps the span at all, which is what lets a user reach a section whose every position is hidden: selecting from the visible position before it to the visible position after it covers the whole gap.

The two bounds are read in either order, so a selection made right to left answers the same blocks as one made left to right.

Parameters:

  • firstPos: Long. One end of the span.
  • lastPos: Long. The other end of the span.

Returns: BetterArray. The one based block indices, in map order. Empty when the span touches no block.

Writing

Clear #

clear

Drop every block the sheet carries

Signature:

Public Sub Clear()

What SectionBuilder calls while it builds a sheet.

Called before a rebuild. A sheet rebuilt with fewer sections than last time would otherwise keep the tail of the old map, and those stale blocks point at positions the new sheet gives to something else.

Add #

add

Record one section block

Signature:

Public Function Add(ByVal sectionName As String, _
                    ByVal startPos As Long, _
                    ByVal endPos As Long) As Long

Parameters:

  • sectionName: String. The main section value, or an empty string.
  • startPos: Long. The first position of the block.
  • endPos: Long. The last position of the block.

Returns: Long. The one based index the block was filed under.

Throws:

  • ProjectError.InvalidArgument When startPos is below 1 or endPos is before startPos.
Internal members (not exported)

Storage

LoadBlocks #

load-blocks

Read every stored block into memory

Signature:

Private Sub LoadBlocks()

Reading and writing the hidden names the map is kept in.

A block whose name is missing or unreadable ends the load. The count and the blocks are written together, so a gap means the sheet was interrupted mid-build and what follows the gap cannot be trusted.

BlockName #

block-name

The hidden name one block is kept under

Signature:

Private Function BlockName(ByVal index As Long) As String

Parameters:

Returns: String. The name identifier.

Encode #

encode

Write a block as one stored string

Signature:

Private Function Encode(ByVal sectionName As String, _
                        ByVal startPos As Long, _
                        ByVal endPos As Long) As String

Parameters:

Returns: String. The stored form.

Decode #

decode

Read a stored string back into a block

Signature:

Private Function Decode(ByVal raw As String, _
                        ByRef sectionName As String, _
                        ByRef startPos As Long, _
                        ByRef endPos As Long) As Boolean

The split is capped at three fields, which lets the last field hold a section title carrying the separator and still arrive whole.

Parameters:

Returns: Boolean. True when the string held a usable block.

TrimmedName #

trimmed-name

Cut a section title down to what a defined name can carry

Signature:

Private Function TrimmedName(ByVal sectionName As String) As String

Parameters:

Returns: String. The title, at most MAX_NAME_LENGTH characters.

ReadCount #

read-count

Read how many blocks the sheet says it carries

Signature:

Private Function ReadCount() As Long

Returns: Long. The stored count, or 0.

WriteCount #

write-count

Write how many blocks the sheet carries

Signature:

Private Sub WriteCount(ByVal newCount As Long)

Parameters:

ReadValue #

read-value

Read one stored block, answering empty when it is absent

Signature:

Private Function ReadValue(ByVal nameId As String) As String

Parameters:

Returns: String. The stored form, or an empty string.

WriteValue #

write-value

Write one stored block

Signature:

Private Sub WriteValue(ByVal nameId As String, ByVal value As String)

EnsureName then SetValue, because EnsureName leaves a name that already exists holding whatever it held before. Clear removes the old names first, so this is the belt to that pair of braces.

Parameters:

RemoveIfPresent #

remove-if-present

Drop one stored block when the sheet carries it

Signature:

Private Sub RemoveIfPresent(ByVal nameId As String)

Parameters:

Helpers

EnsureValidIndex #

ensure-valid-index

Refuse a block index outside the map

Signature:

Private Sub EnsureValidIndex(ByVal index As Long)

Private guards.

Parameters:

Throws:

ThrowError #

throw-error

Raise a project error naming this class

Signature:

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

Parameters:

Used in (10 file(s))