ShowHideLayout

Everything show/hide does to a worksheet. One instance covers one sheet of one layer. The entry list, ShowHide, says which variables are hidden; this class knows where each of them sits and writes the change. IsHidden and SetHidden read and write the hidden state, Size, SetSize and SizeWhenShown the width or height, IsVertical and SetOrientation the orientation of a printed entry, and FailureCount counts the writes that Excel refused.

THE AXIS IS DECIDED HERE AND NOWHERE ELSE

HList, Printed a column VList, CRF a row

A CRF holds one variable per row and reads its position from crf index, its own dictionary column.

PROTECTION IS BRACKETED INSIDE THE CLASS

Every method that writes to the sheet unprotects it first and protects it again after. Reads leave the protection alone. A loop that writes many positions calls BeginBatch first and EndBatch at the end, which holds one bracket open over the whole run.

A SIZE OF ZERO IS NEVER WRITTEN

Excel reports a hidden column as width 0 and hides a column that is set to width 0. SetSize refuses anything at or below zero. Hiding is SetHidden's work, and a blank size cell leaves the sheet alone.

Depends on: Passwords, Checking, The named cell a printed sheet puts at the head of its table, How far above the anchor the printed header row sits. PRINTSTART is on the, first line of the table, so the header the user reads is the line above it., The row height a turned header needs to stay readable

Version: 1.0 (2026-08-01)

Instantiation

Create #

create

Bind a layout to one worksheet of one layer

Signature:

Public Function Create(ByVal sh As Worksheet, _
                       ByVal layer As Byte, _
                       Optional ByVal passManager As Passwords, _
                       Optional ByVal baseTableName As String = vbNullString) As ShowHideLayout

The factory.

Parameters:

  • sh: Worksheet. The sheet the entries live on.
  • layer: Byte. A ShowHideWorksheetLayer value. Which layer the sheet belongs to.
  • passwords: Optional Passwords. The protection keys of the workbook. The layout writes without a protection bracket when it has none.
  • baseTableName: Optional String. The table name with no print_ prefix, which is what the PRINTSTART anchor is named after. Only the printed layer reads it.

Returns: ShowHideLayout. The layout, ready to read and write.

Throws:

  • ProjectError.ObjectNotInitialized When the worksheet is Nothing.
  • ProjectError.InvalidArgument When the layer is outside the four values.

Bind #

bind

Take the four things the layout works with

Signature:

Public Sub Bind(ByVal sh As Worksheet, _
                ByVal layer As Byte, _
                ByVal passManager As Passwords, _
                ByVal baseTableName As String)

Public because the factory calls it on a fresh instance.

Parameters:

  • sh: Worksheet. The sheet the entries live on.
  • layer: Byte. A ShowHideWorksheetLayer value. The layer of the sheet.
  • passwords: Passwords. The protection keys, or Nothing.
  • baseTableName: String. The table name with no print_ prefix.
Accessors

Wksh #

wksh

The worksheet this layout writes to

Signature:

Public Property Get Wksh() As Worksheet

What the layout is bound to.

Layer #

layer

The layer this layout was built for

Signature:

Public Property Get Layer() As Byte

Axis #

axis

Whether this layer hides rows or columns

Signature:

Public Property Get Axis() As Byte

SupportsOrientation #

supports-orientation

Whether the sheet has a header that can be turned

Signature:

Public Property Get SupportsOrientation() As Boolean

True on a printed sheet alone. A data entry sheet, a VList and a CRF all read their labels straight, so turning a header means nothing there.

FailureCount #

failure-count

How many writes Excel refused

Signature:

Public Property Get FailureCount() As Long

A position the sheet does not have, or a sheet that stays protected, leaves the write undone. The count says how many, so a caller can tell a silent run from a run that did nothing.

Visibility

IsHidden #

is-hidden

Whether a position is hidden on the sheet

Signature:

Public Function IsHidden(ByVal position As Long) As Boolean

Reading and writing whether a position is hidden.

Parameters:

  • position: Long. The column or row number.

Returns: Boolean. True when Excel is hiding it.

SetHidden #

set-hidden

Hide or show one position

Signature:

Public Sub SetHidden(ByVal position As Long, ByVal hidden As Boolean)

Parameters:

  • position: Long. The column or row number.
  • hidden: Boolean. True to hide it.
Size

Size #

size

The width or height of one position

Signature:

Public Function Size(ByVal position As Long) As Double

Reading and writing the width of a column or the height of a row.

Parameters:

  • position: Long. The column or row number.

Returns: Double. The column width or the row height. A hidden position answers 0, which is how Excel reports it.

SetSize #

set-size

Set the width or height of one position

Signature:

Public Sub SetSize(ByVal position As Long, ByVal newSize As Double)

A size at or below zero is refused. Excel hides a column that is set to width 0, so writing a missing size would hide the very entry the caller is trying to restore.

Parameters:

  • position: Long. The column or row number.
  • newSize: Double. The width or height to write.

SizeWhenShown #

size-when-shown

The size a position would have if it were visible

Signature:

Public Function SizeWhenShown(ByVal position As Long) As Double

A hidden position reports a size of 0, so a migration file built from a live sheet used to record 0 for every hidden column and bring it back at width 0 - a column the user can neither see nor grab. This reveals the position long enough to read its real size and hides it again.

Parameters:

  • position: Long. The column or row number.

Returns: Double. The size the position holds when visible.

Orientation

IsVertical #

is-vertical

Whether the printed header of one position is turned

Signature:

Public Function IsVertical(ByVal position As Long) As Boolean

The turned header of a printed sheet.

Excel stores a flat cell two ways: the angle 0, and the constant xlHorizontal (-4128), which is what a cell written with Orientation = 0 reads back as. Both mean flat, so both answer False here. Every flat header used to read as turned, because the test was angle <> 0.

Parameters:

  • position: Long. The column number.

Returns: Boolean. True when the header cell reads bottom to top.

SetOrientation #

set-orientation

Turn the printed header of one position, or lay it flat

Signature:

Public Sub SetOrientation(ByVal position As Long, ByVal vertical As Boolean)

The row height and the column autofit belong to the turned branch alone. They used to run on every action, so choosing Hide on a column whose header was turned laid that header flat on the way to hiding it, and the direction was gone when the user showed the column again.

Parameters:

  • position: Long. The column number.
  • vertical: Boolean. True to turn the header.
Protection bracket

BeginBatch #

begin-batch

Unprotect the sheet and keep it open

Signature:

Public Sub BeginBatch()

Holding the sheet open across a run of writes.

A loop that writes many positions calls this first, so the sheet is unprotected once instead of once per position. Every BeginBatch needs its EndBatch.

EndBatch #

end-batch

Close the bracket BeginBatch opened

Signature:

Public Sub EndBatch()
Internal members (not exported)

Helpers

AxisOf #

axis-of

The direction one layer occupies

Signature:

Private Function AxisOf(ByVal layer As Byte) As Byte

Private workers.

Parameters:

Returns: Byte. A ShowHideAxis value. Column for HList and Printed, row for VList and CRF.

PrintHeaderCell #

print-header-cell

The printed header cell of one column

Signature:

Private Function PrintHeaderCell(ByVal position As Long) As Range

The anchor is a named cell called <table name>_PRINTSTART, written by VarWriter on the first line of the printed table, and the table name it uses carries no print_ prefix. The header the reader sees is the line above it.

Parameters:

Returns: Range. The header cell, or Nothing when the anchor is absent.

UnprotectSheet #

unprotect-sheet

Unprotect the sheet, counting how deep the bracket is

Signature:

Private Sub UnprotectSheet()

Not called Unlock. Lock and Unlock are VBA file statements, so a line holding nothing but the word Unlock is read as one of those and the compile breaks.

THE SCREEN IS HELD HERE TOO

Hiding a column repaints the sheet. Every write in this class comes through this bracket, so the screen is held from the moment the sheet opens until the moment it closes again, and a run of writes repaints once at the end instead of once per position. That is what made the show/hide forms flicker: applying a saved layout writes every entry in turn, and SizeWhenShown deliberately shows a column and hides it again to measure it, which was a visible flash per column.

The value the bracket opened on is what gets put back, so a caller that had already turned the screen off keeps it off when the bracket closes.

ReprotectSheet #

reprotect-sheet

Protect the sheet and release the screen once the outermost bracket closes

Signature:

Private Sub ReprotectSheet()

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 (11 file(s))