Converting a column of UK dates to text in VBA

Question

My system is in a UK locale (windows 7, office professional Plus 2013).

I have some columns of dates, stored as dates in the Uk format (dd/mm/yyyy) and I need to convert them to text in the same format. The problem i'm having is it seems that every VBA function defaults to spitting out US format dates when converting to text.

I've tried using the range.TextToColumns function and example code is below (mostly macro recorder based - oddly text to columns works fine in excel, but the recorded code gives US dates... bizarre)

Sub ColumnToText(rngColumn As Range)
' ---------------------------------------------------------------------
'   Takes a column, coverts it to text
' ---------------------------------------------------------------------

rngColumn.TextToColumns _
    Destination:=rngColumn.Range("A1"), _
    DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, _
    Tab:=False, _
    Semicolon:=False, _
    Comma:=False, _
    Space:=False, _
    Other:=False, _
    FieldInfo:=Array(1, 2), _
    TrailingMinusNumbers:=True

End Sub

There are plenty of ways I could do this by looping through the cells, but i'm aiming for an efficient solution that I can use in future as well as this case (i'm a little OCD about optimisation).

Ideally I'd like to be able to get the TextToColumns function to work in VBA the same way it does in Excel, but I realise that may not be possible.

Answer 1

This will do the whole column at once.

Sub foo()
[A:A].NumberFormat = "@"
[A:A] = [IF(A:A<>"", TEXT(A:A,"dd/mm/yyyy"),"")]
End Sub

Answer 2

With dates, if your OS (Windows) is in English-US you can use r.Value = t

Set r = Range("a1:a9")
t = r.Value
r.NumberFormat = "@"
r.Value = t

Otherwise if you do not want the dates to be converted to the US date format, you must use r.FormulaLocal = t

Set r = Range("a1:a9")
t = r.Value
r.NumberFormat = "@"
r.FormulaLocal = t

When a data is sent from vba to a cell, an internal type conversion function is called if required, that is if the data type is different from the numberformat property. This internal type conversion function recognizes only the international date format (yyyy / mm / dd) and US dates formats (eg. mm / dd / yyyy)

You can use cell.FormulaLocal = data in place of cell.Value = data to avoid this function to be called.