Force VBA to use British localisation

Question

I've hit a snag with a spreadsheet I've created. It works fine on my PC but I know that some of the other PC's in the office have US localisations. Is there a way to force VBA to check things using British localisation?

The issue I'm having is that as part of the macro I use Text to Columns to ensure that the dates entered are appearing as dates, not numbers or text. On my PC it seems to be working fine, but I know I set this up as UK localisation.

Other people are having issues, and aren't aware of whether it's a UK localisation or not. Rather than having to faff about with checking other people's localisation or insisting that they have UK localisations on their computers, it'd be ideal if I can stick an extra line of code into VBA to force everything done in this macro to be done in the UK localisation.

The issue itself is that the program is switching dates around to US format from UK format. It appears to be happening when the text-to-columns stage happens in the macro.

Is there a way of getting VBA to check automatically if it's a UK localisation, if not then changing it to the UK localisation and then changing it back at the end? I don't want to force every pc to have UK localisation because it might cause other problems or annoy users - something that is definitely NOT desirable!

Cheers!

Answer 1

OK, so I've come to the conclusion that it's not just a localisation issue. I've checked on another pc in the office that is set to UK localisation and it's reproducing there.

Two potential solutions for this issue I've come across are:

Firstly, as Excel stores dates as a separate identifier, use a userform to input the dates - The plan is to get the users to click a "Select Date Range" box, which will call the userform "Select Date". This will contain six drop down boxes - two sets of three selecting day, month and year. Day will be numeric, month textual, year numeric, as this will mean that both the user and excel will be under no confusion as to which is which.

The second solution is to get excel to store the dates as DDMMMYY - or 01jan2015 for example. This is an alternate solution a colleague of mine has suggested, but as I'm planning on teaching myself userforms at some point, may as well be now!

Hope this helps anyone who has the same issue :)

EDIT-

Ended up using the solution Trevor Eyre suggested Here: Formatting MM/DD/YYYY dates in textbox in VBA for userforms. It's elegant and useful.

Answer 2

I have similar issue when copying dates into matrices. To avoid misrepresentation I have transformed date into a number using DateSerial(yy,mm,dd), and then just formatting it as date when showing on spreadsheet. All calculations work fine (i.e. networkdays).

Answer 3

Guyz,

There is a very awesome native function:

    DatePart("d", "01/08/2015") will return you the day
    DatePart("m", "02/08/2015") will return you the month

This function appearently takes into account local settings. This function resolved all my problems when I had to send my code to other locations

A very poor solution would be to use:

    day=Split("somedate","someseparator")

Hope this helps