VBA优化求助：如何高效处理以文本格式存储的数字？

原代码运行慢的核心原因是逐个单元格循环操作，每次读写单元格都会触发Excel的界面刷新和事件，数据量大时速度会特别卡。另外你注释里的SpecialCells写法无效，是因为当范围内没有符合条件的单元格时，SpecialCells会直接报错，原代码的错误处理跳过了后续操作，导致看起来范围为空。

下面是几种高效的优化方案：

方案1：数组批量处理（最快） #

把表格数据一次性读入内存数组处理，再批量写回单元格，彻底避免逐个操作单元格的开销：

Public Function ConvertTextNumbersToValues(ByVal sh As Worksheet)
    Dim tbl As ListObject
    Dim dataArr As Variant
    Dim i As Long, j As Long
    
    ' 关闭Excel后台冗余操作，进一步提速
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    On Error GoTo Cleanup ' 确保出错时恢复Excel设置
    
    ' 获取表格对象，无数据直接退出
    Set tbl = sh.ListObjects(1)
    If tbl.DataBodyRange Is Nothing Then GoTo Cleanup
    
    ' 一次性读取整表数据到数组
    dataArr = tbl.DataBodyRange.Value
    
    ' 遍历数组处理文本型数字
    For i = LBound(dataArr, 1) To UBound(dataArr, 1)
        For j = LBound(dataArr, 2) To UBound(dataArr, 2)
            If IsNumeric(dataArr(i, j)) Then
                dataArr(i, j) = CDbl(dataArr(i, j)) ' 转换为数值型
            End If
        Next j
    Next i
    
    ' 批量写回处理后的数据
    tbl.DataBodyRange.Value = dataArr

Cleanup:
    ' 恢复Excel默认设置
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Function

方案2：利用Excel原生选择性粘贴（简洁高效） #

用Excel内置的「选择性粘贴-加」功能，快速批量转换文本型数字，这是原生的高效方法：

Public Function ConvertTextNumbersToValues_Fast(ByVal sh As Worksheet)
    Dim tbl As ListObject
    Dim tempCell As Range
    
    ' 关闭后台操作提速
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    On Error GoTo Cleanup
    
    Set tbl = sh.ListObjects(1)
    If tbl.DataBodyRange Is Nothing Then GoTo Cleanup
    
    ' 找一个空白临时单元格存0
    Set tempCell = sh.Cells(sh.Rows.Count, sh.Columns.Count).End(xlUp).Offset(1, 0)
    tempCell.Value = 0
    
    ' 用选择性粘贴+0的方式快速转换文本数字为数值
    tempCell.Copy
    tbl.DataBodyRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
    Application.CutCopyMode = False
    
    ' 清空临时单元格
    tempCell.ClearContents

Cleanup:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Function

方案3：修复SpecialCells的用法 #

如果一定要用SpecialCells，需要先捕获错误，确保目标范围存在后再操作：

Public Function ConvertTextNumbers_WithSpecialCells(ByVal sh As Worksheet)
    Dim tbl As ListObject
    Dim textCells As Range
    
    Application.ScreenUpdating = False
    
    On Error GoTo Cleanup
    
    Set tbl = sh.ListObjects(1)
    If tbl.DataBodyRange Is Nothing Then GoTo Cleanup
    
    ' 尝试获取文本型常量单元格，捕获无匹配的错误
    On Error Resume Next
    Set textCells = tbl.DataBodyRange.SpecialCells(xlCellTypeConstants, xlTextValues)
    On Error GoTo 0
    
    ' 存在符合条件的单元格时，批量赋值转换
    If Not textCells Is Nothing Then
        textCells.Value = textCells.Value
    End If

Cleanup:
    Application.ScreenUpdating = True
End Function

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