ShowHideStore

The table that carries a user's show/hide choices from one session to the next, and from one workbook to another during a migration. One table holds all four layers, told apart by the layer column, and every saved layout, told apart by the layout_name column. Save writes choices, Load reads them back, Clear drops them, HasLayer says whether a layer has rows, and HasTable, Table and RowCount answer where the store stands.

THE STORE MAKES ITS OWN TABLE

Create and CreateOnSheet build the ListObject when it is absent, and add the layout_name column to a table an older build made without it.

CreateForRead binds to whatever table a workbook already carries and builds nothing, which is what reading a migration file needs.

SEVEN COLUMNS

layer hlist / printed / vlist / crf field_key the variable name, and the key rows are matched on header_text for a person reading the sheet hidden_flag true / false entry_size the width or the height the entry has when shown, blank when unknown orientation vertical / horizontal, blank off the printed layer layout_name empty on the rows of the current state, the layout's name on the rows of a saved layout

The size and the hidden flag are two separate columns. A size at or below zero means "leave the sheet alone".

A file written by an older build carries column_width where this one writes entry_size, and no layout_name at all. Load reads either size name, and every row of a file without the layout column reads as current state.

SAVED LAYOUTS

A saved layout is a named copy of the same rows. Save and Load take the name; LayoutNames, HasLayout and LayoutCount answer what is stored; DeleteLayout and RenameLayout manage the list; MergeLayoutsFrom copies another store's layouts in, which is how they travel through a migration file. The store holds at most MaxSavedLayouts names, and Save refuses the one past the cap.

SAVE REPLACES BY KEY

Save replaces the rows whose layer, layout name and field key match the entry list, and keeps every other row. Several worksheets share one layer - every HList sheet writes hlist rows - so replacing a whole layer would drop the other sheets' rows on every save. Variable names are unique across the workbook, which is what makes the key safe. A row whose variable left the dictionary lingers; Load skips it.

Depends on: ShowHide, ShowHideLayout, Checking, BetterArray, The worksheet a linelist keeps its show/hide table on, and the name a, linelist generated before the internal-sheet rename gave it, The table this class writes, and the name an older build wrote, The per-sheet tables the first show/hide design tiled across the same, worksheet. Both designs answer to ListObjects(1), so they cannot share a sheet., How many saved layouts one store holds at most

Version: 1.1 (2026-08-11)

Instantiation

Create #

create

Open the store of a workbook, making the table when it is absent

Signature:

Public Function Create(ByVal wkb As Workbook) As ShowHideStore

Three ways in: two that provision, one that reads what is there.

Parameters:

wkb: Workbook. The workbook holding the __show_hide worksheet.

Returns: ShowHideStore. A store ready to save and load.

Throws:

ProjectError.ObjectNotInitialized When the workbook is Nothing.
ProjectError.ElementNotFound When the workbook has no __show_hide worksheet.

CreateOnSheet #

create-on-sheet

Open the store on one worksheet, making the table when it is absent

Signature:

Public Function CreateOnSheet(ByVal sh As Worksheet) As ShowHideStore

Any per-sheet table of the first show/hide design is deleted on the way, since both designs read ListObjects(1) and the two layouts hold different columns.

Parameters:

sh: Worksheet. The sheet to keep the table on.

Returns: ShowHideStore. A store ready to save and load.

Throws:

ProjectError.ObjectNotInitialized When the worksheet is Nothing.

CreateForRead #

create-for-read

Bind to the table a worksheet already carries

Signature:

Public Function CreateForRead(ByVal sh As Worksheet) As ShowHideStore

Provisions nothing, which is what reading another workbook's migration file needs. HasTable says whether anything was found.

Parameters:

sh: Worksheet. The sheet to read.

Returns: ShowHideStore. A store bound to what is there.

Bind #

bind

Take the sheet and the table the factory resolved

Signature:

Public Sub Bind(ByVal sh As Worksheet, ByVal lo As ListObject)

Public because the factories call it on a fresh instance.

Parameters:

sh: Worksheet. The sheet the table lives on.
lo: ListObject. The table, or Nothing when the sheet carries none.

Accessors

HasTable #

has-table

Whether a table was found or made

Signature:

Public Property Get HasTable() As Boolean

What the store is bound to.

Table #

table

The ListObject the store reads and writes

Signature:

Public Property Get Table() As ListObject

RowCount #

row-count

How many rows the store holds, over all four layers

Signature:

Public Property Get RowCount() As Long

HasLayer #

has-layer

Whether the store holds any row of one layer

Signature:

Public Function HasLayer(ByVal layer As Byte) As Boolean

Parameters:

layer: Byte. A ShowHideWorksheetLayer value. The layer to look for.

Returns: Boolean. True when at least one row carries that layer.

Saving

Save #

save

Write one layer's choices, leaving every other row alone

Signature:

Public Sub Save(ByVal entries As ShowHide, _
                Optional ByVal layout As ShowHideLayout, _
                Optional ByVal layoutName As String = vbNullString)

Writing an entry list into the table.

The rows whose layer, layout name and field key match the entry list are replaced; everything else is carried over untouched. So four layers share one table, several worksheets share one layer, and the current state sits beside the saved layouts.

Without a layout the store writes visibility alone, which is what closing the form needs. With one it also records the size each entry has when shown and, on a printed sheet, the direction of its header - which is what a migration export and a saved layout need.

Parameters:

entries: ShowHide. The entry list to write.
layout: Optional ShowHideLayout. The sheet to read sizes from.
layoutName: Optional String. Empty writes the current state; a name writes a saved layout.

Throws:

ProjectError.ObjectNotInitialized When the entry list is Nothing.
ProjectError.ElementNotFound When the store has no table, or a name is given and the table has no layout_name column.
ProjectError.InvalidArgument When a new name would pass the cap.

Clear #

clear

Drop every row of one layer of one state

Signature:

Public Sub Clear(ByVal layer As Byte, _
                 Optional ByVal layoutName As String = vbNullString)

Parameters:

layer: Byte. A ShowHideWorksheetLayer value. The layer to drop.
layoutName: Optional String. Empty drops current-state rows; a name drops that layout's rows of the layer. DeleteLayout drops a whole layout.

Throws:

ProjectError.ElementNotFound When the store has no table.

Load #

load

Read one layer's saved choices into an entry list

Signature:

Public Function Load(ByVal entries As ShowHide, _
                     Optional ByVal layout As ShowHideLayout, _
                     Optional ByVal layoutName As String = vbNullString) As Long

Reading the table back into an entry list.

Only the rows of the entry list's own layer and of the named state are read. A variable the list does not carry is skipped, which is what happens when the dictionary changed between two sessions. On a file written before saved layouts existed, every row reads as current state.

With a layout, the saved size and header direction are applied to the sheet as well. Visibility is left to ShowHide.Apply, so a caller that wants the sheet to match the list calls Load and then Apply.

Parameters:

entries: ShowHide. The entry list to fill.
layout: Optional ShowHideLayout. The sheet to size.
layoutName: Optional String. Empty reads the current state; a name reads that saved layout.

Returns: Long. The number of rows that matched an entry.

Saved layouts

MaxSavedLayouts #

max-saved-layouts

How many saved layouts one store holds at most

Signature:

Public Property Get MaxSavedLayouts() As Long

The named copies of the show/hide state, and their bookkeeping.

LayoutNames #

layout-names

The saved layout names, in the order they first appear

Signature:

Public Function LayoutNames() As BetterArray

Names are compared without regard to case, and each keeps the casing of its first row. The current state has no name and is never listed.

Returns: BetterArray. The distinct names, empty when nothing is saved.

LayoutCount #

layout-count

How many saved layouts the store holds

Signature:

Public Function LayoutCount() As Long

HasLayout #

has-layout

Whether a saved layout of one name is stored

Signature:

Public Function HasLayout(ByVal layoutName As String) As Boolean

Parameters:

layoutName: String. The name to look for, compared without regard to case.

Returns: Boolean. True when at least one row carries the name.

DeleteLayout #

delete-layout

Drop every row of one saved layout, over all its layers

Signature:

Public Sub DeleteLayout(ByVal layoutName As String)

Parameters:

layoutName: String. The name to drop. The current state has no name and is cleared through Clear.

Throws:

ProjectError.ElementNotFound When the store has no table.
ProjectError.InvalidArgument When the name is empty.

RenameLayout #

rename-layout

Give one saved layout another name

Signature:

Public Sub RenameLayout(ByVal oldName As String, ByVal newName As String)

Renaming onto a name the store already holds is refused, because the rows of the two layouts would merge and neither could be told apart again. Renaming a layout onto its own name with other casing is allowed.

Parameters:

oldName: String. The name the layout holds.
newName: String. The name to give it.

Throws:

ProjectError.ElementNotFound When the store has no table or no layout of the old name.
ProjectError.InvalidArgument When a name is empty or the new name is already stored.

MergeLayoutsFrom #

merge-layouts-from

Copy another store's saved layouts into this one

Signature:

Public Function MergeLayoutsFrom(ByVal source As ShowHideStore) As BetterArray

How saved layouts travel: the exporter merges the linelist's layouts into the migration file's store, and the importer merges the file's layouts back. A name both stores hold is taken from the source, replacing the local rows. A new name is taken while the cap has room; the rest are skipped and answered to the caller, so an import can report them. Rows are copied as they stand - a variable the receiving dictionary does not carry is skipped by Load later.

Parameters:

source: ShowHideStore. The store to copy from.

Returns: BetterArray. The names that were skipped over the cap, empty when every layout came across.

Internal members (not exported)

Helpers

Provision #

provision

Find the store table on a sheet, or build it

Signature:

Private Function Provision(ByVal sh As Worksheet) As ListObject

Private workers of the three factories and the two writers.

Parameters:

sh: Worksheet. The sheet to provision.

Returns: ListObject. The store table.

EnsureLayoutColumn #

ensure-layout-column

Add the layout_name column to a table an older build made

Signature:

Private Sub EnsureLayoutColumn(ByVal lo As ListObject)

A linelist generated before saved layouts existed carries a six column table. The column lands at the right edge and every existing row keeps an empty cell there, which is exactly what a current-state row looks like.

Parameters:

lo: ListObject. The table to check.

NamedTable #

named-table

Look for a store table by name

Signature:

Private Function NamedTable(ByVal sh As Worksheet) As ListObject

The name this class writes comes first, then the name an older build wrote. A table under any other name is left alone, so provisioning never adopts a table that holds something else.

Parameters:

sh: Worksheet. The sheet to search.

Returns: ListObject. The table, or Nothing.

FindTable #

find-table

Look for a store table already on a sheet

Signature:

Private Function FindTable(ByVal sh As Worksheet) As ListObject

Either of the two known names first, then whatever single table the sheet carries. The last step is what lets a migration file written by a build this one has never heard of still be read.

Parameters:

sh: Worksheet. The sheet to search.

Returns: ListObject. The table, or Nothing.

DropLegacyTables #

drop-legacy-tables

Delete the per-sheet tables of the first show/hide design

Signature:

Private Sub DropLegacyTables(ByVal sh As Worksheet)

Parameters:

sh: Worksheet. The sheet to sweep.

KeptRows #

kept-rows

Every row a save or a clear carries over untouched

Signature:

Private Function KeptRows(ByVal token As String, _
                          ByVal layerCol As Long, _
                          ByVal layoutName As String, _
                          ByVal layoutCol As Long, _
                          ByVal entries As ShowHide, _
                          ByVal fieldCol As Long, _
                          ByRef keptCount As Long) As Variant

A row is replaced when its layer, its layout name and its field key all match. With no entry list, every key of the layer and state matches, which is what Clear asks for.

Parameters:

token: String. The layer token being replaced.
layerCol: Long. The index of the layer column.
layoutName: String. The state being replaced, empty for the current one.
layoutCol: Long. The index of the layout column, or 0 on an old table.
entries: ShowHide. The entry list whose keys are replaced, or Nothing.
fieldCol: Long. The index of the field key column, or 0.
keptCount: Long. Set to the number of rows kept.

Returns: Variant. A two dimensional block of the kept rows, or Empty.

AppendSourceLayoutRows #

append-source-layout-rows

Copy the accepted layout rows of another table into a block

Signature:

Private Function AppendSourceLayoutRows(ByRef block As Variant, _
                                        ByVal startCount As Long, _
                                        ByVal srcLo As ListObject, _
                                        ByVal srcRows As Variant, _
                                        ByVal accepted As Collection, _
                                        ByVal srcLayoutCol As Long) As Long

Columns are matched by header on both sides, so a source written by another build still lands in the right cells here.

Parameters:

block: Variant. The block being filled, sized by the caller.
startCount: Long. How many rows the block already holds.
srcLo: ListObject. The source table, for its headers.
srcRows: Variant. The source body, already read.
accepted: Collection. The layout names to copy, keyed on their lower case.
srcLayoutCol: Long. The source's layout column index.

Returns: Long. The row count of the block once the copies are in.

RowLayoutName #

row-layout-name

The layout name one row carries

Signature:

Private Function RowLayoutName(ByVal tableRows As Variant, _
                               ByVal rowIndex As Long, _
                               ByVal layoutCol As Long) As String

Parameters:

tableRows: Variant. The body block being read.
rowIndex: Long. The row to read.
layoutCol: Long. The layout column index, or 0 on an old table.

Returns: String. The trimmed name, empty for a current-state row.

LayoutMatches #

layout-matches

Whether two layout names are the same name

Signature:

Private Function LayoutMatches(ByVal firstName As String, _
                               ByVal secondName As String) As Boolean

Parameters:

firstName: String. One name, already trimmed.
secondName: String. The other, already trimmed.

Returns: Boolean. True when they match without regard to case.

InCollection #

in-collection

Whether a keyed Collection holds one name

Signature:

Private Function InCollection(ByVal keyed As Collection, _
                              ByVal layoutName As String) As Boolean

Parameters:

keyed: Collection. The collection, keyed on lower cased names.
layoutName: String. The name to look for.

Returns: Boolean. True when the key is there.

WriteAll #

write-all

Replace the whole table body with one block

Signature:

Private Sub WriteAll(ByVal block As Variant, ByVal rowCount As Long)

One write and one resize, whatever the row count. Writing row by row through ListRows.Add costs one Excel crossing per row, and a data sheet with two hundred variables closes its form four times slower for it.

Parameters:

block: Variant. The rows to write, one dimension per row and column.
rowCount: Long. How many rows of the block to keep.

TrimBlock #

trim-block

Cut a block down to the rows that are used

Signature:

Private Function TrimBlock(ByVal block As Variant, _
                           ByVal rowCount As Long, _
                           ByVal colCount As Long) As Variant

Parameters:

block: Variant. The oversized block.
rowCount: Long. How many rows to keep.
colCount: Long. How many columns to keep.

Returns: Variant. A block of exactly rowCount by colCount.

ColumnIndex #

column-index

Where one named column sits in the store's own table

Signature:

Private Function ColumnIndex(ByVal headerName As String) As Long

Parameters:

headerName: String. The column header to find.

Returns: Long. The one based column number, or 0 when the header is absent.

ColumnIndexOf #

column-index-of

Where one named column sits in any store table

Signature:

Private Function ColumnIndexOf(ByVal lo As ListObject, _
                               ByVal headerName As String) As Long

The merge reads another store's table through this, so both sides are matched by header and a source written by another build still maps.

Parameters:

lo: ListObject. The table to search.
headerName: String. The column header to find.

Returns: Long. The one based column number, or 0 when the header is absent.

SizeColumnIndex #

size-column-index

Where the size column sits, under either of its two names

Signature:

Private Function SizeColumnIndex() As Long

A file written by an older build calls it column_width.

Returns: Long. The one based column number, or 0 when neither name is there.

RequireTable #

require-table

Refuse to write when there is no table

Signature:

Private Sub RequireTable()

Throws:

ProjectError.ElementNotFound When the store is bound to nothing.

LayerToken #

layer-token

The word one layer is written under

Signature:

Private Function LayerToken(ByVal layer As Byte) As String

Parameters:

layer: Byte. A ShowHideWorksheetLayer value. The layer to name.

Returns: String. The token stored in the layer column.

Throws:

ProjectError.InvalidArgument When the layer is unknown.

OrientationToken #

orientation-token

The word a header direction is written under

Signature:

Private Function OrientationToken(ByVal vertical As Boolean) As String

Parameters:

vertical: Boolean. True when the header is turned.

Returns: String. "vertical" or "horizontal".

BoolToString #

bool-to-string

Write a hidden flag in a form no language changes

Signature:

Private Function BoolToString(ByVal value As Boolean) As String

Parameters:

value: Boolean. The flag to write.

Returns: String. "true" or "false".

ToBool #

to-bool

Read a stored hidden flag

Signature:

Private Function ToBool(ByVal value As Variant) As Boolean

Parameters:

value: Variant. The cell value.

Returns: Boolean. True when the cell says so.

ToSize #

to-size

Read a stored size, answering zero for anything unusable

Signature:

Private Function ToSize(ByVal value As Variant) As Double

Parameters:

value: Variant. The cell value.

Returns: Double. The size, or 0.

ThrowError #

throw-error

Raise a project error naming this class

Signature:

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

Parameters:

errNumber: ProjectError. The error to raise.
message: String. What went wrong.

Used in (9 file(s))

LLExporter.cls
LLImporter.cls
LLGeo.cls
Linelist.cls
ShowHide.cls
EventsLinelistButtons.bas
FormLogicAdvanced.bas
FormLogicShowHideSave.bas
TestShowHide.bas