Localization of VBA forms

[Mikael Sorensen]

What's the best way to Localize a VBA form?
I would prefer not to use:
- third party tools
- external dll's

(I need to translate the form into danish, english,
german, dutch ...)

Regards,
Mikael Sorensen

 

[Lars-Eric Gisslén]

Mikael,

We have solved it in the following way:
1. Define all strings in arrays, one for each language.
2. Check with langage setting the current user have in the registry in the:
HKEY_CURRENT_USER\Control Panel\International\sLanguage
3. Set the strings in the Userform_Initialize method.

If you want to able to be change the language at runtime you can put the
code in a separate method and call it with the appropriate value for each
language

You can use the code below as an idea/starting point.

Private Sub UserForm_Initialize()
Dim sLang As String
Dim aSV(1) As String
Dim aEN(1) As String
Dim aLang As Variant
Dim i As Long

sLang = System.PrivateProfileString("", _
"HKEY_CURRENT_USER\Control Panel\International", _
"sLanguage")

' Swedish strings
aSV(0) = "Namn:"
aSV(1) = "Adress:"

' English strings
aEN(0) = "Name:"
aEN(1) = "Address:"

If sLang = "SVE" Then
' Swedish Captions
aLang = aSV
Else
' Unknow language, use English
aLang = aEN
End If

For i = 0 To UBound(aLang)
' Set caption for label controls
Me.Controls("CtrlLabel" & Format(i)).Caption = aLang(i)
Next

End Sub

 

[Mikael Sorensen]

Lars-Eric,

How do you handle strings that would otherwise be hardcoded (not related
controls)?
- error mssages
- msgbox text
- ...

Regards,
Mikael

 

[Lars-Eric Gisslén]

Mikael,

We have our own classes for error messages and other messages. The classes
has all string definitions in Arrays and using public constants for the
array elements.

Something like this:

Set oMsg = New MessageObject
oMsg.Display sLang, ID_MSGTXT_NAMEMISSING, ID_MSGCAPT_ALERT
-----------------
Public Sub Display(sLng As String, nMsg As Long, nCaption as Long) ' In the
Class

If sLng = "SVE" Then
sTxt = aSETxt(nMsg)
sCaption = aSECap(nCaption)
Else
sTxt = aENTxt(nMsg)
sCaption = aENCap(nCaption)
End If

MsgBox sTxt, vbInformation, sCaption
End Sub

This is quite similar to using String Resources in DLL files in more low
level dev tools for internationalized applications. There you reference all
strings by numeric constants. Load the appropriate language Resource and
referense the strings by the same numeric constants as used in the Resource.

When we use Win Api calls that fails we usually call Win API's to get the
correct error message in the same language as the OS.