Force VBA uses British localization

Question

I fell into the spreadsheet trap that I created. It works fine on my PC, but I know that some other PCs in the office are localized in the USA. Is there a way to get VBA to check things using UK localization?

The problem I am facing is that as part of the macro, I use Text to Columns to ensure that the dates entered appear as dates, not numbers or text. It seems to be working fine on my PC, but I know that I configured it as the localization of the UK.

Other people are having problems, and they don’t know if this is the localization of the UK or not. Instead of having to check other people's localization or insist that they have UK localization on their computers, it would be ideal if I could insert an extra line of code in VBA to force everything that was done in this macro , make in Localization UK.

The problem itself is that the program switches dates to the US format from the British format. It seems like this happens when the text-column phase occurs in a macro.

Is there a way to get VBA to automatically check if it's localized in the UK, if you don't change it to the localization of the UK, and then change it at the end? I don’t want every computer to be located in the UK, because it can cause other problems or annoy users - something that is definitely NOT desirable!

Hooray!

Answer 1

I have the same problem when copying dates to matrices. To avoid distortion, I converted the date to a number using DateSerial (yy, mm, dd) and then just formatted it as the date when I showed the table. All calculations work fine (i.e., on the network).

Answer 2

OK, so I came to the conclusion that this is not just a localization problem. I checked another computer in the office, which is configured to localize the UK, and it plays it.

The two possible solutions to this problem that I encountered are the following:

First, since Excel stores dates as a separate identifier, use a custom form to enter dates. The plan is to get users to click the Select Date Range field, which will bring up the Select Date user form. It will contain six drop-down boxes - two sets of three days of selection, a month and a year. The day will be a numeric, text month, numeric number, as this will mean that both the user and excel will not be confused as to what is.

The second solution is to get excel to store dates as DDMMMYY - or 01jan2015, for example. This is an alternative solution proposed by my colleague, but since I plan to educate myself on user forms at some point, maybe now!

Hope this helps anyone who has the same issue :)

EDIT -

Finished using the Trevor Eyre solution offered here: Formatting MM / DD / YYYY dates in a text box in VBA for custom forms. It is elegant and useful.

Answer 3

Guyz,

There is a very amazing feature:

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

This function naturally takes into account local settings. This function resolved all my problems when I had to send the code to other places.

A very bad solution would be to use:

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

Hope this helps