Translating Excel Applications

I’ve never translated anything I’ve done in Excel to any other language. As I was designing a userform the other day, I began to think about translating. I was little perplexed either to contact experts from translation agency in London city for translating or to build this userform so that it could be translated easily? I thought about putting all of the my user interface strings in one module. Just a bunch of global string constants that the app would use where necessary. To translate the app, you only have to translate the constants in that module.

Then I started creating the constants. That’s a lot of typing and I’m certainly not going to create all of them. So I wrote this:

Sub CreateCaptions(uf As Object)

Dim ctl As Control
Dim sVars As String
Dim sCaption As String
Dim sPrefix As String

Const sUS As String = “_”
Const sFORM As String = “FORM”
Const sDEC As String = ” As String = “
Const sCAP As String = “CAPTION”
Const sACC As String = “ACCELERATOR”

sPrefix = “Public Const gsTR_” & UCase(uf.Name) & sUS

sVars = sPrefix & sFORM & sUS & sCAP & sDEC & Chr$(34) & _
uf.Caption & Chr$(34) & vbNewLine & vbNewLine

For Each ctl In uf.Controls
On Error Resume Next
sCaption = ctl.Caption
On Error GoTo 0

If Len(sCaption) > 0 Then
sVars = sVars & sPrefix & UCase(ctl.Name) & sUS & sCAP & _
sDEC & Chr$(34) & ctl.Caption & Chr$(34) & vbNewLine
sVars = sVars & sPrefix & UCase(ctl.Name) & sUS & sACC & _
sDEC & Chr$(34) & ctl.Accelerator & Chr$(34) & vbNewLine & vbNewLine
End If
Next ctl

Debug.Print sVars

End Sub

And it outputs this:

Public Const gsTR_UMANAGE_FORM_CAPTION As String = “Manage Favorites”

Public Const gsTR_UMANAGE_LBLFAVORITES_CAPTION As String = “Favorites”
Public Const gsTR_UMANAGE_LBLFAVORITES_ACCELERATOR As String = “F”

Public Const gsTR_UMANAGE_CMDMOVEUP_CAPTION As String = “Move Up”
Public Const gsTR_UMANAGE_CMDMOVEUP_ACCELERATOR As String = “U”

Public Const gsTR_UMANAGE_CMDMOVEDOWN_CAPTION As String = “Move Down”
Public Const gsTR_UMANAGE_CMDMOVEDOWN_ACCELERATOR As String = “D”

Public Const gsTR_UMANAGE_CMDADDFAVORITE_CAPTION As String = “Add New”
Public Const gsTR_UMANAGE_CMDADDFAVORITE_ACCELERATOR As String = “A”

Public Const gsTR_UMANAGE_CMDREMOVEFAVORITE_CAPTION As String = “Remove”
Public Const gsTR_UMANAGE_CMDREMOVEFAVORITE_ACCELERATOR As String = “R”

Public Const gsTR_UMANAGE_CMDCLOSE_CAPTION As String = “Close”
Public Const gsTR_UMANAGE_CMDCLOSE_ACCELERATOR As String = “C”

The form is gsTR(anslaste)_FormName_Form||ControlName_Property. Pretty long constant names, but I won’t get confused about what they’re for. :) The code goes through all of the controls on the userform. If the control has a caption, it creates a constant for the caption and the accelerator key. It’s a handy list of accelerator keys too.

Presumably if you translate all of these constants in this module, the userform will be in whatever langauge you want. I’d also need to include message boxes and menus (harder if it’s Ribbon based, I think). Then there’s the problem of German. I don’t speak German, but I learned a few words a few years ago. Everything is longer. If you want to say ‘Move Up’ in German, it comes out like ItemPromotenHigher. So none of the German words will fit in the controls widths I’ve set.

Do you do anything different to make your apps more easily translated?

9 thoughts on “Translating Excel Applications

  1. I store all the translations in an excel file (easiest to edit for translators or finicky clients). I use 1 column for the (unique) ID, one column per language.

    On application load or language change I read the file and store the strings in a scripting dictionary. On form load I fill the needed captions, tooltips and messages from the dictionary. I prefer to use a function for all calls to the dictionary so I can trap missing ID’s. ..

    You’ll find that most languages require more space than English. Hungarian is even worse than German (or maybe it’s my translator who needs more words?) Just keep plenty of space. I never use autosize and textwrap for my controls.

  2. I don’t do this, but I think that you would have to put them in a table, so you can load them dynamically at run time and switch the language during install/in options. I think would then bug them in to constants.

    I have seen them other programes that do this, I think it makes sense.

  3. Use tags, setting the tag string to be English-language caption. Or, better still, a name-caption pair delimited by a semicolon.

    This is less work than it sounds: after you’ve written and tested the application, a short VBA subroutine can enumerate all the controls on each form and spreadsheet, and write a tag of ParentName;ControlName;EnglishCaption into each control’s tag property AND to a hidden sheet, with the parent object name in column A, the control name on’B’, and the English caption in ‘C’ under the column heading ‘English’.

    Now fill in the next column under the heading ‘French’, the next one in ‘German’, and so on. Advanced students may want to specify a code page, although this can be picked up from the registry too.

    On open, a subroutine queries the locale in the registry, then fills in all the captions. It is possible to fill in entries manually for chart captions and labels, a more difficult item to enumerate in code.

    It can be done without tags at all, but self-documenting controls are easier to debug!

    Apologies for not supplying the source code but I’m away from my PC.

  4. I’m so lazy: more than half my colleagues are German but their English is so good (it’s the “global language” of the company) I don’t bother with translations. But never having done something seldom stops me from having an opinion!

    I think I’d be inclined to create some sort of enhanced Dictionary (there’s a clue in the class name) that loads itself from some text resource, which could be a hidden worksheet, a module (ugh) or something external – text file, MDB, XML, whatever. Call it a Translator, perhaps.

    Beyond that, I think I’d consider using some sort of magic to indicate the controls that needed to be considered: maybe use the Tag property, if it’s free? Maybe put a “C” for caption needed, an “A” for accelerator and “AC” for both.

    Now my workflow can be something like this:
    1. Determine language and load my Translator
    2. Pass the form to the Translator, which works through the controls, setting properties as needed.

    I kinda hope that would work. I might even build a Collection or Translators (is there a collective noun? A Tower? As in Babel?) so that I could switch on demand, perhaps within a loaded form. It shouldn’t take more than a few lines of code.

  5. Looks like I do the same as a few people here.

    Separate sheet with the first column being the unique identifier, and then a column per language. Display text is generated by finding the unique id (say lanMailSignoff or lantbCustomerName) and then using the language offset to find the text.

  6. Without extensive experiences with this; any lookup table system will do. I feel the problem is length of text when changing language. How wide is a label; how high is a checkbox, to contain all versions, and how silly does it then look using the most effective language?

  7. The big issue with multi-language support (MLS) is not the translation thing but the aesthetic thing. That has been what has put me off “automated MLS.”

    I would suggest doing this as a two step process with a specific, and relatively simple, data structure: a table with columns: Control ID, Language ID, Text, Left, Width, Top, Height.

    Adding a new language would be something done at design time. The first step is to add the text for the language.

    I haven’t done much programming in the VBE environment. So, I don’t know how easy the following is. At worst, one can always do what’s needed “by hand.” {grin}

    Now, (use code to?) update the userform (or whatever UI you are using) with the new text. With the new text in place, adjust the userform controls so that they are readable and aesthetically appealing.

    Now, update the table with the controls’ dimensions.

    The runtime MLS support code works with both the language text and control position/size.

    The task of handling error messages should be a lot simpler…I think…

  8. We’ve been working on totally multi-lingual interfaces for a while now. Language string for forms are in a sheet contained within an addin, strings for messages/progress bar captions etc. are in a sheet, with look ups based on language ids. Unfortunately it gets worse: form captions and message box captions cannot handle some of the international fonts, so we have a form handler that manages international captions, and a custom crafted message class. After that, it get’s worse: any form contents also need translation. e.g. translating the contents of lists, whether it is simple – e.g. ones, thousands, millions, or complex – what’s the name of this abstract thing in the user’s language. A lot of that we handle through database translation tables. Then it get’s worse. Because we’re data driven, any form contents require translation back into english when passed to the db (for efficiency against indexes, and readability), and back into the user language for output. Output is complicated: what number formats exist in a given language, how are dates displayed, etc… On top of all this, we have to translate command bars, the dreaded ribbon, help files. GOOD LUCK.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.