Excel VBA求值宏如何忽略°符号以正常处理角度尺寸

解决方法 #

你可以通过VBA的Replace函数删除单元格内容中的°符号后再做数值校验，即可兼容角度尺寸的处理场景，具体修改方案如下：

步骤1：新增值清理辅助函数 #

在你的模块中新增如下通用函数，用于自动去除单元格内容中的°符号，同时自动将纯数字内容转换为数值类型：

Function CleanCellValue(inputVal As Variant) As Variant
    ' 处理错误值直接返回
    If IsError(inputVal) Then
        CleanCellValue = inputVal
        Exit Function
    End If
    ' 仅处理字符串类型内容
    If TypeName(inputVal) = "String" Then
        ' 移除所有°符号
        inputVal = Replace(inputVal, "°", "")
        ' 替换后为数值则转换为双精度类型返回
        If IsNumeric(inputVal) Then
            CleanCellValue = CDbl(inputVal)
        Else
            CleanCellValue = inputVal
        End If
    Else
        ' 非字符串类型直接返回原值
        CleanCellValue = inputVal
    End If
End Function

步骤2：修改原有逻辑适配清理规则 #

将原有代码中所有直接读取单元格值、判断是否为数值、做数值比较的位置，都替换为调用CleanCellValue处理后的值即可，修改后的完整代码如下：

Sub Evaluate_Pre_Forge()
    Dim R As Integer
    Dim R2 As Integer
    Dim Rng As Range
    Dim processedMax As Double
    Dim processedRngVal As Variant
    
    R = 12
    R2 = 13
    Do While (R < 70)
        processedMax = CleanCellValue(Cells(R, 14).Value)
        ' 处理N列为非数值的场景
        If Not IsNumeric(processedMax) Then
            For Each Rng In Range(("T" & R), ("W" & R2))
                processedRngVal = CleanCellValue(Rng.Value)
                If UCase(Cells(R, 8).Value) = "Y" Then
                    If Not IsEmpty(Rng) Then
                        If Not IsNumeric(processedRngVal) And processedRngVal <> "Conforms" Then
                            Rng.Interior.Color = 16777215
                        ElseIf Not IsNumeric(processedRngVal) And processedRngVal = "Conforms" Then
                            Rng.Interior.Color = 7862528
                        End If
                    End If
                End If
            Next Rng
        ' 处理N列为数值的场景
        ElseIf IsNumeric(processedMax) Then
            If processedMax > 0 And CleanCellValue(Cells(R, 17).Value) <= 0 Then
                For Each Rng In Range(("T" & R), ("W" & R2))
                    processedRngVal = CleanCellValue(Rng.Value)
                    If UCase(Cells(R, 8).Value) = "Y" Then
                        If Not IsEmpty(Rng) Then
                            If IsNumeric(processedRngVal) Then
                                ' 最大值>=100的判断逻辑
                                If processedMax >= 100 Then
                                    If processedRngVal >= 100 Then
                                        If processedMax >= processedRngVal Then
                                            Rng.Interior.Color = 7862528
                                        End If
                                    End If
                                    If processedRngVal < 100 And processedRngVal >= 10 Then
                                        Rng.Interior.Color = 7862528
                                    End If
                                    If processedRngVal < 10 And processedRngVal >= 0 Then
                                        Rng.Interior.Color = 7862528
                                    End If
                                End If
                                ' 最大值在10~100之间的判断逻辑
                                If processedMax < 100 And processedMax >= 10 Then
                                    If processedRngVal < 100 And processedRngVal >= 10 Then
                                        If processedMax >= processedRngVal Then
                                            Rng.Interior.Color = 7862528
                                        End If
                                    End If
                                    If processedRngVal < 10 And processedRngVal >= 0 Then
                                        Rng.Interior.Color = 7862528
                                    End If
                                End If
                                ' 最大值在0~10之间的判断逻辑
                                If processedMax < 10 Then
                                    If processedRngVal < 10 And processedRngVal >= 0 Then
                                        If processedMax >= processedRngVal Then
                                            Rng.Interior.Color = 7862528
                                        End If
                                    End If
                                End If
                            ElseIf Not IsNumeric(processedRngVal) And processedRngVal = "Conforms" Then
                                Rng.Interior.Color = 7862528
                            End If
                        End If
                    End If
                Next Rng
            End If
        End If
        R = R + 2
        R2 = R2 + 2
    Loop
End Sub

额外优化说明 #

本次修改同时把原来的大小写Y的判断逻辑替换成了UCase统一转大写判断，减少了冗余的判断条件，不影响原有功能。

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