excel vba string to date

Question

Windows 10 Pro, Regional Settings to UK English. In Excel VBA I have a string "02/05/2017 16:30" That, in the UK, means "02 May 2017 16:30"

But VBA turns this to US format somehow and in the cell puts "05/02/2017 16:30"

The VBA code is like this

Dim sField As String
sField = "02/05/2017 16:30"
ws.Cells(1,1) = sField

I can use CDate to get around this but CDate but that requires extra code to determine which cells are dates and which aren't, whereas the implicit conversion works for all types.

Answer 1

Use a Date variable instead, and always provide your date in MDY in VBA.

Dim sField As Date
sField = #05/02/2017 16:30#
ws.Cells(1,1) = sField

AFAIK in VBA you must always work the 'American way', with dates MDY. It does NOT follow regional settings. Which is good, because that enables running the same code on heterogeneous environments.