Switching Languages: Formulas vs. VBA

[Sal Paradise]

I am currently creating a massive bilingual report using Excel based on data gathered from a customer visit, as most of the engineers are non-native English speakers, and most everywhere else in the world wants reports in English. Currently I am using this roundabout method:

I have a lookup Table sheet:

Excel Workbook
	A	B	C
1	English	Japanese	French
2	Dog	?	Chien
3	Cat	?	Chat
4	Fish	?	Poisson
5	Song	?	Chanson
6	Trash	??	Poubelle
7	House	?	Maison
LookupTable

This sheet holds all my language information (the real sheet actually has a label row in column A which is added to manage the dropdowns for several categories, but that's neither here nor there).

This sheet is for inputting data in your native language:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Input:	English
2	Output:	French
3
4	#	Item
5	4	Cat
6	2	Dog
7	1	Trash
8	4	House
9	2	Song
InputSheet

As you can see, you decide the input language (which determines the list the input dropdown comes from), and you decide the output language (which will determine the OutputSheet language).

In order to get from step A to step B without massive messy formulas, I create a 'tweener sheet:

Excel Workbook
	A	B
1	#	Item Index
2	4	2
3	2	1
4	1	5
5	4	6
6	2	4
LookupSheet

#VALUE!

</td></tr></table></td></tr></table>

This stores all my data in between, so I have the indexes of all the things I need to look up, regardless of what the output language becomes.

Then I look up those indexes in the final step to get my output:

Excel Workbook
	A	B
1	#	Item
2	4	Chat
3	2	Chien
4	1	Poubelle
5	4	Maison
6	2	Chanson
OutputSheet

#VALUE!

</td></tr></table></td></tr></table>

Presto! Without using any French at all, I translated my list into French, and could do it into Croatian if I added the data.

The problems with this method (as far as I can tell) are primarily:

• If I have a dozen dropdowns (as I do), it means managing dozens of named ranges
• I need an intermediate page to diddle the data without it getting unwieldy
• It's slow (relatively speaking), since everything is all formulas
• Let's say I have a French guy and an English guy making a Japanese report. The French guy wants to input in French, and the English guy in English, but the input method only allows a single language for input (if you switch partway, the intermediate sheet can't look anything up, and to add the possibility to the formulas would be a massive processor hog)

Is there a similar way to do this with VBA that is cleaner or neater? Are there obvious improvements I can make to my method or formulas without using VBA? Is this one of those problems that so few people have that it won't get any response? Please let me know.

 

[Sal Paradise]

I'm taking it that nobody has to do this with Excel?

Perhaps if I redefine the problem, I can get some more brainpower on this.

I need to make sheets which are widely used in multiple languages. We need to be able to enter in one language, and output a report in another based on a translation table filled in somewhere. There will be up to 200 rows and 20 text lookup columns on each sheet, in addition to many formulas doing math, so performance does become an issue.

I want to know the best way to translate everything into the target language with as few problems as possible, and preferrably with a manageable understandable logic so that someone who looks at it can (generally speaking) figure out what's going on and edit it if needed.

The original request was for no VBA, but after seeing the results and complexity of the sheet, VBA has become a possibility. Thinking long-term and flexibility, VBA seems like the way to go, but I can't think of a really efficient way to code a giant translation function and was hoping to get suggestions because I don't know about how various methods of going about it will affect sheet performance.

For instance, I could make an array of the lookup sheet to use as a table, loop through it looking for the cell value, and then output the desired language into another sheet. However, would that be slow due to the fact it'd be looking through hundreds of lines in several columns each time? Would it be better to make data validation dropdowns in the desired input language and then write VBA functions to write a lookup for that language on Workbook_Change for those ranges?

How would you go about it?

 

[nbrcrunch]

no experience with this but I did google it....

http://office.microsoft.com/en-us/ork2000/HA011382941033.aspx

 

[Sal Paradise]

That doesn't exactly match what I'm trying to do. This has nothing to do with multi-lingual versions of Excel, or different versions/languages and matching things up, but rather to do with translating a report as in my original post from an input language to an output language via a translation dictionary/lookup table.

Currently I do it via formulas (as described in the OP step-by-step), but I am thinking about switching to a VBA solution to do the same. The question is how much more efficient that will make my sheet, and what the most efficient way to go about it would be. If I just write any functions to do lookups with some sort of a massive array, and then find out it's not ideal, I'd have to start from scratch. Rather than that, I'd like to know what the best way of dealing with that much information is.

 

[jindon]

Do you want to translate every single language to particular language from your lookup table?

If so, how many languages do the lookup table has?

 

[nbrcrunch]

how about this

http://www.mrexcel.com/Excel_Translate_training.html

 

[Bdra]

I may be missing something in what you are trying to accomplish, but this looks like one of those things that are painfully complicated to do via formulas, and extremely simple via VBA.

This may be a brain-dead approach, but I would just take each input word, go to the lookup table, and use Find to locate the word in the appropriate column. Something like this:

With Sheets("LookupTable")
        Set InputWordCell = .Columns(InputLanguageColumn).Find(What:=InputWord, _
            After:=.Cells(2, InputLanguageColumn), LookIn:=xlValues, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, Searchformat:=False)
 
        If Not InputWordCell Is Nothing Then
            InputWordRow = InputWordCell.Row
        Else
            'Do something to handle words not found on the list
        End If
 
        OutputWord = .Cells(InputWordRow, OutputLanguageColumn).Value
    End With

You are right that looping through every item in the lookup column would be slow. The Find method is much faster. (This is a little out of my depth, but I believe Excel's built-in functions are coded in C++, which executes much faster than VBA. Therefore, use built-in functions whenever possible.)

Also use Application.Calculation = xlManual etc. to optimize execution speed. This code won't take long to set up. I would suggest giving it a spin to see if the speed is tolerable.

 

[Sal Paradise]

Thank you very much for a place to start -- that does look like it would work. I'll toss up an example when I have free time and give it a spin around the reporting.

The reason I (very very painfully) originally made the report with formulas is because the person who wanted it asked me not to use VBA. He envisioned a teeny tiny auto-reporting sheet that was almost all manual input on the backend, monolingual, and otherwise much like what he was already doing by hand in PowerPoint. As projects go, the further along it got, the more he wanted it to do, to the point where I explained it would be a lot quicker to do it in VBA, but he was worried that someone's PC wouldn't be set up with Macros, or other problems would occur.

The primary thing is that it needs to work. The engineers go out for a week or 2 weeks to conduct an engineering study, and have to provide a report on the final day. Usually that means sitting with liquor in a hotel room at 2am on Thursday night trying to put everything into PowerPoint by hand. If they use Excel, it needs to be incredibly flexible, and otherwise stable so that they don't get to the end and find out they can't make the report properly.

Another problem is that the computer making this is Excel 2000, the computers the engineers have are split between 2000, 2003 and 2007. That means code has to work on all systems, which is an added hassle. So there's an advantage to keeping it simple.

We'll see what comes from on high and how to proceed, but using find looks like it may be the way to go. I'll do some benchmarking later.

Do you want to translate every single language to particular language from your lookup table?

If so, how many languages do the lookup table has?

The language table currently has 2 languages. It will have another two added in the future. There is the possibility that even more will be added after that. I'd say 6-7 languages max for the next couple years, but if it's possible to get translations and jobs that use those languages (especially if translation is easy) then potentially more.

Currently for language selection I have an Input dropdown, and an Output dropdown. As I showed in the first post, I lookup each entry in the InputLanguage column of the table, and write down the matches for each cell in another sheet which holds the non-language reference numbers. The reason for this is that occasionally, a multi-national client asks for a report in another language. By keeping the intermediate step, we can just switch the output language and generate an additional report.