VBA TextToColumns分列结果与手动操作不一致原因及修复方法

• 存在被识别为数值（日期序列号）类型的单元格值，当前单元格显示为09/01/2022
• 手动操作流程：选中X列，打开「数据」选项卡下的「分列」功能，选择「分隔符号」类型，取消所有分隔符选项勾选，列数据格式选择「文本」后点击完成，操作后单元格正常显示预期值09/01/2022
• 同一设备、同一数据集环境下运行录制得到的宏，单元格最终显示为1/9/2022，与手动操作结果不一致

录制得到的原始VBA代码如下：

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

VBA调用TextToColumns方法的默认逻辑和手动操作GUI向导的逻辑存在区别：

• 手动操作分列向导选择「文本」格式时，Excel会直接提取单元格当前的显示文本，转换为文本类型存储，不会重新解析底层存储的日期序列号
• VBA运行TextToColumns时，不会继承GUI操作的格式保留逻辑，默认会先读取单元格底层的日期序列号，按照系统当前默认的短日期格式（不带前导零的M/d/yyyy格式）重新生成字符串后写入，即使在FieldInfo参数中指定了文本类型，也会触发这个自动解析逻辑，最终丢失月、日字段的前导零
• 宏录制器不会捕获操作前的单元格格式状态，也不会自动补全禁用自动日期解析的相关参数，是这类录制宏运行结果和手动操作不一致的核心原因

在执行分列操作前，显式将目标列的单元格格式设置为文本，阻断VBA的自动日期重解析逻辑即可，修正后的可运行代码如下：

Sub FixDateFormat()
    ' 预先将X列设置为文本格式，禁止自动日期格式转换
    Columns("X:X").NumberFormat = "@"
    ' 执行分列操作，参数与手动操作保持完全一致
    Columns("X:X").TextToColumns _
        Destination:=Range("X1"), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=False, _
        Semicolon:=False, _
        Comma:=False, _
        Space:=False, _
        Other:=False, _
        FieldInfo:=Array(1, 2), _
        TrailingMinusNumbers:=True
End Sub

如果不需要保留分列操作逻辑，也可以直接读取单元格的显示值写入为文本，运行效率更高，不会触发任何自动格式转换。

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