Last Updated: July 2016
Whilst developing my VBA Add-
Many internationalisation solutions require developers to install disk based libraries
to deal with internationalisation issues. My solution is to incorporate the translated
strings within the VBA code so that the language setting of the local machine is
used without having to install any extras. My macros are not massive and so the translations
are also small enough to be incorporated in the Add-
To include string translations simply requires you to import two modules; Constants.bas and Translation.cls. Constants.bas defines the message IDs to use in your code. Translation.cls contains the language translations in VBA dictionaries.
To create these modules I have developed an Excel macro enabled spreadsheet in which you collate all the languages as shown below.
The first column (ID) will be used in your code to retrieve the translated string. It MUST conform to VBA variable naming conventions and not be a reserved word.
Having setup your spreadsheet you simply invoke the export macro to generate the two modules you need to import into your project.
The spreadsheet requires a template Translation.dat which is used to create the Translation.cls module. The Export macro simply replaces placeholders with generated code.
To use the modules you import them into your project then you need to do something
Dim tran as Translation
Set tran=new Translation()
Your message will then appear in whatever language you have catered for. The default , in my case, is English so, if the local machine language hasn’t been included English, will be what the user sees. You can modify the Case Else statement in the function langString() at the end of Translation.dat if you want to change that behaviour.
The Spreadsheet which does the work includes a Help sheet -