vbagettext is a collection of Visual Basic for Applications modules and classes that implement basic I18N and L10N capabilities for VBA projects. You can import these components into your own VBA project.
Since vbagettext uses the GNU gettext PO file format, translators can employ common localization tools, such as POedit.
The VBA components are written for and tested with an Excel VBA project (XL Toolbox).
The vbagettext project consists of three components:
See below for information about a demo workbook.
The XL Dev Tools add-in (developer.xla by the same author) is required to extract the translatable strings from your VBA project and generate a GNO PO file. You may obtain it here:
You may download the source code at Sourceforge.net:
The Git command to clone the repository is:
git clone git://git.code.sf.net/p/vbagettext/code vbagettext-codeOnce you have downloaded the modules, import them into your project from the Visual Basic IDE using the File-Import (CTRL+M) command. A more convenient alternative is to use the command from the XL Dev Tools menu (since you have installed this add-in to extract your strings, you can just as well use it for other tasks).
A compiled DLL can be downloaded at Sourceforge:
The source code for the DLL is included in the Git repository (see above). It is not trivial to compile the DLL from source code; I put together some instructions.
To make the DLL available at run-time, either place it into your Windows system32 folder (which requires administrative rights), or place it into the same folder that your VBA project file resides in. For addins on Windows XP, this is typically:
%appdata%\Microsoft\AddinsIf Windows complains that it cannot find the DLL, you may need to register the path to the DLL with code like this:
#If VBA7 Then
Private Declare PtrSafe Function SetDllDirectory Lib "kernel32.dll" Alias "SetDllDirectoryA" ( _
ByVal lpPathName As String) As Boolean
#Else
Private Declare Function SetDllDirectory Lib "kernel32.dll" Alias "SetDllDirectoryA" ( _
ByVal lpPathName As String) As Boolean
#End If
Function AddDLLPath(ByRef path As String) As Boolean
Dim s As String
s = path & Chr(0)
AddDLLPath = SetDllDirectory(s)
End Function
Function RemoveDLLPath() As Boolean
RemoveDLLPath = AddDLLPath("")
End FunctionThen, make sure to call the AddDLLPath function with the appropriate parameter (i.e., the location of your DLL) before you use any of the vbagettext functions.
Preparing your VBA project for translation comprises the following steps:
In order to build a GNU Gettext PO file, you need to extract all of the translatable strings from your code. These strings can be:
Strings in functions and procedures:
MsgBox "Hello World."
Dim MyString = String
myString = "Hello World."Private Const MyConstant = "Hello World."You can use the XL Developer Tools addin to automatically extract all of these different kinds of strings and collect them in a GNU Gettext PO file.
To enable the XL Developer Tools to find the strings that you want to translate, you need to mark them in special ways.
To mark translatable strings in functions and procedures, surround them with a function call to the vbagettext translate function t().
This function is defined in the Module_GetText module:
Function t(ByVal text As String, ParamArray params() As Variant) As StringAll literal text that is used as the text parameter of this function will be identified and extracted from your project.
For example, if you have the code block as above:
MsgBox "Hello World."
Dim MyString = String
myString = "Hello World."The marked version would be:
MsgBox t("Hello World.")
Dim MyString = String
myString = t("Hello World.")As you can tell from the function declaration, the function accepts optional parameters if you want to include variables in the text:
MsgBox t("There are {} months in a year.", 12)Of course, you can also use more than one parameter:
Dim Days_In_June as Long
Dim Months_In_Year as Long
Days_In_June = 30
Months_In_Year = 12
MsgBox t("There are {} days in June and {} months in a year.", _
Days_In_June, Months_In_Year)For your convenience, vbgagettext supports the C-style \n code to insert line breaks into strings. It is not recommended to use the usual vbNewLine constant, as this will break string extraction. Furthermore, Gettext translation software such as POedit can deal with \n, and you can also use \n to mark newlines in your translated strings.
Example:
MsgBox t("This is a string that\nspans two lines.")Marking string constants at the module level is done a bit differently:
Private Const MyConstant = "Hello World." ' < translateIt is the < translate part that turns the constant in to a translatable string. Important: In order to have the constant translated in the final code, you must enclose it in a t() function call:
MsgBox t(MyConstant)Strings in form controls are automatically extracted as they are very easy to identify programmatically.
There may be some controls’ captions that you do not want to have extracted for translation. To prevent extraction of a control’s caption, add the tag noI18N to it’s properties:
Form captions are automatically extracted.
To have your RibbonUI translated, you need to work with callback functions like this:
Public Sub MyProject_UI_getLabel(control as IRibbonControl, ByRef returnedVal)
Select Case control.id
Case "MyOwnID": returnedVal = t("My command")
End Case
End SubUse the XL Dev Tools addin to extract the strings that you marked and collect them in a GNU Gettext PO file.
Important: In order for this to work, you need to relax Excel’s security settings a little bit – see below.
Once you have installed the XL Dev Tools addin, start up Excel and open your project. Then, open the Visual Basic IDE by pressing Alt+F11. Make sure that your project is not running by pressing the ‘stop’ button or by choosing the “Reset” command from the “Run” menu.
Execute the “Internationalization…” command from the “XL Dev Tools” menu in the VBE.
Then, click on the radiobutton for “addins” or “workbooks”, as appropriate for your project.
When you click on “Create PO template”, you will be prompted for a file name for the PO template file. It has a .POT extension which causes Windows® to confuse it with a PowerPoint® template, so double-clicking this file will produce unexpected results.
You can distribute the newly generated PO template file to your translators to start translation.
The default security settings of Microsoft® Excel® do not permit the XL Dev Tools addin read access to your own project’s code. Therefore, to extract strings from your project, you need to relax Excel’s security settings a little bit. Read more here:
This is only needed for the extraction of strings.
To use the vbagettext library with a project that is distributed to end users, you do not need to relax the security settings.
Please note: You use the vbagettext library entirely at your own risk. No warranties, not even for merchantability or fitness for a particular purpose. The author does not assume any liability for any kind of damage that might result from installing or using the vbagettext library.
To translate the strings contained in the PO template file, use a suitable tool such as POedit, which is also free software.
If you have never translated your current VBA project before, start by generating a new PO file from the PO template file:
You will be prompted for information about the project and the team of translators – while informative, it is not really necessary to enter the information here, and you can dismiss the dialog.
Immediately after creating the new catalog, you will be prompted for a file name to save the .PO file. You must follow naming conventions, otherwise your file will not be found by the vbagettext functions.
Name your .PO file by appending an underscore followed by an international language code. Examples:
vbagettextdemo.pot
vbagettextdemo_de.po
vbagettextdemo_it.po
vbagettextdemo_en-US.po
vbagettextdemo_fr-FR.poThen, start translating your strings.
Whenever you save your .PO file, a machine-readable .MO file will automatically be generated and saved in the same folder. It is this .MO file that you need to distribute with your project.
If you already have a translated .PO file and you obtain an updated .POT template file due to changes in your VBA project, open the .PO file in POedit and choose “Update from POT file…” from the “Catalog” menu.
To use the translation in your VBA project, make sure you have imported all required vbagettext modules and classes (see “Obtaining vbagettext”).
In the Module_Gettext module, you must adjust two module-wide constants:
' Adjust the next two constants for use in your project
Private Const LOCALE_DIR = "resource" ' where to locate the translation files
Private Const LOCALE_BASE_NAME = "vbagettextdemo" ' base name of the PO filesThe LOCALE_DIR constant indicates a folder relative to your workbook or addin’s folder where the .MO files are stored. In the demo workbook, this is also the folder that contains the xltoolbox.dll and its 64-bit variant, xltoolbox-64.dll.
The LOCALE_BASE_NAME is the base name of the .MO files. It is advisable to make this the same name as the workbook or addin’s base file name, and it may be that a future version of vbagettext will always automatically use the current workbook or addin’s base file name.
To set a language in your project, use the SetLanguage() function. Example:
SetLanguage "de"To retrieve the current language, use the GetLanguage() function. Example:
MsgBox t("Current language: {}", GetLanguage)To obtain a string array with the available languages (based on the .MO files that are actually found in the LOCALE_DIR folder):
Dim astrLanguages() as String
dim lngCurrent as Long
astrLanguages = BuildLanguageList(lngCurrent)The BuildLanguageList function sets the lngCurrent parameter to the index of the current language in the string array, which is useful if you want to fill a ListBox control and highlight the current language in it.
Have a look at a little demo workbook to see vbagettext in action. The demo workbook and its supporting files can be downloaded from Sourceforge:
When you click on the “Start” button in this workbook, the following UserForm is displayed in English:
Clicking the “MsgBox” button brings up an English message:
When you choose “Deutsch (de)” (German) from the ListBox, some of the text (except where noted) will be translated at run-time to German:
Clicking the “MsgBox” button brings up the German translation of the message:
Contact the author, Daniel Kraus, at xltoolbox (at) gmx (dot) net.
This code is published unter the GPL v2 license.
vbagettext
This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.