How to Store String Resources

When creating multilingual applications, you cannot hard code any text strings that will be displayed to the user; you must look them up in a string resource. The easiest form of string resource is a simple worksheet table. Give all your text items a unique identifier and store them in a worksheet, one row per identifier and one column for each supported language. You can then look up the ID and return the string in the appropriate language using a simple VLOOKUP function.

You will need to do the same for all your menu items, worksheet contents, and UserForm controls. The following code is a simple example, which assumes you have a worksheet called shLanguage that contains a lookup table that has been given a name of rgTranslation. It also assumes you have a public variable to identify which column to read the text from. The variable typically would be set in an Options type screen.

Note that the code shown here is not particularly fast and is shown as an example. A faster (and more complex) routine would read the entire column of IDs and selected language texts into two static VBA arrays, then work from those, only reading in a new array when the language selection was changed:

Public iLanguageCol As Integer

Sub Test()

iLanguageCol = 2 MsgBox GetText(1001) End Sub

' ITextID - The string ID to look up Function GetText(lTextID As Long) As String

Dim vaTest As Variant Static rgLangTable As Range

'Set an object to point to the string resource table (once) If rgLangTable Is Nothing Then

Set rgLangTable = ThisWorkbook.Worksheets("shLanguage") .Range("rgTranslation")

End If

'If the language choice is not set, assume the first language in our table If iLanguageCol < 2 Then iLanguageCol = 2

'Try to locate and read off the required text vaTest = Application.VLookup(lTextID, rgLangTable, iLanguageCol)

'If we got some text, return it If Not IsError(vaTest) Then GetText = vaTest End Function

Many of your messages will be constructed at run time. For example, you may have code to check that a number is within certain boundaries:

MsgBox "The number must be greater than " & CStr(iMin) & _ " and less than " & CStr(iMax) & "."

End If

This would mean that you have to store two text strings with different IDs in your resource sheet, which is both inefficient and much harder to translate. In the example given, you probably would not have a separate translation string for the full stop. Hence, the maximum value would always come at the end of the sentence, which may not be appropriate for many languages. A better approach is to store the combined string with placeholders for the two numbers, and substitute the numbers at run time (using the custom ReplaceHolders function, shown at the end of the chapter):

If iValue < iMin Or iValue > iMax Then MsgBox ReplaceHolders( _

"The number must be greater than %0 and less than %1.", _

End If

The translator (who may not understand your program) can construct a correct sentence, inserting the values at the appropriate points.

0 0

Post a comment