代码跳过将单个单元格从文本转换为数字的格式。

Question

我正在获取传感器数据，这些数据存储在Excel工作表中。由于某种原因，所述传感器的制造商将测量结果输出为文本而不是数字。

当然，这可以手动更改。但是，由于新工作表的大小和频率，我决定编写一个小的外接程序，执行以下Makro：

Sub ConvertTextToNumber(ByVal control As IRibbonControl)
    With Range("=D5:G10000")
        .NumberFormat = "General"
        .Value = .Value
    End With

    With Range("=K5:L10000")
        .NumberFormat = "General"
        .Value = .Value
    End With    
End Sub

原则上，这是完美的。在按下丝带条中的按钮时，这些范围内的每个数字都会从文本格式转换为数字格式。然而，有些细胞只是对此免疫。它们也不能手动重新格式化。我尝试过许多事情，但我已经达到了我的excel能力的极限。我希望有人能帮忙解决这个问题。

这是一张图片，展示了这个问题的一个例子。绿色标记的单元格仍被格式化为文本。

​

​

Answer 1

看起来Excel在使用.Value = .Value时不会将字符串转换为数字，所以您需要使用CDec()显式地将它们转换为小数(如果值是数字的)。

Option Explicit

Public Sub Example()
    
    With [A1:A2] 'Range("D5:G10000")
        Dim ValueArr() As Variant
        ValueArr = .Value2   ' read values into array for fast processing
        
        ' Loop through 2 dimensional array
        Dim iRow As Long
        For iRow = LBound(ValueArr, 1) To UBound(ValueArr, 1)
            Dim iCol As Long
            For iCol = LBound(ValueArr, 2) To UBound(ValueArr, 2)
            
                ' convert strings to decimals if value is numeric
                If IsNumeric(ValueArr(iRow, iCol)) Then
                    ValueArr(iRow, iCol) = CDec(ValueArr(iRow, iCol))
                End If
                
            Next iCol
        Next iRow
        
        ' set numberformat and write array back to cells
        .NumberFormat = "General"
        .Value2 = ValueArr
    End With

End Sub

Answer 2

所有这些都有类似的问题:这是我的最后版本的@Pᴇʜ的答案，最终奏效了。谢谢和学分！

Option Explicit

Public Sub ConvertTextToNumber(ByVal control As IRibbonControl)
    
    With [D5:G10000] 'Range("D5:G10000")
        Dim ValueArr() As Variant
        ValueArr = .Value2   ' read values into array for fast processing
        
        ' Loop through 2 dimensional array
        Dim iRow As Long
        For iRow = LBound(ValueArr, 1) To UBound(ValueArr, 1)
            Dim iCol As Long
            For iCol = LBound(ValueArr, 2) To UBound(ValueArr, 2)
            
                ValueArr(iRow, iCol) = Replace((ValueArr(iRow, iCol)), ",", Application.DecimalSeparator)
                ValueArr(iRow, iCol) = Replace((ValueArr(iRow, iCol)), ".", Application.ThousandsSeparator)
                
                ' convert strings to decimals if value is numeric
                If IsNumeric(ValueArr(iRow, iCol)) Then
                    ValueArr(iRow, iCol) = CDec(ValueArr(iRow, iCol))
                End If
                
            Next iCol
        Next iRow
        
        ' set numberformat and write array back to cells
        .NumberFormat = "General"
        .Value2 = ValueArr
    End With

    With [K5:L10000] 'Range("K5:L10000")
        Dim secondValueArr() As Variant
        secondValueArr = .Value2   ' read values into array for fast processing
        
        ' Loop through 2 dimensional array
        Dim secondiRow As Long
        For secondiRow = LBound(secondValueArr, 1) To UBound(secondValueArr, 1)
            Dim secondiCol As Long
            For secondiCol = LBound(secondValueArr, 2) To UBound(secondValueArr, 2)
            
                secondValueArr(secondiRow, secondiCol) = Replace((secondValueArr(secondiRow, secondiCol)), ",", Application.DecimalSeparator)
                secondValueArr(secondiRow, secondiCol) = Replace((secondValueArr(secondiRow, secondiCol)), ".", Application.ThousandsSeparator)
                
                ' convert strings to decimals if value is numeric
                If IsNumeric(secondValueArr(secondiRow, secondiCol)) Then
                    secondValueArr(secondiRow, secondiCol) = CDec(secondValueArr(secondiRow, secondiCol))
                End If
                
            Next secondiCol
        Next secondiRow
        
        ' set numberformat and write array back to cells
        .NumberFormat = "General"
        .Value2 = secondValueArr
    End With


End Sub

注意:执行第二次循环并重命名每个变量可能是不公开的，但是我对VBA并不熟悉，而这只是起作用了！