VBA自定义函数调用返回#VALUE!错误 列字母拼接Range参数问题求解

问题排查 #

• Range地址构造语法错误：Range("ColumnLetter & 2: ColumnLetter & 105")写法错误，双引号包裹的内容会被识别为字符串，不会解析ColumnLetter变量值，导致生成非法地址，是触发#VALUE!错误的核心原因。
• countEmptyTests函数中未声明和赋值ColumnLetter变量，直接调用会报错。
• VLookup未做容错处理：如果查找值不存在于Worksheets(1).Range("A2:C302")区域，WorksheetFunction.VLookup会直接抛出运行时错误，导致函数返回异常。
• 未显式指定Range所属工作表，默认取活动工作表，跨表调用函数时会出现取值错误。

修正后完整代码 #

Public Function countPassableSteps(thisRange As Range) As Long
    Application.Volatile
    Dim lColorCounter As Long
    Dim rngCell As Range
    Dim myVar As Variant
    For Each rngCell In thisRange
        If Not IsEmpty(rngCell.Value) Then
            ' 改用Application.VLookup避免查找失败直接报错
            myVar = Application.VLookup(rngCell.Value, Worksheets(1).Range("A2:C302"), 3, False)
            If Not IsError(myVar) And myVar = True Then
                lColorCounter = lColorCounter + 1
            End If
        End If
    Next
    countPassableSteps = lColorCounter
End Function


Public Function countPassableTests(thisRange As Range) As Long
    Application.Volatile
    Dim lColorCounter As Long
    Dim rngCell As Range
    ' 可按需修改为你的数据所在工作表索引/名称
    Dim dataSheet As Worksheet
    Set dataSheet = ThisWorkbook.Worksheets(1)
    For Each rngCell In thisRange
        If Not IsEmpty(rngCell.Value) Then
            Dim ColumnNumber As Long
            Dim ColumnLetter As String
            ColumnNumber = rngCell.Column
            ColumnLetter = Split(Cells(1, ColumnNumber).Address, "$")(1)
            Dim NbStepsPassable As Long
            ' 修正Range构造语法，显式指定工作表
            NbStepsPassable = countPassableSteps(dataSheet.Range(ColumnLetter & "2:" & ColumnLetter & "105"))
            Dim NbNotEmpty As Long
            NbNotEmpty = WorksheetFunction.CountA(dataSheet.Range(ColumnLetter & "2:" & ColumnLetter & "105"))
            If NbStepsPassable = NbNotEmpty Then lColorCounter = lColorCounter + 1
        End If
    Next
    countPassableTests = lColorCounter
End Function


Public Function countEmptyTests(thisRange As Range) As Long
    Application.Volatile
    Dim lColorCounter As Long
    Dim rngCell As Range
    Dim dataSheet As Worksheet
    Set dataSheet = ThisWorkbook.Worksheets(1)
    For Each rngCell In thisRange
        If Not IsEmpty(rngCell.Value) Then
            ' 补充ColumnLetter变量定义
            Dim ColumnNumber As Long
            Dim ColumnLetter As String
            ColumnNumber = rngCell.Column
            ColumnLetter = Split(Cells(1, ColumnNumber).Address, "$")(1)
            Dim NbNotEmpty As Long
            NbNotEmpty = WorksheetFunction.CountA(dataSheet.Range(ColumnLetter & "2:" & ColumnLetter & "105"))
            If NbNotEmpty = 0 Then lColorCounter = lColorCounter + 1
        End If
    Next
    countEmptyTests = lColorCounter
End Function

内容的提问来源于stack exchange，提问作者Loïc Dubois