Tag Archives: Microsoft

Whose Language is it Anyway?

Over the past couple of days I’ve been having one of my periodic frustrations with Microsoft Excel. This time it was down to date formatting. Basically I was asked to produce a VBA macro that saved off the contents of one worksheet to a CSV file.

This is in theory pretty straightforward as Excel has an option on the Save dialog to save in CSV format. However I didn’t bargain on the complications of Excel.

One of the common problem areas that people come across on Excel relates to it being a US product, so by default it will use the confusing US numeric date format, of mm/dd/yy. As a result we will always ensure that all of our dates are defined with a textual month, which avoids too many problems, we also ensure that the international settings are set to UK, which usually avoids problems. When you save out a CSV manually, the dates come out fine. However when you do the same thing in a VBA macro it ignores the settings, and converts everything to the US numeric date format.

After some digging around, I found that this is because VBA maintains a different language setting, so although I was working on a UK English machine, with fully UK English settings, VBA was in US English.

Having found this out, I then tried to force VBA into UK English, not possible as the language libraries are separate too. So this morning I got a copy of the VBA Software Development Kit, complete with language files. I ran through the install, and got to the screen where it allows me to choose the language in which to install VBA:

Language Choice

As you can see, not much of a choice at all – the only English provided is US English. If I want to get the dates to save in any other way than the confusing US numeric dates, I have to manually output the file.

As one respondant to a similar query about dates online said VBA is very US centric…