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.

Depends on: BetterArray, Checking, IChecking, ProjectError, checking objects

Version: 1.0 (2026-02-09)

Factory

create #

Factory returning an initialised interface

Signature:

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

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: ITranslationObject. Ready to use.

Elements

language #

Current target language

Signature:

Public Property Get Language() As String

Returns: String. One of the language column headers.

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 #

Descriptive name including table and language

Signature:

Private Property Get Name() As String

Returns: String. Identifier for diagnostic output.

value-exists #

Whether a tag exists in the translation table

Signature:

Private Function ValueExists(ByVal Text As String) As Boolean

Parameters:

Text: String. Tag to search.

Returns: Boolean. True when the tag is found.

translated-value #

Translate a text tag or formula string

Signature:

Private 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

translate-range #

Translate all values in a column range

Signature:

Private 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.

translate-form #

Translate all supported controls on a user form

Signature:

Private Sub TranslateForm(ByVal UserFrm As Object)

Parameters:

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

Checkings

has-checkings #

Whether diagnostic entries have been recorded

Signature:

Private Property Get HasCheckings() As Boolean

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

checking-values #

Retrieve the diagnostic log entries

Signature:

Private Property Get CheckingValues() As Object

Returns: Object. The checking instance, or Nothing.

Internal members (not exported)

Elements

self #

Self-reference as ITranslationObject

Signature:

Public Property Get Self() As ITranslationObject

Returns: ITranslationObject. Self-reference.

table #

The backing ListObject translation table

Signature:

Public Property Get Table() As ListObject

Returns: ListObject. The translation table.

table-set #

Assign the backing ListObject and reset caches

Signature:

Public Property Set Table(ByVal lo As ListObject)

Parameters:

lo: ListObject. The translation table.

language-set #

Set the target language and reset caches

Signature:

Public Property Let Language(ByVal transLng As String)

Parameters:

transLng: String. The new language column header.

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.

Checkings

log-info #

Record a diagnostic entry

Signature:

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

Parameters:

label: String. The message to add.
scope: Byte. The checking scope. Defaults to checkingNote.

show-debug #

Print diagnostic information to the Immediate window

Signature:

Private Sub ShowDebug()

Helpers

sanitize-text #

Clean a raw value using worksheet functions

Signature:

Private Function SanitizeText(ByVal rawValue As String) As String

Parameters:

rawValue: String. The raw text.

Returns: String. Cleaned text.

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.

keys-data-range #

Retrieve the data body range of the first (tags) column

Signature:

Private Function KeysDataRange() As Range

Returns: Range. The keys column data, or Nothing.

resolve-translation-cell #

Find the translated cell for a given tag token

Signature:

Private Function ResolveTranslationCell(ByVal token As String) As Range

Parameters:

token: String. The tag to look up.

Returns: Range. The cell in the language column, or Nothing.

translate-formula-text #

Translate only the double-quoted chunks within a formula string

Signature:

Private Function TranslateFormulaText(ByVal formulaText As String) As String

Parameters:

formulaText: String. The formula text.

Returns: String. The formula with quoted chunks translated.

supports-user-form-contract #

Check whether an object exposes a Controls collection

Signature:

Private Function SupportsUserFormContract(ByVal candidate As Object) As Boolean

Parameters:

candidate: Object. The object to test.

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

should-translate-control #

Determine whether a control type supports caption translation

Signature:

Private Function ShouldTranslateControl(ByVal controlType As String) As Boolean

Parameters:

controlType: String. TypeName of the control.

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

translate-multi-page-control #

Translate page captions within a MultiPage control

Signature:

Private Sub TranslateMultiPageControl(ByVal multiPageControl As Object)

Parameters:

multiPageControl: Object. The MultiPage control.

validate-list-object #

Guard against Nothing ListObject arguments

Signature:

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

Parameters:

source: ListObject. The table to validate.
sourceMember: String. Calling member name for error context.

reset-caches #

Invalidate cached column index

Signature:

Private Sub ResetCaches(Optional ByVal resetLanguageIndex As Boolean = True)

Parameters:

resetLanguageIndex: Boolean. When True, clears the language column cache. Defaults to True.

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:

errNumber: ProjectError. The error code.
message: String. Descriptive error message.
sourceMember: String. Optional calling member name.

Used in (86 file(s))