SetupTranslationsTable

Factory helpers

InitialiseCore #

initialise-core

Store the supplied table and optional scope inside the current instance.

Signature:

Public Sub InitialiseCore(ByVal translationTable As ListObject, Optional ByVal stateScope As ApplicationState = Nothing)

Public configuration

HostWorksheet #

host-worksheet

Worksheet hosting the translations table.

Signature:

Public Property Get HostWorksheet() As Worksheet

TableName #

table-name

Name of the managed translations ListObject.

Signature:

Public Property Get TableName() As String

SetDisplayPrompts #

set-display-prompts

Allow callers to toggle display prompts used during updates.

Signature:

Public Sub SetDisplayPrompts(ByVal state As Boolean)

ResolveScope #

resolve-scope

Guarded access to the application scope.

Signature:

Private Function ResolveScope() As ApplicationState

Language management

EnsureLanguages #

ensure-languages

Ensure all provided languages exist as table headers.

Signature:

Public Sub EnsureLanguages(Optional ByVal lang As String = vbNullString)

TryGetWrittenLanguages #

try-get-written-languages

Collect language names written to the right of the table.

Signature:

Private Function TryGetWrittenLanguages(ByRef languages As String, ByRef typedCells As Range, ByVal newLang As String) As Boolean

Parameters:

• languages: ByRef String. Receives the semicolon separated language list.
• typedCells: ByRef Range. Receives the cells holding the typed names, to clear later.
• newLang: String. Extra language requested by the caller.

EnsureLanguageColumn #

ensure-language-column

Create the language column when it does not exist.

Signature:

Private Sub EnsureLanguageColumn(ByVal languageName As String)

PersistLanguageList #

persist-language-list

Persist the available languages into the worksheet hidden names.

Signature:

Private Sub PersistLanguageList()

SwitchDefaultLanguage #

switch-default-language

Promote the requested language column to become the primary language.

Signature:

Public Sub SwitchDefaultLanguage(ByVal languageName As String)

SwitchableLanguages #

switchable-languages

Retrieve the available languages excluding the default column.

Signature:

Public Function SwitchableLanguages() As BetterArray

Languages #

languages

Languages available for translation excluding the default column unless requested.

Signature:

Public Property Get Languages(Optional ByVal includeDefault As Boolean = False) As BetterArray

Language utilities

DefaultLanguageHeader #

default-language-header

Resolve the current default language header.

Signature:

Private Function DefaultLanguageHeader() As String

LanguageColumnIndex #

language-column-index

Resolve the column index for a provided language header.

Signature:

Private Function LanguageColumnIndex(ByVal languageName As String) As Long

ResolveLanguageColumn #

resolve-language-column

Resolve the data body range for a given language column.

Signature:

Private Function ResolveLanguageColumn(ByVal languageName As String) As Range

SwapLanguageColumns #

swap-language-columns

Swap the headers and the data between two language columns.

Signature:

Private Sub SwapLanguageColumns(ByVal firstIndex As Long, ByVal secondIndex As Long)

The data body of each column is read and written as one block, so a swap costs four Excel crossings whatever the row count. Number formats stay where they are: translation columns hold text. The translations table carries no totals row, so nothing here handles one.

UniqueTemporaryHeader #

unique-temporary-header

Generate a temporary header that does not collide with existing columns.

Signature:

Private Function UniqueTemporaryHeader() As String

Registry processing

ResetSequence #

reset-sequence

Reset the hidden update sequence counter to zero.

Signature:

Public Sub ResetSequence(ByVal registrySheet As Worksheet)

UpdateFromRegistry #

update-from-registry

Update the translations table using the supplied registry sheet.

Signature:

Public Sub UpdateFromRegistry(ByVal registrySheet As Worksheet, _
                             Optional ByVal languages As String = vbNullString)

The sequence counter is written after the rows settle. A run that fails halfway leaves the counter where it was, so the retry works at the same sequence and the rows the failed run tagged survive.

ProcessRegistry #

process-registry

Iterate all registry tables and process each watcher row.

Signature:

Private Sub ProcessRegistry(ByVal registrySheet As Worksheet, ByVal updateSequence As Long)

ProcessRegistryTable #

process-registry-table

Process a single registry ListObject.

Signature:

Private Sub ProcessRegistryTable(ByVal registryTable As ListObject, ByVal updateSequence As Long)

RegistryColumn #

registry-column

Resolve a registry column by header name, falling back to its published position.

Signature:

Private Function RegistryColumn(ByVal registryTable As ListObject, _
                                ByVal headerName As String, _
                                ByVal fallbackColumn As Long) As Long

RegistryColumnByHeader #

registry-column-by-header

Find a registry column by header name, answering 0 when the header is absent.

Signature:

Private Function RegistryColumnByHeader(ByVal registryTable As ListObject, _
                                        ByVal headerName As String) As Long

ThrowRegistryShape #

throw-registry-shape

Report a registry table that carries too few columns.

Signature:

Private Sub ThrowRegistryShape(ByVal registryTable As ListObject, ByVal columnCount As Long)

ShouldProcessRange #

should-process-range

Determine whether the named range should be processed this cycle.

Signature:

Private Function ShouldProcessRange(ByVal status As String, ByVal updateSequence As Long) As Boolean

ProcessSourceRange #

process-source-range

Dispatch processing based on the requested translation mode.

Signature:

Private Sub ProcessSourceRange(ByVal sourceRange As Range, ByVal rngTag As String, ByVal mode As String)

The mode is lowered and trimmed once, and every branch reads that one value. A watched range and a blank mode both carry no labels, so both are skipped without a word.

ProcessTextRange #

process-text-range

Add literal cell values to the translations table.

Signature:

Private Sub ProcessTextRange(ByVal sourceRange As Range, ByVal rngTag As String)

ProcessFormulaRange #

process-formula-range

Extract quoted portions of formulas and add them as chunks.

Signature:

Private Sub ProcessFormulaRange(ByVal sourceRange As Range, ByVal rngTag As String)

AddTokens #

add-tokens

Add every extracted formula token as a chunk.

Signature:

Private Sub AddTokens(ByVal tokens As BetterArray, ByVal rngTag As String)

EffectiveSourceRange #

effective-source-range

Narrow a source range to the part of the sheet that holds data.

Signature:

Private Function EffectiveSourceRange(ByVal sourceRange As Range) As Range

A defined name over a whole column answers with a 1,048,576 row array. The intersection with the used range keeps the read small.

ExtractFormulaChunks #

extract-formula-chunks

Extract quoted text segments from a formula while handling escaped quotes.

Signature:

Private Function ExtractFormulaChunks(ByVal formulaText As String) As BetterArray

The walk jumps from quote to quote with InStr, so the cost is one call per quote. Escaped double quotes are stripped first, which is what makes IF(A1="", ...) skip its empty comparison string.

Chunk management

AddChunk #

add-chunk

Add or update a translation chunk in the table.

Signature:

Private Sub AddChunk(ByVal label As String, ByVal rngTag As String)

AppendRow #

append-row

Record a new label in memory, to be written to the sheet in one batch.

Signature:

Private Sub AppendRow(ByVal label As String, ByVal rngTag As String)

FlushPendingRows #

flush-pending-rows

Grow the table once and write every new label in one block.

Signature:

Private Sub FlushPendingRows()

GrowTableRows #

grow-table-rows

Extend the managed table by the requested number of rows in one call.

Signature:

Private Sub GrowTableRows(ByVal extraRows As Long)

EnsureLabelCapacity #

ensure-label-capacity

Grow the label buffer geometrically so appends stay linear.

Signature:

Private Sub EnsureLabelCapacity(ByVal wanted As Long)

EnsureTagCapacity #

ensure-tag-capacity

Grow the tag buffer geometrically so appends stay linear.

Signature:

Private Sub EnsureTagCapacity(ByVal wanted As Long)

Label buffer

InitLabelBuffer #

init-label-buffer

Read the label column into a buffer and build the lookup for the cycle.

Signature:

Private Sub InitLabelBuffer()

LabelKey #

label-key

Build the lookup key for a label, one key per spelling.

Signature:

Private Function LabelKey(ByVal label As String) As String

The leading character keeps a numeric label legal as a Collection key. The trailing signature is what makes two spellings different keys: a Collection matches its own keys without regard to case, so dropping the label in on its own would fold "Hello" onto "hello" no matter how it was cased here.

CaseSignature #

case-signature

Spell a label's capitalisation out in digits.

Signature:

Private Function CaseSignature(ByVal label As String) As String

Digits carry no case of their own, so a Collection holds this part of the key exactly as written. A character reads 1 when it differs from its own lower case, which is the whole of what a case-insensitive comparison throws away.

RegisterLabelKey #

register-label-key

Record the row a label sits on, keeping the first row when the same spelling repeats.

Signature:

Private Sub RegisterLabelKey(ByVal label As String, ByVal rowIndex As Long)

FindLabelRow #

find-label-row

Find the row index for a label through the keyed lookup.

Signature:

Private Function FindLabelRow(ByVal label As String) As Long

LabelAt #

label-at

Read a label out of the in-memory buffer.

Signature:

Private Function LabelAt(ByVal rowIndex As Long) As String

Tag buffer

InitTagBuffer #

init-tag-buffer

Read the tag column into a buffer for batch writes.

Signature:

Private Sub InitTagBuffer()

A row that carries a label and no tag is given the manual tag. It was never claimed by a watched range, so it must survive the update.

FlushTagBuffer #

flush-tag-buffer

Write the accumulated tag buffer back to the tag column in one operation.

Signature:

Private Sub FlushTagBuffer()

ReleaseUpdateBuffers #

release-update-buffers

Drop the per-cycle buffers so a large table leaves no memory behind.

Signature:

Private Sub ReleaseUpdateBuffers()

Process tracking

TrackProcessedRange #

track-processed-range

Track the ranges processed during the current registry cycle.

Signature:

Private Sub TrackProcessedRange(ByVal rngName As String)

TrackUnresolvedRange #

track-unresolved-range

Record a registry range whose defined name could not be resolved.

Signature:

Private Sub TrackUnresolvedRange(ByVal rngName As String)

WasRangeProcessed #

was-range-processed

Determine whether a registry range was processed during the active cycle.

Signature:

Private Function WasRangeProcessed(ByVal rngName As String) As Boolean

ResetProcessedRanges #

reset-processed-ranges

Reset processed ranges tracking.

Signature:

Private Sub ResetProcessedRanges()

UnresolvedRanges #

unresolved-ranges

Registry ranges that failed to resolve during the last update.

Signature:

Public Property Get UnresolvedRanges() As BetterArray

RemoveObsoleteLabels #

remove-obsolete-labels

Remove labels whose source ranges were processed but whose tags are stale.

Signature:

Private Sub RemoveObsoleteLabels(ByVal updateSequence As Long)

A row that carries a label and no tag stays. Only a row with neither a label nor a tag is dropped, and that is the blank row a ListObject keeps when it holds no data.

DeduplicateLabels #

deduplicate-labels

Remove duplicate labels keeping the row with the oldest (lowest) tag sequence.

Signature:

Private Sub DeduplicateLabels()

DeleteRowsByIndex #

delete-rows-by-index

Delete the listed table rows, batching them into one Excel call each.

Signature:

Private Sub DeleteRowsByIndex(ByRef indices() As Long, ByVal count As Long)

The indices are sorted downward so a flushed batch leaves the remaining indices valid. Union slows down past a few thousand areas, so the batch is capped.

SortIndicesDescending #

sort-indices-descending

Sort a 1-based Long array in descending order (simple insertion sort).

Signature:

Private Sub SortIndicesDescending(ByRef arr() As Long, ByVal count As Long)

ParseTag #

parse-tag

Parse helper tag text into its components.

Signature:

Private Function ParseTag(ByVal tagText As String, ByRef rangeName As String, ByRef sequenceValue As Long) As Boolean

The split happens at the LAST separator, so a range name that itself carries the separator still yields its sequence number.

CreateBuffer #

create-buffer

Create a BetterArray buffer with deterministic bounds.

Signature:

Private Function CreateBuffer() As BetterArray

BuildDuplicateLine #

build-duplicate-line

Format a single duplicate-count line for reporting.

Signature:

Private Function BuildDuplicateLine(ByVal labelValue As String, ByVal occurrenceCount As Long) As String

Reporting

MissingLabels #

missing-labels

Count missing translation labels for a specific language.

Signature:

Public Function MissingLabels(ByVal lang As String) As Long

NumberOfMissing #

number-of-missing

Summarise missing translations per language and optionally prompt the user.

Signature:

Public Function NumberOfMissing() As String

AppendUnresolvedLine #

append-unresolved-line

Name the registry ranges that could not be resolved during the update.

Signature:

Private Sub AppendUnresolvedLine(ByVal messageLines As BetterArray)

UpdateMissingSummary #

update-missing-summary

Update internal missing summary.

Signature:

Private Sub UpdateMissingSummary(ByVal summary As BetterArray)

InternalMissingSummary #

internal-missing-summary

Cached missing-labels summary across languages.

Signature:

Public Property Get InternalMissingSummary() As BetterArray

UpdateDuplicatesSummary #

update-duplicates-summary

Build duplicate summary for every language column.

Signature:

Private Sub UpdateDuplicatesSummary()

InternalDuplicatesSummary #

internal-duplicates-summary

Cached duplicates summary across languages.

Signature:

Public Property Get InternalDuplicatesSummary() As BetterArray

DuplicateLabels #

duplicate-labels

Report duplicate translation labels when they exist.

Signature:

Public Function DuplicateLabels(ByRef duplicateMessage As String, Optional ByVal languageName As String = vbNullString) As Boolean

ComputeDuplicateSummary #

compute-duplicate-summary

Count duplicates for one language or for the label column.

Signature:

Private Function ComputeDuplicateSummary(ByVal languageName As String) As TDuplicateSummary

Every label is counted through one keyed Collection, so the cost is linear in the row count. Two spellings of the same word are two labels here, so neither is reported as a duplicate of the other.

CollectDuplicateGroups #

collect-duplicate-groups

Find every value a column holds more than once, in first-appearance order.

Signature:

Private Function CollectDuplicateGroups(ByVal targetRange As Range, _
                                        ByRef groupLabels() As String, _

One keyed Collection carries the whole count, so the cost is linear in the row count. The key comes from LabelKey and spells the capitalisation out, so "Hello" and "hello" are counted apart and neither is a duplicate of the other. Both the summary message and the sheet colouring read their groups from here, which is what keeps the two of them saying the same thing.

Parameters:

• targetRange: Range. The one column to count.
• groupLabels: ByRef String(). Receives the duplicated values, 1 based.
• groupCounts: ByRef Long(). Receives how many times each one appears, 1 based.

Returns: Long. Number of duplicate groups found.

LabelColumnData #

label-column-data

Access the label column data range.

Signature:

Private Function LabelColumnData() As Range

ColumnBlock #

column-block

Read a one column range in a single crossing.

Signature:

Private Function ColumnBlock(ByVal source As Range) As Variant

BlockText #

block-text

Read one row out of a column block as text.

Signature:

Private Function BlockText(ByVal block As Variant, ByVal rowIndex As Long) As String

SafeText #

safe-text

Turn a sheet value into text, answering empty for an error or a Null.

Signature:

Private Function SafeText(ByVal candidate As Variant) As String

A cell holding #N/A or #REF! answers with an Error variant, and CStr on one raises 13. That killed a whole update and reported a type mismatch that pointed nowhere.

Tag column infrastructure

TagColumnDataRange #

tag-column-data-range

Retrieve the helper tag column data range.

Signature:

Private Function TagColumnDataRange() As Range

ApplyTagColumnFormatting #

apply-tag-column-formatting

Ensure the helper column keeps its stealth formatting regardless of state.

Signature:

Private Sub ApplyTagColumnFormatting()

PaintTagBorders #

paint-tag-borders

Paint every edge of the tag range white so the column reads as empty.

Signature:

Private Sub PaintTagBorders(ByVal target As Range)

PaintHairlineBorder #

paint-hairline-border

Paint one edge of a range as a white hairline.

Signature:

Private Sub PaintHairlineBorder(ByVal target As Range, ByVal edge As Long)

ApplyLangColumnFormatting #

apply-lang-column-formatting

Apply font, sizing, and duplicate highlighting to a language column.

Signature:

Private Sub ApplyLangColumnFormatting(ByVal langRng As Range, ByRef painted As Long)

The group rules go on first and the red catch-all goes on last. FormatConditions.Add appends, so the rule added last holds the lowest priority and shows through only where no group rule claimed the cell.

Parameters:

• langRng: Range. The language column to format.
• painted: ByRef Long. How many groups the pass has coloured so far, carried across every column so no two groups share a colour. See HighlightDuplicateGroups.

HighlightDuplicateGroups #

highlight-duplicate-groups

Give every duplicate group in a column a fill of its own.

Signature:

Private Sub HighlightDuplicateGroups(ByVal langRng As Range, ByRef painted As Long)

A column painted in one colour says which cells are duplicated and nothing about which of them belong together. One rule per group says both, and the eye pairs them without reading a word.

Three things send a group back to the red catch-all: a palette that has run out (MAX_DUPLICATE_GROUPS), a label whose rule formula would pass MAX_RULE_FORMULA_LENGTH, and a formula Excel refuses. So red no longer reads as "duplicate". It reads as "duplicate this could not group", and a sheet with red on it is still telling the truth.

THE COUNTER IS THE CALLER'S, NOT THIS ROUTINE'S It used to start at zero on every column, so the first group of every language drew the same colour. An untranslated row carries the source spelling in both columns, so those same-coloured cells landed on the same rows and the sheet read as though whole rows were highlighted. Duplicates are counted within one column and always were; what crossed the columns was the colour. Carrying the count across the pass gives every group on the sheet a colour of its own, and the palette is then spent sheet-wide rather than per column.

Parameters:

• langRng: Range. The language column to paint.
• painted: ByRef Long. Groups coloured so far in this pass, advanced here.

AddGroupCondition #

add-group-condition

Give one duplicate group its own format condition.

Signature:

Private Function AddGroupCondition(ByVal langRng As Range, _
                                   ByVal anchor As String, _
                                   ByVal label As String, _
                                   ByVal fillColor As Long) As Boolean

EXACT is what carries the case, so the sheet groups labels the way CollectDuplicateGroups counts them and "Hello" never shares a colour with "hello".

Range.Formula reads US syntax whatever the host locale is and Formula1 follows it, so the formula is written in US form with a comma and no localised form is attempted. FormatConditions.Add appends without complaint, so the count is what says whether it was accepted.

Returns: Boolean. True when the group came away with a rule.

GroupConditionFormula #

group-condition-formula

Build the EXACT test for one duplicate group.

Signature:

Private Function GroupConditionFormula(ByVal anchor As String, _
                                       ByVal label As String) As String

A label carrying a double quote has to have it doubled or the formula closes early and Excel refuses the whole rule.

Returns: String. The formula, or empty when it would pass the length cap.

DuplicateColorCount #

duplicate-color-count

How many duplicate groups the palette colours before it repeats.

Signature:

Public Property Get DuplicateColorCount() As Long

HslToRgb #

hsl-to-rgb

Convert one HSL triplet into the Long an Interior.Color takes.

Signature:

Private Function HslToRgb(ByVal hue As Double, _
                          ByVal saturation As Double, _
                          ByVal lightness As Double) As Long

Hue picks the sector of the wheel, chroma is how far the colour sits from grey at that lightness, and the match value lifts all three channels so the result reads back at the lightness asked for. Stepping hue alone then keeps every group equally pale, which is what makes the black Consolas text on top stay readable whatever colour a group drew.

Parameters:

• hue: Double. Degrees, 0 to 360.
• saturation: Double. 0 to 1.
• lightness: Double. 0 to 1.

Returns: Long. RGB colour.

ColorByte #

color-byte

Turn a 0 to 1 channel into the 0 to 255 byte RGB expects.

Signature:

Private Function ColorByte(ByVal channel As Double) As Long

ConfigureTagColumn #

configure-tag-column

Locate and validate the helper column. No cell is written here.

Signature:

Private Sub ConfigureTagColumn()

EnsureTagColumnReady #

ensure-tag-column-ready

Write the helper column title and its stealth formatting.

Signature:

Private Sub EnsureTagColumnReady()

RequireTagHeader #

require-tag-header

Return the tag header cell, raising an error when unavailable.

Signature:

Private Function RequireTagHeader() As Range

IsTagHeaderAttached #

is-tag-header-attached

Test that the held header cell is alive and still sits beside the table.

Signature:

Private Function IsTagHeaderAttached() As Boolean

Deleting the helper column invalidates the held Range. Inserting a column between the helper and the table leaves it alive and no longer adjacent, and resizing over the wrong column follows from that.

AttachTagHeader #

attach-tag-header

Attempt to reconfigure and attach the tag header cell.

Signature:

Private Sub AttachTagHeader()

IsRangeValid #

is-range-valid

Test whether a Range reference is still alive and readable.

Signature:

Private Function IsRangeValid(ByVal candidate As Range) As Boolean

Tag integration helpers

BeginTagIntegration #

begin-tag-integration

Temporarily include the helper tag column inside the managed table.

Signature:

Private Sub BeginTagIntegration()

EndTagIntegration #

end-tag-integration

Restore the translations table to exclude the helper tag column.

Signature:

Private Sub EndTagIntegration()

The tag formatting is applied here alone, once per cycle.

LabelColumnIndex #

label-column-index

Resolve the current column index storing translation labels.

Signature:

Private Function LabelColumnIndex() As Long

Sorting and formatting

SortTranslations #

sort-translations

Sort the translations by their primary language column.

Signature:

Private Sub SortTranslations()

ApplyFormatting #

apply-formatting

Apply light formatting and duplicate highlighting once per update.

Signature:

Private Sub ApplyFormatting()

The row autofit runs once at the end. EntireRow.AutoFit measures every used column on the sheet, so one call per language column was the same measurement done again and again.

Data exchange

Export #

export

Export translations into a destination workbook.

Signature:

Public Sub Export(ByVal exportWorkbook As Workbook, Optional ByVal Hide As Long = xlSheetVisible)

Registry bookkeeping

PlannedUpdateSequence #

planned-update-sequence

Work out the sequence this cycle runs at, without writing it.

Signature:

Private Function PlannedUpdateSequence(ByVal registrySheet As Worksheet) As Long

PersistCounterValue #

persist-counter-value

Persist the counter value inside a hidden worksheet-level name.

Signature:

Private Sub PersistCounterValue(ByVal registrySheet As Worksheet, ByVal counterValue As Long)

CounterNumericValue #

counter-numeric-value

Retrieve the current counter value stored in worksheet names.

Signature:

Private Function CounterNumericValue(ByVal registrySheet As Worksheet) As Long

HiddenNameStore #

hidden-name-store

Create a HiddenNames wrapper for the supplied registry sheet.

Signature:

Private Function HiddenNameStore(ByVal registrySheet As Worksheet) As HiddenNames

A store that cannot be built means the sequence counter is never written. The next run then reads zero, treats the cycle as sequence 1 and reprocesses every row whatever its status, so this failure has to be heard.

MetadataStore #

metadata-store

Resolve the hidden name store hosting metadata.

Signature:

Private Function MetadataStore() As HiddenNames

Validation and utilities

ValidateInitialised #

validate-initialised

Ensure the manager has been initialised before use.

Signature:

Private Sub ValidateInitialised()

ValidateTranslationTable #

validate-translation-table

Validate the supplied translations table.

Signature:

Private Sub ValidateTranslationTable(ByVal translationTable As ListObject)

ValidateRegistrySheet #

validate-registry-sheet

Validate the registry sheet before processing.

Signature:

Private Sub ValidateRegistrySheet(ByVal registrySheet As Worksheet)

ResolveNamedRange #

resolve-named-range

Resolve a named range from the workbook, returning Nothing when missing.

Signature:

Private Function ResolveNamedRange(ByVal hostwb As Workbook, ByVal rangeName As String) As Range

ThrowProjectError #

throw-project-error

Raise a project error with a contextual message.

Signature:

Private Sub ThrowProjectError(ByVal errNumber As Long, ByVal message As String)

Parameters:

• errNumber: Long. The error number to raise. Callers also pass a raw Err.Number.

• TranslationObject.cls
• SetupErrors.cls
• SetupImport.cls
• UpdatedValues.cls
• EventsDesignerAdvanced.bas
• EventsMasterSetupRibbon.bas
• SetupRibbon.bas
• TestDesignerMulti.bas
• SetupImportTestFixture.bas
• TestSetupTranslationsTable.bas
• TestUpdatedValues.bas