VBA Excel - run string variable as a line of code

Question

In the aim to allow users from different countries to use my application, I would like to initialize a translation of each object in each existing userform (labels,commandbuttons,msgbox,frames, etc...) at the start of the application.

I'll write all the translation in my Languages sheet:

screenshot

I've already made a first userform where the user types his login, password and selects his language.

After this step, the main userform called "Menu" will be launched.

I've already tried to type a piece of code (here below) to find the line of code, in a msgbox that I want to run (example : menu.commandbutton1.caption="Envoyer email")

Private Sub UserForm_Initialize()

    ' Definition of language selected during login
    Set langue = Sheets("Languages").Cells.Find("chosen", 
    lookat:=xlWhole).Offset(-1, 0)

     ' Initialisation of the texts in the selected language
     Dim cel As Range
     Dim action As String
     For Each cel In Sheets("Languages").Range("d3:d999")
         If cel <> "" Then
             action = cel & "=" & """" & cel.Offset(0, -2) & """"
             MsgBox (action)
         End If
     Next cel

 End Sub

I've already read some topics about this subject but those does not correspond exactly to what i would like to do.

If you have a solution, or a work around, it would be very helpful.

Why you are trying pass command variables as strings..? these controls parameters(like menu.commandbutton1.caption) are fixed even if language changes rightt?? — Kiran Maroju
– Kiran Maroju, Commented Nov 23, 2017 at 16:10
How many languages do you plan to support? Are you really asking how to change the labels on the form, how to display messages in the selected language, etc.? If you are trying to 'run string variable as a line of code', then where is a sample of your code that you want to execute? There are several ways to implement multi-language labels, etc. — Wayne G. Dunn
– Wayne G. Dunn, Commented Nov 23, 2017 at 16:10
Not the best code ever written (by me), but perhaps this gets you going: jkp-ads.com/Articles/DistributeMacro09.asp — jkpieterse
– jkpieterse, Commented Nov 23, 2017 at 17:34

Vityata · Accepted Answer · 2017-11-23 16:12:47Z

2

If you simply want different MsgBox, based on a coutry, this is probably the easiest way to achieve it. Imagine your file is like this:

Then something as easy as this would allow you to use different strings, based on the country:

Public Sub TestMe()

    Dim country             As String
    Dim language            As Long

    country = "Bulgaria" 'or write "England" to see the difference
    language = WorksheetFunction.Match(country, Range("A1:B1"), 0)

    MsgBox (Cells(2, language))
    MsgBox "The capital of " & country & " is " & (Cells(3, language))

End Sub

The idea of the whole trick is simply to pass the correct column, which is done through WorksheetFunction.Match.

answered Nov 23, 2017 at 16:12

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mathieu Guindon Over a year ago

I'd even make that a ListObject/table, with "ResourceKey", "Language" and "Value" columns, for a more robust layout - and then concatenate "ResourceKey" with "Language" for easier Match lookups.

Vityata Over a year ago

@Mat'sMug - yup, Index(Match(), Match()) would be probably a better solution.

Mathieu Guindon · Accepted Answer · 2017-11-23 18:00:51Z

Taken from an old CR post I have here, this solution pretty much mimicks .NET .resx resource files, and you can easily see how to extend it to other languages, and if I were to write it today I'd probably use Index+Match lookups instead of that rather inefficient loop - but anyway it works nicely:

Resources standard module

Option Explicit
Public Enum Culture
    EN_US = 1033
    EN_UK = 2057
    EN_CA = 4105
    FR_FR = 1036
    FR_CA = 3084
End Enum

Private resourceSheet As Worksheet

Public Sub Initialize()

    Dim languageCode As String

    Select Case Application.LanguageSettings.LanguageID(msoLanguageIDUI)

        Case Culture.EN_CA, Culture.EN_UK, Culture.EN_US:
            languageCode = "EN"

        Case Culture.FR_CA, Culture.FR_FR:
            languageCode = "FR"

        Case Else:
            languageCode = "EN"

    End Select

    Set resourceSheet = Worksheets("Resources." & languageCode)

End Sub

Public Function GetResourceString(ByVal resourceName As String) As String

    Dim resxTable As ListObject
    If resourceSheet Is Nothing Then Initialize
    Set resxTable = resourceSheet.ListObjects(1)

    Dim i As Long
    For i = 1 To resxTable.ListRows.Count
        Dim lookup As String
        lookup = resxTable.Range(i + 1, 1)
        If lookup = resourceName Then
            GetResourceString = resxTable.Range(i + 1, 2)
            Exit Function
        End If
    Next

End Function

The idea is, similar to .NET .resx files, to have one worksheet per language, named e.g. Resources.EN and Resources.FR.

Each sheet contains a single ListObject / "table", and can (should) be hidden. The columns are basically Key and Value, so your data would look like this on sheet Resources.EN:

Key                                Value
menu.caption                       Menu
menu.commandbutton1.caption        Send email
menu.commandbutton1.controltiptext Click to send the document

And the Resources.FR sheet would have a similar table, with identical keys and language-specific values.

I'd warmly recommend to use more descriptive names though; e.g. instead of menu.commandbutton1.caption, I'd call it SendMailButtonText, and instead of menu.commandbutton1.controltiptext, I'd call it SendMailButtonTooltip. And if your button is actually named CommandButton1, go ahead and name it SendMailButton - and thank yourself later.

Your code can then "localize" your UI like this:

SendMailButton.Caption = GetResourceString("SendMailButtonText")

The Resources.Initialize procedure takes care of knowing which resource sheet to use, based on Application.LanguageSettings.LanguageID(msoLanguageIDUI) - and falls back to EN, so if a user has an unsupported language, you're still showing something.

Collectives™ on Stack Overflow

VBA Excel - run string variable as a line of code

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related