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 #

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 #

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:

Private Property Get Name() As String

Returns: String. Identifier for diagnostic output.

ValueExists #

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.

TranslatedValue #

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

TranslateRange #

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.

TranslateForm #

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

HasCheckings #

has-checkings

Whether diagnostic entries have been recorded

Signature:

Private Property Get HasCheckings() As Boolean

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

CheckingValues #

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

Self-reference as ITranslationObject

Signature:

Public Property Get Self() As ITranslationObject

Returns: ITranslationObject. Self-reference.

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 and reset caches

Signature:

Public Property Set Table(ByVal lo As ListObject)

Parameters:

lo: ListObject. The translation table.

Language #

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.

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.

Checkings

LogInfo #

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.

ShowDebug #

show-debug

Print diagnostic information to the Immediate window

Signature:

Private Sub ShowDebug()

Helpers

SanitizeText #

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.

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.

KeysDataRange #

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.

ResolveTranslationCell #

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.

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:

formulaText: String. The formula text.

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:

candidate: Object. The object to test.

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:

controlType: String. TypeName of the control.

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:

multiPageControl: Object. The MultiPage control.

ValidateListObject #

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.

ResetCaches #

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.

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:

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

Used in (84 file(s))