Solved: Distributing to foreign users

[chrismc]

Hi,

I have some VBA that I have written which is in English. I want to distribute it to a number of people some of whom are German. Please note - their English is much better than my German.

1 - if I send the spreadsheet will the VBA run on a German language version of Excel without requiring translation.

2 - I would actually like them to be able to see and understand the VBA. Obviously, I would have to translate any comments but what about keywords? Can they be translated simply by opening the code up on a German machine or is there something else that I can do to help them?


Thanks


Chris

[MWE]

Hi,

I have some VBA that I have written which is in English. I want to distribute it to a number of people some of whom are German. Please note - their English is much better than my German.

1 - if I send the spreadsheet will the VBA run on a German language version of Excel without requiring translation.

2 - I would actually like them to be able to see and understand the VBA. Obviously, I would have to translate any comments but what about keywords? Can they be translated simply by opening the code up on a German machine or is there something else that I can do to help them?


Thanks


Christhis is a really interesting question. VBA code (or any other code) is not English or German or anything else. It is code appropriate for that language and should run on any system that has the appropriate compliler or interpreter or whatever (I think). There may be some differences in called procedure names, but I doubt it. Their implementation of Excel probably has lots of German text in help screens and similar.

I have provided code to people all over the world and never received any feedback that there was a language problem with the code (there may have been other problems, but not that their system did not understand English code). I have not had an opportunity to provide code to Vulcans, Ferengies or the Borg, but I suspect my code has universal appeal.

English is pretty much the programming language world-wide. I would not expect decent programmers in Germany to have much trouble with what you wrote. You have a bit of luck that it is Germany. German and English have common roots.

There are some things you can do to make your programs more universally understood:
One is to be careful about how you treat dates and times. The US date format of mm/dd/yy is not used anywhere else. Most systems convert from underlying formats to whatever the user set for date and time conventions as long as they realize that it is a date. But perhaps a better way is to make sure that dates you post to cells, messages, forms, etc., are in an unambiguous format. Personally, I like the dd-mmm-yyyy format (today would be 16-Feb-2006) and 24hour time.

Another is to not use slang in any text. That may be hard to do because US slang, UK slang, Australian slang, etc., have become so much a part of their respective languages that many people may not realize that they are using slang. Use correct English all the time and that includes careful attention to punctuation and spelling.

[XLGibbs]

One is to be careful about how you treat dates and times. The US date format of mm/dd/yy is not used anywhere else. Most systems convert from underlying formats to whatever the user set for date and time conventions as long as they realize that it is a date. But perhaps a better way is to make sure that dates you post to cells, messages, forms, etc., are in an unambiguous format. Personally, I like the dd-mmm-yyyy format (today would be 16-Feb-2006) and 24hour time.

Excellent point. Good information on the internation date issue here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnovba00/html/InternationalIssues.asp

[Bob Phillips]

Hi,

I have some VBA that I have written which is in English. I want to distribute it to a number of people some of whom are German. Please note - their English is much better than my German.

1 - if I send the spreadsheet will the VBA run on a German language version of Excel without requiring translation.

In most cases it will run fine. Excel will identify the langauge and regional settings upon opening, and use these. Function keywords like IF AND ROW SUM etc are all tokenised, so Excel will take the displayed value from the local setting.

There is one big exception to this rule, and that is Analysis Toolpak functions. These do not automatically translate, so should be avoided. In most (all?) cases, they can be re-written with standard functions.


2 - I would actually like them to be able to see and understand the VBA. Obviously, I would have to translate any comments but what about keywords? Can they be translated simply by opening the code up on a German machine or is there something else that I can do to help them.

The VBA code will be seen on any other machine exactly as you write it. So if you want the to read your comments, you will need to provide a translation.

The biggest problem is dates if you are a US user. VBA dates are US centric, but the rest of the world uses a different datea format. In VBA this can usually be avoided by casting the date to force it to the local format (CDate(myDate), but you need to test it. Probably not a problem as you are in the UK, but a good defensive practice anyway.

[TonyJollans]

I agree with the consensus that the VBA should be OK - VBA doesn't come in different language versions.

As has already been mentioned, Date and Time formats as well as numeric separators differ from country to country, and custom formats can, potentially, cause problems (although I believe built-in ones will adjust).

What also differs is all sorts of things in different language versions of the parent Application (Excel). Bob is probably correct when he says that Functions are tokenised - in cells. I'm not so sure about in Named 'Ranges' (or even CF?).

Also literals in VBA which include Functions (or even comma separators destined for formulae which will need semicolon separators in some language versions) and/or Toolbar and Control names can cause problems. I'm not sure about the operation of the WorkSheetFunction object either.

All in all I would say you need to test in the language version you are going to run in.

[brettdj]

There is one big exception to this rule, and that is Analysis Toolpak functions. These do not automatically translate, so should be avoided. In most (all?) cases, they can be re-written with standard functions.Yep- I got burned by a Spanish version of YEARFRAC a couple of weeks ago

When coping with the American date preferences I've found it easier to change the regional settings via control panel than to redo date calculations.

[chrismc]

Thank you all for your input. Fortunately, my code will not touch any of the potential problem areas that you have highlighted so it's looking good.