VBA FindClosest函数查找大于等于目标值返回无结果问题求助

FindClosest函数在以下两种场景下运行完全正常：

• 查找范围内的最接近数值
• 查找范围内小于等于目标值的最接近数值

但调用该函数执行第三种场景查找大于等于目标值的最接近数值时，即便范围内存在符合要求的接近数值，函数仍返回No value found。

问题复现代码 #

Private Sub CommandButton1_Click()
    Output = TextBox1.Value
    MatchVlu = FindClosest(Range("I2:I32"), Output, 1)

    TextBox2 = MatchVlu
End Sub

Function FindClosest(ByVal rng As Range, Target As Variant, Optional Direction As Integer) As Variant
    '功能说明：返回与目标值最接近的数值
    '入参说明：传入待匹配的单元格范围、目标值，以及可选方向参数，参数规则如下
    '方向参数规则：留空或设为0 = 查找最接近的数值
    ' 设为-1 = 查找小于等于目标值的最接近数值
    ' 设为1 = 查找大于等于目标值的最接近数值
    '返回值说明：返回匹配到的数值，在VBA内部调用时也支持直接返回匹配单元格的地址
    t = 1.79769313486231E+308 '初始化最小差值
    FindClosest = "No value found"
    For Each r In rng
        If IsNumeric(r) Then
            u = Abs(r - Target)
            If Direction > 0 And r >= Target Then
                '仅匹配大于等于目标值的数值
                If u < t Then
                    t = u
                    Set FindClosest = r
                End If
            ElseIf Direction < 0 And r <= Target Then
                '仅匹配小于等于目标值的数值
                If u < t Then
                    t = u
                    Set FindClosest = r
                End If
            ElseIf Direction = 0 Then
                If u < t Then
                    t = u
                    Set FindClosest = r
                End If
            End If
        End If
    Next
End Function

根因分析 #

问题出在调用侧的参数传递逻辑：TextBox1.Value返回的是字符串类型值，直接赋值给Output变量后传入FindClosest函数，在执行r >= Target判断时，VBA会将数值类型的单元格值与字符串类型的目标值做比较，默认规则下所有数值均小于字符串，因此r >= Target的条件永远无法触发，导致方向为1时始终返回未找到结果。

修复方案 #

将输入的文本框值转换为数值类型后再传入函数即可，建议同时增加输入合法性校验，避免非数值输入引发报错：

Private Sub CommandButton1_Click()
    Dim Output As Double
    ' 校验输入是否为合法数值
    If Not IsNumeric(TextBox1.Value) Then
        TextBox2 = "请输入合法数值"
        Exit Sub
    End If
    Output = CDbl(TextBox1.Value)
    MatchVlu = FindClosest(Range("I2:I32"), Output, 1)
    TextBox2 = MatchVlu
End Sub

建议在模块顶部加上Option Explicit强制变量声明，避免类似隐式类型转换导致的隐性问题。

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