VBA equivalent to VBScript's 'SetLocale

Question

To summarize my problem, I'm currently having the exact same issue as the person from this question here. I'm trying to parse a US date into an Excel sheet that has German as default and gives me a type mismatch on most of the dates because of it.

SetLocale sounded like the perfect solution to my issue, but after a minute of further research I discovered that GetLocale and SetLocale are apparently not supported in VBA.

It sort of worked when I assigned the parsed date to a String variable (I end up with a column using either MM/DD/YYYY or DD/MMM/YYYY format depending on whether or not I had a type mismatch, as long as I use On Error Resume), but I need them in a MM/DD/YYYY date type/format in order to compare all the parsed dates to a specific date in another cell (attempting to determine if the site has had any updates since the date entered in the specific cell).

I've also tried doing TimeStamp = Format(TimeStamp, "MM/DD/YYYY") (TimeStamp being a variable containing the parsed date), but it doesn't seem to be working- most likely due to the type mismatch error.

If anyone knows a VBA equivalent of the SetLocale function used in the linked question for me to try out, I would greatly appreciate it. If there isn't any, I'll be happy to amend my question and add my current code here to try and hammer out a solution together.

Thank you for your time and your help.

Answer 1

If you know they are all in US format, you can use a function like this:

Function ConvertUSDate(sDate) As Date
    ConvertUSDate = Evaluate("DATEVALUE(""" & sDate & """)")
End Function