如何避免重复运行TextToColumns时日期日/月互换问题？

我有多个关联数据源，使用宏执行TextToColumns函数时，重复运行会导致日期的日与月互换；再次运行则恢复原始状态（此变化并非仅修改格式，会影响后续流程对日期各部分的识别）。

复现代码 #

测试子程序及转换函数如下：

Sub test()
    Dim T_Test As ListObject
    Set T_Test = ThisWorkbook.Worksheets(1).ListObjects("T_Test")
    
    Call Convert_TextToColumns(T_Test, "Date", "DD.MM.YYYY")
End Sub

Sub Convert_TextToColumns(ByRef tbl As ListObject, headerName As String, nrFormat As String)
    Dim rng As Range
    Set rng = tbl.ListColumns(headerName).Range
    rng.NumberFormat = nrFormat
    rng.TextToColumns Destination:=rng, DataType:=xlDelimited, Other:=False, FieldInfo:=Array(1, xlDMYFormat)
End Sub

复现步骤 #

• 插入含示例数据的表格并命名为"T_Test"
• 运行宏后，日期01.12.2022会变为12.01.2022
• 再次运行宏，日期恢复为原始值

已尝试的无效方案 #

我曾尝试用移除前导单引号的函数替代TextToColumns，但无法将日期转换为可按年、月、日分组筛选的完整日期，相关代码如下：

Sub Convert_TextToDate(ByRef tbl As ListObject, headerName As String, nrFormat As String)
    ' Only convert if not already in date format.
    Dim rng As Range
    Set rng = tbl.ListColumns(headerName).DataBodyRange
    rng.Replace What:="'", replacement:"", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True
    
    Dim cell As Variant
    Dim temp As Variant
    For Each cell In rng
        cell.Value = cell.Value ' tried this (doesn't work)
        cell.Value = Replace(cell.Value, "'", "") ' tried this (doesn't work)
    Next cell
End Sub

寻求解决方向 #

• 是否可判断某列已执行过TextToColumns（即已是完整日期格式），以此避免重复执行？
• 或有其他可行方案？

内容的提问来源于stack exchange，提问作者user8788429