VBA提取混合数据时金额转货币格式偶尔失效的原因排查

问题背景 #

单元格内容为金额与其他数据混合（金额后接空格及其他数据），编写VBA代码将数据拆分至相邻单元格。代码可正常提取数据并将金额转为货币格式，但偶尔会出现金额转换失效的情况（如金额745.20未完成转换），需分析问题成因。

相关VBA代码 #

Sub Rozdzielenie2()

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

Dim Tablica() As String
Dim Dane As Range
Dim i As Integer
Dim j As Integer: j = ActiveCell.Row

Set Dane = ActiveCell
    Tablica = Split(Dane.Value, Chr(10))
        For i = 1 To UBound(Tablica) + 1
            Arkusz1.Range("C" & j).Value = Tablica(i - 1)
            Arkusz1.Range("D" & j) = "=LEFT(RC[-1],LEN(RC[-1])-10)"
            Arkusz1.Range("D" & j).Value = Trim(CCur(Arkusz1.Range("D" & j)))
            Arkusz1.Range("E" & j) = "=RIGHT(RC[-2],10)"
            Arkusz1.Range("F" & j) = "=DATEVALUE(RC[-1])"
            Arkusz1.Range("F" & j).Value = CDate(Arkusz1.Range("F" & j))
            
            Selection.Offset(1, 0).Select
            Selection.EntireRow.Insert
           
            j = j + 1
        Next i
    Selection.EntireRow.Delete
    Columns("F:F").NumberFormat = "dd/mm/yyyy r."
    Columns("D:D").NumberFormat = "#,##0.00 zł"

'Columns("E").Delete
'Columns("C").Delete

With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub

失效成因分析 #

• 固定长度截取的逻辑缺陷：代码通过LEFT(RC[-1],LEN(RC[-1])-10)提取金额，默认金额后跟随的内容固定为10个字符。但如果原数据中金额后的内容长度不一致（比如日期格式变动、多/少空格、存在特殊字符），会导致截取的金额字符串包含非数字内容或缺失部分数字，CCur无法完成转换，最终单元格保留文本格式，即使设置了货币数字格式也无法生效。
• 区域设置对CCur的影响：CCur函数依赖系统区域设置的小数分隔符、千位分隔符规则。如果原数据中的金额格式（比如用.做小数分隔符）与系统区域设置（比如默认,为小数分隔符）不匹配，CCur会无法识别字符串为有效金额，转换失败后单元格仍为文本类型。
• 原数据中的隐藏字符干扰：原单元格内容可能存在不可见的非打印字符（如特殊空格、控制字符），拆分后这些字符留在金额字符串中，CCur无法将带有隐藏字符的字符串转为数值，导致金额以文本形式存在，设置货币格式后无变化。
• 单元格操作的时序问题：代码在循环中插入行并修改单元格引用，可能导致公式计算时出现引用错误，获取到不完整或错误的字符串，后续CCur转换自然失效。

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