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

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).