TranslationObject

Tag-based translator backed by a ListObject table. Looks up tags in the first column and returns the value from the active language column. Supports plain text, formula-aware translation (quoted chunks only), range bulk-translation, and user-form caption translation. The table is read once into memory and every value is matched against that copy through a keyed index. The copy is kept until the table changes shape or Refresh is called, so a run that translates a thousand strings reads the worksheet twice and looks each string up without walking the copy.

Depends on: BetterArray, Checking, checking objects

Version: 1.0 (2026-02-09)

Factory

Create #

create

Factory returning an initialised interface

Signature:

Public Function Create(ByVal translationLo As ListObject, ByVal translationLang As String) As TranslationObject

A translation object wraps a ListObject whose first column contains tags and remaining columns hold language translations. Tags not found during translation are returned as-is.

Parameters:

  • translationLo: ListObject. The translation table.
  • translationLang: String. The target language column header.

Returns: TranslationObject. Ready to use.

Elements

Language #

language

Current target language

Signature:

Public Property Get Language() As String

Returns: String. One of the language column headers.

LanguagesList #

languages-list

List of available language headers

Signature:

Public Property Get LanguagesList() As BetterArray

Returns: BetterArray. Each language header, excluding the helper/tag column.

Name #

name

Descriptive name including table and language

Signature:

Public Property Get Name() As String

Returns: String. Identifier for diagnostic output.

ValueExists #

value-exists

Whether a tag exists in the translation table

Signature:

Public Function ValueExists(ByVal Text As String) As Boolean

Parameters:

  • Text: String. Tag to search.

Returns: Boolean. True when the tag is found.

TranslatedValue #

translated-value

Translate a text tag or formula string

Signature:

Public Function TranslatedValue(ByVal textToTranslate As String, _
                                Optional ByVal containsFormula As Boolean = False) As String

When containsFormula is True, only double-quoted chunks inside the text are translated; unquoted characters pass through unchanged.

Parameters:

  • textToTranslate: String. Text to translate.
  • containsFormula: Boolean. Translate as formula. Defaults to False.

Returns: String. The translated value, or the original text when not found.

Outside

TranslateRange #

translate-range

Translate all values in a column range

Signature:

Public Sub TranslateRange(ByVal colRng As Range, Optional ByVal containsFormula As Boolean = False)

Parameters:

  • colRng: Range. Column range whose values should be translated.
  • containsFormula: Boolean. When True, translates as formulas. Defaults to False.

TranslateForm #

translate-form

Translate all supported controls on a user form

Signature:

Public Sub TranslateForm(ByVal UserFrm As Object)

Parameters:

  • UserFrm: Object. Any user form exposing a Controls collection.
Checkings

HasCheckings #

has-checkings

Whether diagnostic entries have been recorded

Signature:

Public Property Get HasCheckings() As Boolean

Returns: Boolean. True when at least one entry exists.

CheckingValues #

checking-values

Retrieve the diagnostic log entries

Signature:

Public Property Get CheckingValues() As Object

Returns: Object. The checking instance, or Nothing.

Helpers

Refresh #

refresh

Drop the copy held in memory

Signature:

Public Sub Refresh()

The next translation reads the table again. Call this after writing into the translation table through anything other than this object.

Internal members (not exported)

Elements

Table #

table

The backing ListObject translation table

Signature:

Public Property Get Table() As ListObject

Returns: ListObject. The translation table.

Table #

table-set

Assign the backing ListObject

Signature:

Public Property Set Table(ByVal lo As ListObject)

Parameters:

Language #

language-set

Set the target language

Signature:

Public Property Let Language(ByVal transLng As String)

Parameters:

Checkings

LogInfo #

log-info

Record a diagnostic entry

Signature:

Private Sub LogInfo(ByVal label As String, _
                    Optional ByVal scope As Byte = checkingNote)

Parameters:

Helpers

SanitizeText #

sanitize-text

Clean a raw value the way the worksheet functions used to

Signature:

Private Function SanitizeText(ByVal rawValue As String) As String

Does the same three jobs as SUBSTITUTE + CLEAN + TRIM did, in plain VBA so a call no longer goes out to Excel: the non-breaking space becomes an ordinary space, characters below code 32 are dropped, then leading, trailing and repeated spaces are squeezed. Worksheet TRIM squeezes inner runs of spaces down to one, which VBA Trim$ does not do, so that part is written out here.

This routine used to substitute Chr$(60) instead, which is '<'. Every comparison operator was deleted from the text it was handed, so a translated CHOICE_FORMULA came back with "age_months < 6" reading "age_months 6", and a plain cell holding '<' was altered even when the tag had no translation at all. The character meant here is the non-breaking space, the same one Formulas.CleanString replaces.

Parameters:

Returns: String. Cleaned text.

HeaderRowRange #

header-row-range

Retrieve the header row range of the translation table

Signature:

Private Function HeaderRowRange() As Range

Returns: Range. The header row, or Nothing.

RangeBlock #

range-block

Read a range into a two dimensional array in one go

Signature:

Private Function RangeBlock(ByVal target As Range) As Variant

Range.Value hands back a plain value for a single cell, so that case is wrapped to keep one shape for every caller.

Parameters:

Returns: Variant. A two dimensional array, or Empty when target is Nothing.

TextOf #

text-of

Turn a cell value into text without ever raising

Signature:

Private Function TextOf(ByVal cellValue As Variant) As String

Parameters:

Returns: String. The value as text, empty for errors and blanks.

LanguageIndex #

language-index

Resolve the one-based column index of the active language

Signature:

Private Function LanguageIndex() As Long

Returns: Long. Column index, or -1 when not found.

TableSignature #

table-signature

What the copy in memory was read from

Signature:

Private Function TableSignature() As String

The table name, the address of its body and the target language. A row added or removed, a column added or removed, a table replaced or a language switched all change it, and each one is a reason to read the table again.

Returns: String. The signature of the table as it stands now.

RefreshLookup #

refresh-lookup

Read the tags and the active language column into memory

Signature:

Private Sub RefreshLookup()

Called at the start of every public translation call. The copy is kept between calls and reread when the table it came from changes shape or the language moves. Building one analysis sheet asks for four translations per chart, and every one of them used to read the whole table.

THE LIMIT OF THIS

A value typed over an existing cell leaves the shape of the table alone, so the copy does not notice it. Refresh drops the copy for a caller that has just done that.

RegisterLookupKey #

register-lookup-key

Record the row a tag sits on, keeping the first row when a tag repeats

Signature:

Private Sub RegisterLookupKey(ByVal token As String, ByVal rowIndex As Long)

Collection.Add raises 457 on a key it already holds, so the first row a tag appears on is the row that stays. That is the row the scan this index replaced answered with, because the scan stopped at its first match.

Parameters:

LookupKey #

lookup-key

Build the index key for a tag, one key per spelling

Signature:

Private Function LookupKey(ByVal token As String) As String

A Collection matches its own keys without regard to case, and this lookup is case sensitive, so the key carries the capitalisation as a separate part and "Hello" never answers for "hello". The leading character keeps a numeric tag legal as a key. Same construction as SetupTranslationsTable.LabelKey.

Parameters:

Returns: String. The key.

CaseSignature #

case-signature

Spell a tag's capitalisation out in digits

Signature:

Private Function CaseSignature(ByVal token 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.

Parameters:

Returns: String. One digit per character.

LookupRow #

lookup-row

Find the row of a tag in the copy held in memory

Signature:

Private Function LookupRow(ByVal token As String) As Long

Answered by the keyed index built with the copy. It used to walk the copy from the top on every call, and translating the setup asks this once per cell of every translated column, so the walk cost rows times cells and was the whole of why translating a setup took as long as it did.

Parameters:

Returns: Long. The one-based row, or 0 when the tag is not there.

TranslateText #

translate-text

Translate one value against the copy already in memory

Signature:

Private Function TranslateText(ByVal textToTranslate As String, _
                               ByVal containsFormula As Boolean) As String

Parameters:

Returns: String. The translated value.

TranslateTag #

translate-tag

Return the translation of one tag, or the cleaned tag itself

Signature:

Private Function TranslateTag(ByVal token As String) As String

Parameters:

Returns: String. The translation, or the cleaned token when there is none.

TranslateFormulaText #

translate-formula-text

Translate only the double-quoted chunks within a formula string

Signature:

Private Function TranslateFormulaText(ByVal formulaText As String) As String

Parameters:

Returns: String. The formula with quoted chunks translated.

SupportsUserFormContract #

supports-user-form-contract

Check whether an object exposes a Controls collection

Signature:

Private Function SupportsUserFormContract(ByVal candidate As Object) As Boolean

Parameters:

Returns: Boolean. True when a Controls property is accessible.

ShouldTranslateControl #

should-translate-control

Determine whether a control type supports caption translation

Signature:

Private Function ShouldTranslateControl(ByVal controlType As String) As Boolean

Parameters:

Returns: Boolean. True for CommandButton, Label, OptionButton, Page, Frame, CheckBox.

TranslateMultiPageControl #

translate-multi-page-control

Translate page captions within a MultiPage control

Signature:

Private Sub TranslateMultiPageControl(ByVal multiPageControl As Object)

Parameters:

ValidateListObject #

validate-list-object

Guard against Nothing ListObject arguments

Signature:

Private Sub ValidateListObject(ByVal source As ListObject, ByVal sourceMember As String)

Parameters:

RaiseError #

raise-error

Raise a typed project error with optional member context

Signature:

Private Sub RaiseError(ByVal errNumber As ProjectError, ByVal message As String, Optional ByVal sourceMember As String = vbNullString)

Parameters:

Seal #

seal

Seal the instance so setup setters can no longer be written

Signature:

Public Sub Seal()

GuardNotSealed #

guard-not-sealed

Guard a setup setter against writes after sealing

Signature:

Private Sub GuardNotSealed(ByVal propName As String)

Parameters:

Used in (66 file(s))