While implementing the Excel Formula Translator in PHP, I created an Excel workbook including all my translations of the Excel formulas, error codes and arguments of the CELL() and INFO() functions. I thought, it would be a good idea to offer the file for free at the end of this article. Please do not hesitate to send me an e-mail or leave a comment here, if you find an error in one of the translations.
To extract the formulas in the different languages, I had firstly inserted all available formulas in a column in the “Formulas” worksheet of the workbook. For this, I used the English version of Excel, as English is my reference language and I sorted all formulas in alphabetical order. Of course, as I am usually using the German version of Excel, I had to install the English language pack.
Once all formulas have been entered, it is relatively easy to implement a macro which reads all formulas from the column by using the FormulaLocal property and just writes the formula name to another column. Excel automatically translates the formulas to the active language, so if you switch to another language and the read and write the formulas, you will automatically get the localized names.
To getting the whole thing a little bit more comfortable, I added a UserForm which checks, if the identifier (LCID) of the active language can be found in a column head. The language identifier is retrieved each time when the workbook is opened. The macro reduced the needed amount of time for translating the formulas to a few minutes.
However, a time consuming task was to download and install all available Office 2010 language packs. Each language pack uses between 300 and 400 MB. The full installation of all language packs consumes, including Windows 7, approximately 40 GB hard disk capacity. But now I know, all language packs can be installed side by side and I did not have any problems.
After I had translated the formulas to a few languages, I added a worksheet for comparing a selected language to another. By this way, I could filter out all languages that use the English formulas without any differences. It is not necessary that these languages are listed in the translator.
Unfortunately, it was not possible to use a macro to translate the Excel error codes and the arguments of the CELL() and INFO() functions. I had to do this manually by switching to each language and typing the translated values. And, sometimes I also had to get help from an online translator, as especially the arguments for the CELL() function are ordered alphabetically and therefore this order differs from the order of the English names.
After all formulas and other elements had been translated, I created a small macro for writing the results to PHP files. This is not very difficult, as in VBA we can easily create a function for writing data in a file on the hard disk.
That’s all. The workbook included in the following download link reflects my current state of all translations and also includes the macro. The worksheets are protected by an empty password and the VBA code is unprotected.
Update on 11/20/2011: I had improved the translator by adding the formula translations for Excel 2010 Service Pack 1. Some translations of Excel Formulas were changed back to the names of Excel 2007 in Excel 2010 SP1. Therefore, two workbooks are now available with the translations. Current Version is Version 1.75.