Localization of formulas in Excel

The name of an Excel file

In an Excel sheet I needed the name of my Excel file as a value in a cell. For a problem like this, Google is your friend, so I did a search for “get the name of my workbook into an excel cell“. And of course the first link was a good one, as it pointed me to Excel formula: Get workbook name only. The answer didn’t look very simple by the way:

But I faithfully pasted it into my sheet. Unfortunately it didn’t work! Owww, of course: I was working in the Dutch version of Excel and that wants the formulas to be in “Dutch” too. Apparently Microsoft thinks the users can understand all the math in those difficult expressions, but the English words need to be translated for them.


With some trial and error I translated the formula into Dutch:

Personally I think the Dutch translation is more difficult to understand than the English version, but it worked. That is the main thing.
But when I opened the workbook on a PC with an English version of Excel, the Dutch formula didn’t work of course!

I started looking for a solution. First of all, I pasted the original formula into the cell next to the one that didn’t work. It worked fine on my English language computer. But I expected it would fail again on the Dutch computer. To my great amazement that was not the case: it worked without a problem and the formula had been translated automagically!

So I could have saved myself the trouble of translating the formula? If you look closely, you will notice this is not exactly the same as my own translation: the quoted texts were left untranslated! And yet it works. Apparently the Dutch Excel doesn’t mind whether you write “filename” or “bestandsnaam”.

Translated backwards

Then I took a detailed look at what the English version of Excel had done with my translation. Hadn’t that been translated back into English correctly? Well, almost:

Again the formula had been translated, but not the quoted strings. And the English version of Excel hasn’t got a clue about what a “bestandsnaam” is.


Apparently formulas are stored internally in English or in a binary format, but the content of strings remains untouched. It is not a problem to get formulas from the Internet, but do paste them into a spreadsheet that is in the same language as your formula.

And don’t fall into the trap of using Dutch terms to indicate which information you want to request in the function CELL( … ); the English terms work in the Dutch version of Excel too and your spreadsheet will be usable in all languages.

I would be curious to know whether this tip also applies for the German and Portuguese versions of Excel. Can someone please experiment with that and let me know in the comments below?


WordPress filter repairs WPML

Error 404: File not foundFor a while it wasn’t possible to switch from the English to the Dutch version of my website. The Dutch version was available if you accessed it directly, and it was working fine, but WPML’s language switch didn’t work.
If you used the language switch, you would end up at www.elangco.nl/wordpress/wordpress/wordpress/wordpress/wordpress/wordpress/, not at www.elangco.nl/. Apparently there was some sort of bug in WPML, but it wasn’t solved, in spite of installing the latest version, help from a support person, etc.

I know the background of this problem is that I want my WordPress files located in a subfolder on the site, not in the root folder, but hey, this must be possible, right? I refuse to move the files to the root folder. I like them just where they are.

WordPress filters

But as we all (should) know, WordPress is full of hooks to change its functionality.
It should be possible to change the faulty URL before it is presented to the visitor of the website.
I created this simple snippet of code:

add_filter('the_content', 'rewriteURL');

function rewriteURL($URL) {
$URL = str_replace('.nl/wordpress/wordpress/wordpress/', '.nl/', $URL);
return $URL;

And stuck it into the functions.php of my theme.

And it worked like a charm! The links to the Dutch version all seem to work now.

Press This

But what would happen if I put this message online? The filter would probably work on the content of this posting too, making the problem invisible and making the solution look like “no operation”… And indeed, so it did.

So I had to revisit this post a few times until everything looked hunky-dory. I had to write ‘/wordpress’ six times in my message to make it appear three times in the posting.

Of course editing a file in my theme is not the right way to solve this in the long term, I will have to put my code in a child theme or in a plugin. At least I have shown now, that it will work when I do so.

And what does Yoast think of this?

I routinely use Yoast’s SEO plugin to check my messages. For this message I had chosen a keyword that appeared 16 times in the message. (You can guess what it is, right?) Now Yoast thinks that is too often. Hmm… I would say it is fair use of the keyword in this case: I had to write it many times to explain what is going on here.


More than a year has passed since all this happened. Recently I installed the premium version of Yoast SEO and things didn’t work the way they should. I had to deactivate all my plugins in an attempt to solve this issue. Of course my home-made plug-in was the first one to disable.

It did not fix the problem with Yoast SEO, but the problem with the url also didn’t come back. Maybe the problem in WPML had been fixed in the mean time. I disabled many more plug-ins until only WPML and Yoast SEO were left. (I was afraid I would loose all my settings and the links between translated articles and pages if I would switch off WPML.) But now deabling WPML was the only option left.

And there was something else: in the debug console of my web browser, I could see many failed requests to address the REST API. My WordPress installation is located in elangco.nl/wordpress, so REST API requests should go to elangco.nl as well, but they were going to elangco.com instead.

Learning Dutch

I'm Learning Dutch ButtonIn addition to taking Dutch language classes, it is essential to use your target language in the real world. But all of the students I teach Dutch to have the same problem: Dutch people speak English to them! This is of course very convenient when you go shopping, but when you are trying to learn Dutch, it is more a hindrance than a help.
I think Holland is quite unique in this respect. If you go to France for instance, you will not likely meet anyone who is willing and able to speak English with you. The Dutch are different: most of them speak one or more foreign languages and they are very eager to show they do.
So it was high time to find a solution for this. My students want to get an opportunity to exercise their new skills in speaking Dutch. That is why I have decided to make special buttons for them. On these buttons is the text: “I’m learning Dutch“.
It is my hope that Dutch people, when they read this, will react in the right way: don’t speak English to my students, but speak slowly, articulate clearly, and repeat what you said if necessary.
I will be handing out these buttons to all my students from now on, so if you meet one of them, you’ll know what to do!

Language training Dutch and English

Elangco also provides language training in Dutch and English. We started that last year and it is a great success. Most of my students are ex-pats, people who have been sent to the Netherlands by their company to work here for a year or more. An international company offers these language lessons and is subcontracting us (and many other teachers) to do the actual work.
Meanwhile I have also met ex-pats who do not get such a language training from their employer. They can contract me directly. This is of course an advantage for both of us, because no expensive intermediate organization is involved.
So far these contacts were acquired purely by mouth-to-mouth publicity, since these services were not yet advertized on our website. High time to change that. Read all about it on the page Language training.

Machine translation from Wordfast

I linked my own macro based machine translation into my Wordfast Classic translation workflow. I know many translators frown upon machine translation, but I think it can be very useful. I also think we will see more and more machine translation in the future whether translators like it or not.

Over many years I have developed my own set of macro tools in Microsoft Word that help me improve the speed and quality of my translation work. I know machine translation in general is still very poor in quality, but my macros are quite good actually. So why do I think I can do better than the big players on the market? Because my macros are not trying to do everything for everyone: They are very specific to my language pairs and my client’s subject matter. And of course all translations are handled manually: I do not really trust the machine, I just allow it to help me.

Calling a machine translation macro from WordfastThere was a problem with merging my own solution with the CAT-tool Wordfast: Even though my macro was called I never saw its output when I opened a new translation unit. After struggling with this for a long time, I posted a call for help on the Wordfast support forum at ProZ.com. I got some very helpful replies from John Fossey and Daniel Grau and I kept on researching the problem myself.

The solution I found works in two stages: I have wrapped the Wordfast macro that opens the next segment in a macro of my own and I use the macro call that is done by Wordfast just to set a flag to indicate to my wrapper macro it should do a translation on this segment.

So the way to use this is to call the wrapper macro every time you want to open a new segment instead of the standard macro WfNextSegment that Wordfast uses (linked to the key ALT+DOWN). You can link the wrapper macro to the same key, or to another key, for instance ALT+HOME.

This is the wrapper macro:

Sub MyNextSegment()
' MyNextSegment Macro
' Ask Wordfast to open the next translation unit
' After that do a translation if Wordfast has asked for one
    MyTrigger = False
    Application.Run MacroName:="WfNextSegment"
    If MyTrigger Then
      Call MyTranslate
    End If

End Sub

And this is the trigger macro:

Sub MySetTrigger()
' MySetTrigger Macro
' This is called every time Wordfast wants to call for MT
    MyTrigger = True

End Sub

Of course the boolean MyTrigger has to be declared as a global variable in the module:

Dim MyTrigger As Boolean

MyTranslate is the macro that does the actual translation work. I would like to leave that part as an exercise to the reader. 🙂


Language research with Google Ngram

A language blogger discussing technology is of course very interesting for Elangco. Today I found such an entry, discussing tools for analysing n-gram frequencies in a large database of text. (An n-gram, in language, is a sequence of n words or letters.)

I have always been a fan of the postings and podcasts on language by @GrammarGirl (Mignon Fogarty). But now she has surprised me with a posting on technology. In this post she explains about Google’s Ngram Viewer: A tool for searching word frequencies in the vast database of Google Books.

I didn’t know there were tools like this out there on the Internet, but she even points to several alternatives for the Google tool and explains some (dis)advantages of those. It seems like a great toy to play with, but it can also be very useful.

I used to do language research like this by entering words or short phrases into Google in different spellings and checking the number of hits found. But that will only tell you about the spelling in use TODAY. And of course the fact that a majority of the people on the Internet spell a word in a certain way does not guarantee that spelling is correct at all!

No, if you are looking for correct spelling, use a dictionary. And if you are looking for correct grammar, listen to GrammarGirl!

Anyway, I visited the Ngram pages for a quick look and I noticed that Google even offers the raw data for download. Using that you could do your own research and for instance do a comparison between different spellings of a word. That would solve one of the disadvantages Mignon mentions in her post: The tool only supports limited comparing of different n-grams.

But you will have to work really hard to analyse the material yourself: It is a huge amount of data! They also have statistics from different languages. Unfortunately no Dutch or Portuguese yet…

Multi-language WordPress… Other ways

The ink of my posting on WPML had hardly dried, when the makers decided it was time to turn their plugin commercial. This not only invalidated a lot of what I said then, but also made WPML a lot less attractive for new websites. Existing websites that have been built using WPML are now more or less locked in to keep using it and pay a substantial yearly fee for that.

It seems like a good idea to look for alternatives. I found a site that lists WPML itself, but also 4 alternatives to WPML:

Language Switcher

I have to admit WPML is very professional, so I am not sure any of these four come even close to it in features and flexibility, but I intend to find out. If you have experience with any of these, please tell us by leaving a comment.

Elangco’s Birthday

Today is Elangco’s official birthday. October 4th 2010 is the day Elangco was registered at the Chamber of Commerce. Time to look back. And time to look ahead.

Of course I wrote a business plan before I started the company. In a business plan you write about predictions of the growth of your business. But how can you predict that? I do know Elangco’s growth will be mainly by word of mouth: A happy customer may refer new customers to me. Such a process can be modeled using the Fibonacci series: 1, 1, 2, 3, 5, 8, 13, 21…. It starts off slowly, but it will grow faster and faster over time. We just don’t know the time scale: Will it be days? Not very likely! Will it be years? I don’t hope so! It has to be somewhere in between. So in my plans, I have estimated this time scale to be “a few months”. And that seems to be true.

But in reality the pattern of growth is very irregular: One moment there is little to do, then suddenly all projects seem to peak at the same time.

One thing is for sure: Running my own business is a lot more fun than working for a boss. Never before did I work so hard, but it feels like a vacation: I enjoy my freedom every day!


ATutor 2.0.3 released

ATutor 2.0.3 has been released. In this release two add-on modules have been integrated, several security enhancements have been added, and lots or little bug fixes and refinements have been made.

Existing users should upgrade as soon as possible to take advantage of new security features. Elangco has of course created a Dutch Language pack for this version again, so that our customers can immediately do this.

For a detailed list of changes, visit the ATutor: Change Log. View the demo to try the new features, and download ATutor to install or upgrade.

New in this ATutor release:

  • The External Tools Module, previously available as an add-on to provide support for IMS BasicLTI, has now been integrated as a standard module. BasicLTI allows administrators and instructors to extend ATutor with external applications by simply entering the URL of a BasicLTI tool, and entering its login information. External tools can be added to content pages as learning activities. See ATutor BasicLTI Certification
  • The Assignment Dropbox module, also previously available as an add-on, has now been integrated as a standard module. This module greatly simplifies assignment submission by reducing it to a single step.
  • IMS Common Cartridge support has been updated, replacing the validation scripts with the latest versions from IMS. This corrects a variety of incompatibilities in content, improving interoperability with other systems that support the standard. Slow cartridge validation has been corrected. Cartridges now import in seconds. See ATutor Common Cartridge Certification
  • The Infusion Javascript Libraries have been upgrade to version 1.4. These libraries add accessible, highly interactive components to ATutor, such as the inline editors, the tool reorders, and the multifile uploader. This upgrade corrects a number of bugs in the multifile uploader, making it usable across a wider range of Web browsers. See fluidproject.org for more about Infusion.
  • New Modules from this year’s Google Summer of Code: Projects included modules for integrating video conferencing (BigBlueButton), a Calendar module, a new iPad theme and updates to the smartphone themes, google apps and smartphone apps for ATutor Social, and a Google app for AChecker, among others. These will be refined further and included with future ATutor releases.
  • The Search OpenLearn module is being officially released along with ATutor 2.0.3. This module allows students and instructors to search through more than 600 free courses available at the OpenLearn site, the open content repository of the Open University in the UK. Content can be viewed online, or exported in a variety of formats. Instructors can import content directly into their ATutor courses where it can be modified or repurposed.


Disable monitor detection for Windows 7

Many people know the problem you get when you use multiple monitors in combination with one or more KVM-switches on a Windows 7 system: As is so often the case with Microsoft stuff, Windows tries to be “smart” and it reacts to your switching the monitor by moving application windows from one screen to another.

If you search for this problem on the Internet, you find many solutions, including disabling some system services or changing a registry key DMMEnableDDCPolling to 0.

All lines connected 1:1 except pin 11I have taken a different approach to solve this: The monitor detection works via a dialogue between the computer and the monitor through one or two seperate lines in the VGA cable. So all we need to do is to take away that connection!

I have built a simple adapter plug with a 15-pin VGA connector on each side (one male, one female). 14 lines are simply connected right through, only the connection on pin 11 is left out. This is one of the data lines in the VGA connection.

The result is the PC is now blind to my switching the monitors. This makes switching between the monitors so much more convenient! Only disadvantage is the PC cannot autodetect the best screen resolution and refresh rate when starting up. You may need to select the appropriate screen resolution manually when you power up the PC (maybe once every day?) After that you can switch as often as you like (100x a day?) All your windows and icons stay right where you put them!