Excel VBA用户窗体TextBox转Double出现类型不匹配错误求助

问题描述 #

开发的Excel 365 VBA脚本，通过用户窗体实现Excel文件导入、TextBox编辑数据后写入数据库工作簿，此前运行完全正常。近期运行用户窗体时，使用CDbl()将TextBox内以句点作为小数分隔符的数字字符串转换为Double类型时，触发Run-time error '13': Type mismatch类型不匹配错误。

监视窗口显示TextBox内容正常，即使先把内容赋值给变量再转换仍报错。已尝试保存项目、重启文件及电脑，未对Excel或系统进行更新，使用的是之前测试正常的同一文件。

相关代码片段：

Private Sub Btn_finish_Click()
    Dim i%, row%
    Dim teszt As Double
    row = Me.Controls("Box_insert_place").Value
    If Me.Controls("CheckBox_check").Value = True Then
        'std measurement data'
        If Me.Controls("Box_std_OD_8").Value <> "" Then Cells(row, 16).Value = CDbl(Me.Controls("Box_std_OD_8").Value)
        If Me.Controls("Box_std_OD_1").Value <> "" Then Cells(row, 17).Value = CDbl(Me.Controls("Box_std_OD_1").Value)
        (更多类似代码行)
    End If
End Sub

解决方案 #

问题根源 #

VBA的CDbl()函数依赖系统区域设置的小数分隔符，若当前系统默认小数分隔符为逗号，输入句点的数字字符串就会因格式不匹配导致转换失败。

方法1：临时切换VBA小数分隔符 #

在转换代码前后临时修改Excel的小数分隔符设置，转换完成后恢复原设置：

Private Sub Btn_finish_Click()
    Dim i%, row%
    Dim teszt As Double
    Dim originalDecimalSep As String
    ' 保存原小数分隔符
    originalDecimalSep = Application.DecimalSeparator
    ' 设置为句点作为小数分隔符
    Application.DecimalSeparator = "."
    
    row = Me.Controls("Box_insert_place").Value
    If Me.Controls("CheckBox_check").Value = True Then
        'std measurement data'
        If Me.Controls("Box_std_OD_8").Value <> "" Then Cells(row, 16).Value = CDbl(Me.Controls("Box_std_OD_8").Value)
        If Me.Controls("Box_std_OD_1").Value <> "" Then Cells(row, 17).Value = CDbl(Me.Controls("Box_std_OD_1").Value)
        (更多类似代码行)
    End If
    
    ' 恢复原小数分隔符
    Application.DecimalSeparator = originalDecimalSep
End Sub

方法2：使用Val()函数替代CDbl() #

Val()函数默认识别句点为小数分隔符，不受系统区域设置影响，直接替换即可：

Private Sub Btn_finish_Click()
    Dim i%, row%
    Dim teszt As Double
    row = Me.Controls("Box_insert_place").Value
    If Me.Controls("CheckBox_check").Value = True Then
        'std measurement data'
        If Me.Controls("Box_std_OD_8").Value <> "" Then Cells(row, 16).Value = Val(Me.Controls("Box_std_OD_8").Value)
        If Me.Controls("Box_std_OD_1").Value <> "" Then Cells(row, 17).Value = Val(Me.Controls("Box_std_OD_1").Value)
        (更多类似代码行)
    End If
End Sub

方法3：手动替换分隔符后转换 #

将TextBox中的句点替换为系统当前的小数分隔符，再用CDbl()转换：

Private Sub Btn_finish_Click()
    Dim i%, row%
    Dim teszt As Double
    Dim inputStr As String
    row = Me.Controls("Box_insert_place").Value
    If Me.Controls("CheckBox_check").Value = True Then
        'std measurement data'
        inputStr = Me.Controls("Box_std_OD_8").Value
        If inputStr <> "" Then
            inputStr = Replace(inputStr, ".", Application.DecimalSeparator)
            Cells(row, 16).Value = CDbl(inputStr)
        End If
        
        inputStr = Me.Controls("Box_std_OD_1").Value
        If inputStr <> "" Then
            inputStr = Replace(inputStr, ".", Application.DecimalSeparator)
            Cells(row, 17).Value = CDbl(inputStr)
        End If
        (更多类似代码行)
    End If
End Sub

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