VBA Dictionary Macro to find and replace (translate) text

[excelstarter1]

Hey guys, I am Robert and new to this forum. As I am an Excel/VBA beginner, I hope you can help me with my problem. My head is already in pain

I am trying to build a VBA dictionary macro. What is the idea/problem: I have an Excel file which my company is using all the time to consolidate different information from e.g. subsidiary companies or customers. Our main Excel file is in English (and the content is always changing). However, we would like to have a macro to automatically translate all the content from the English Workbook to another language e.g. Spanish or Italian.

Example Table in this original English Excel File:

Customer	C1	...	...
Country	Denmark	...	...
Name	...	...	...
Item	...	...	...
Price	100	...	...

<tbody>
</tbody>

Basically the macro should load an Excel File which works as a dictionary (with column 1 containing English word, column 2 Spanish translation, column 3 Italian translation etc.). Then I want the macro to find and replace all the words/text in the original English Workbook (highlighted in blue in above table) with the Spanish or Italian Translation (from the dictionary file).

Basically it is a loop for each cell in the whole workbook (all worksheets) by comparing each cell content (text) with the dictionary and replacing the original content with the translation (from the dictionary).

Does anyone have an idea how to start??

I read a lot about the "Scripting.Dictionary" object, but I dont think this will work in my case as I have more than 2 columns in the dictionary (column 1: original English, colum 2: Spanish translation, column 3: Italian translation and so on).

Thank you very much in advance!!!

Best regards
Robert

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

If you are using Windows then Dictionaries should work. They don't work on Macs.

Dictionaries can hold all sorts of things including arrays and other dictionaries so there is plenty of scope for adding words and languages - and you thought you had a headache already?

May I respectfully suggest that you do not do it the way you specified because I foresee problems. For instance, if the change is not entirely successful for some reason (e.g. the original language was not what you thought) then some words will be changed and others not. After that you will never be able to get it working again.

It would be more reliable if the translation were run as some kind of reporting step. That is, have a master copy in a set language then translate it on demand. Would that be acceptable or do you want to then be able to make changes in the new language. The former could probably be done with VLOOKUPs. I think the latter will be difficult.

Regards,

 

[excelstarter1]

RickXL thank you very much for your reply and suggestions.

I would still prefer to stick with the macro. Missing out a few words is not a big issue. Thats quite easy to detect. It would still be very helpful if the dictionary macro would cover 80-90% of all the text/words.

Unfortunately the excel file looks different every time so a master file oder copy would not help in my case and the vlookup would be quite tedious with 40+ tabs in a workbook...

Do you have a first idea on how to start with the dictionary macro so I can try to work it out? I just need an initial idea and some help. Right now I am just lost and dont know where to start...

Thanks again!

 

[RickXL]

Here is a starter. The precise code will depend on yet to be determined constraints.

I have assumed that all translations will be from the same language - English in this example.
If you need to translate from any language to any other you might want to convert the words to "meaning codes" or IDs of some sort as an intermediate step. Otherwise you will have lots of repetitions in the data and that offends my database design sensibilities. Basically, a simple rule is, if you routinely need to add columns to a database table then you have the wrong design.

The code is:

Sub Test()
    Dim dic     As Object
    Dim arr     As Variant
    Dim i       As Long
    
    Set dic = CreateObject("Scripting.Dictionary")
    With ThisWorkbook.Worksheets("Words")
        arr = .Range("A2", .Cells(.Rows.Count, "E").End(xlUp))
    End With
    
    For i = 1 To UBound(arr)
        dic(arr(i, 1)) = Application.Index(arr, i, 0)
    Next
    
    With ThisWorkbook.Worksheets("CopyOfData")
        For i = 1 To dic.Count
            .Cells.Replace What:=dic.Keys()(i - 1), Replacement:=dic.Items()(i - 1)(2), LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        Next
    End With
End Sub

The data I used was:

Excel 2013
	A	B	C	D	E
1	English	German	French	Italian	Spanish
2	dog	Hund	chien	cane	perro
3	cat	Katze	chat	gatto	gato
Words

The data to be converted was the words cat and dog randomly inserted into a worksheet called CopyOfData.
The dictionary data was read from a worksheet called Words.
The language to be translated is defined as a number - the one in red.

How does it work?

The word data is read into an array called arr.

That array is then copied into the dictionary a line at a time. The first word (column a) is used as the Key and all the words are added as a dictionary item in the form of an array. The row can be found by specifying the English word and the other words can be located by specifying the column number. So you could find a word, look it up then determine the foreign equivalent.

In this example, I check all the words in the dictionary and run a Replace statement for every one.

dic.Keys()(i - 1) means the Key word in row i-1 of the dictionary. (Dictionaries always start from row 0.)
dic.Items()(i - 1)(2) means the associated Item in row i-1 but take the second column from the array in there.


There are many other ways to do this and there may be a better way for your application but it is hard to tell without all the details.



Regards,

 

[excelstarter1]

RickXL, wow thank you so much, thats a great starts for my macro and works already
Will have to try a few things now.

Two questions so far:
(1) how can I add formulas with text to the find and replace routine?

e.g. a formula is ="customer "&A1
Now I want the macro to translate the word "customer" within the formula. Do you have an idea?

(2) how can I add titles/test in charts to the routine?

Best regards

 

[excelstarter1]

One issue: for some reason when I extend the dicitonary to 3 or more entries, the macro does not consider the additional entries.

e.g. with 40 entries, the following code returns only 19 existing entries.

MsgBox "Dictionary contains " & dict.Count & " entries"

Maybe there is a problem with the following code / counting procedure?

    For i = 1 To UBound(arr)
        dic(arr(i, 1)) = Application.Index(arr, i, 0)
    Next

 

[RickXL]

Having slept on the problem, I don't think you actually need a Dictionary at all. The worksheet that contains the word list is actually the only dictionary you need. Using a Dictionary Object just replicates that.

Why not just read in the data from the columns you want and then loop round them with the Replace step:

Sub Test()
    Const lang1 As Long = 2
    Const lang2 As Long = 4
    Dim arr1    As Variant
    Dim arr2    As Variant
    Dim i       As Long
    Dim lr      As Long
    
    With ThisWorkbook.Worksheets("Words")
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        arr1 = .Range(.Cells(2, lang1), .Cells(lr, lang1))
        arr2 = .Range(.Cells(2, lang2), .Cells(lr, lang2))
    End With
    
    With ThisWorkbook.Worksheets("CopyOfData")
        For i = 1 To lr - 1
            .Cells.Replace What:=arr1(i, 1), Replacement:=arr2(i, 1), LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        Next
    End With
End Sub

This should remove some of the other issues as well. It also means that it is now easy to change from any language to any other. This is set by the red numbers.

The first step that reads the words into the arrays is not strictly necessary. You could just read the cell values in the same loop as the Replace command. However, it should speed things up a little depending on how many words you need to translate.

Regards,

 

[excelstarter1]

Awesome, thats even better!! It's working!

(1) How can I include case insensitive comparison, so that company and Company is handled as the same word?

(2) How can I tell the macro to run through and translate all worksheets in the workbook? Also does the current code run through ALL cells the worksheet or just the cells with content? Otherwise it would slow down the whole macro, right?

(3) How can I add formulas with text to the find and replace routine?
e.g. a formula is ="customer "&A1
Now I want the macro to translate the word "customer" within the formula. Do you have an idea?

(4) How can I add titles/text in charts to the translation routine?

Thanks!
Regards

 

[RickXL]

Point 1. It should already be case insensitive.

            .Cells.Replace What:=arr1(i, 1), Replacement:=arr2(i, 1), LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False

Regards,

 

[excelstarter1]

Thanks, didn't notice the MatchCase Function.

Any thoughts on the other points? I am still struggling...

Regards