search variable name in a range adn assign its value

Question

I'm beginning to modify an Excel VBA to get it localized in different languages. I'm going on the solution of assigning a variable to the caption.value and assign the value of the variable according to the desired language.

Language	FR	EN
Ln_Date	Date	Date
Ln_Created by	Créé par	Created by

And in my example, with th language set priorly, I would like to give to each variable (name in the firdst column) its value.

The question is, do I have to set the variable first (public ln_Date as string) and after search its value in the first column and assign its value (variable = range(variable_name + offset(language),1).Text) Or is the a possibility the create on the fly while searching the range the variable with their type (public string) and their value ?

I'm trying for each variable defined priorly to search in the range and assign its value but as the program advance in its developpement, the function is becoming more and more big/complex and it seems to me that it is not the best way to do things as I have to add loops for each new language variable.. It seems like a maze in the end

Answer 1

There is no way to access the name of a variable during runtime, so you code would look like this (I guess you want to set named ranges for all the variables?)

Dim Ln_Date As String
Ln_Date = range("Ln_Date" + offset(language), 1).Text
(... and so on for every variable)

You might think about using a Collection or Dictionary instead.

My suggestion is not to read the translated words into variables but to create a small function that provides a the wanted text whenever it is needed. The easiest variation could look like this (I assume that the translation table is on a separate sheet called Translations - change that to your needs).

Function getLanguageColumn(language As String) As Long
    With ThisWorkbook.Sheets("Translations")
        Dim col As Long
        On Error Resume Next
        col = Application.Match(language, ThisWorkbook.Sheets("Translations").Rows(1), 0)
        On Error GoTo 0
        If col = 0 Then col = 2         ' Default: First column
    End With
    getLanguageColumn = col
End Function

Function getTranslation(code As String, language As String)
    Dim col As Long
    col = getLanguageColumn(language)

    Dim translation As String
    With ThisWorkbook.Sheets("Translations")
        On Error Resume Next
        translation = WorksheetFunction.VLookup(code, .UsedRange, col, False)
        On Error GoTo 0
        If translation = "" Then translation = "Can't translate code " & code
    End With
    getTranslation = translation 
End Function

There is room for improvement (better error handling, cashing the translation table into an array or dictionary), but as long as the table doesn't get too large, it will do.

Then, in your code you call the function whenever you need a translated term. For example, you create a Sub in a user form called SetLabels and call that routine when the form is displayed (UserForm_Activate()) or when the user changes the language if you give them the option to chose.

Dim Language As String

Private Sub UserForm_Activate()
    Language = "FR"
    SetLabels
End If

Sub SetLabels
    Me.LabelDate = getTranslation("LN_Date", Language)
    Me.LabelCreatedBy = getTranslation("Ln_Created by", Language)
    (...)
End Sub