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:

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…