Learn German
language according to the method of the Gestapo.
Employees working in foreign companies are often forced to prepare two identical reports for their domestic and foreign superiors – in and English. The numbers in these reports are, of course, the same, but only the signatures, headings and other text change.
This problem can be solved more elegantly. We create a new sheet in such a report and call it “Dictionary“. We write down on this sheet in any order all the words that need to be automatically translated. For each language, add a new column, like so:
Then go to the Visual Basic editor (menu Service – Macro – Visual Basic Editor), insert a new module (menu Insert – Module) and copy the text of these two macros there:
Sub Translate() Dim cell1 as Range, cell2 As Range Dim i as Long, Langs As Long Langs = 3 'количество языков перевода, включая русский For Each cell1 In ActiveSheet.Cells.SpecialCells(xlCellTypeConstants) For Each cell2 In Worksheets("Словарь").Cells.SpecialCells(xlCellTypeConstants) If cell1.Value = cell2.Value Then i = cell2.Column If i = Langs Then i = 1 Else i = i + 1 cell1.Value = Worksheets("Словарь").Cells(cell2.Row, i).Value GoTo 1 End If Next cell2 1: Next cell1 End Sub Now you can return to the sheet with the report and run the macro Translate through the menu Service – Macro – Macros (Tools — Macro — Macros) or by pressing ALT+F8. All words from your report found in on the sheet Dictionary, will be replaced by words from the next column, i.e. translated into another language. By running the macro several times, we will translate our report sequentially into -English-German–English-and so on in a circle. It will look like this:
Handy, isn’t it? 🙂
- What are macros, where to insert macro code in VBA, how to use them
- Converting text to transliteration